How to Divide a Column by a Number in Excel (3 Quick Methods)

Get FREE Advanced Excel Exercises with Solutions!

Dividing a column by a number in Excel is very common in doing calculations.  But sometimes anyone can find it difficult to divide a large column by a number in Excel.  There are some easy methods by which you can divide a column by a number in Excel very quickly. This article will discuss 3 quick methods to divide a column by a number in Excel with proper illustrations.


How to Divide a Column by a Number in Excel: 3 Easy Methods

I have sample data of 3 columns and 9 rows. In this data table, I know the revenue and the price per unit. So I will get the sales amount by dividing the revenue column by price. In this article, I will show 3 quick methods to divide a column by a number


1. Divide a Column by a Number Using a Formula

In this method, the column will be divided by a number directly by using the formula in the first cell and will do Copy-Paste for the remaining cells of the column.

Steps:

  • At first, write the following formula in the first cell of the column:

=D5/100

This formula is to divide the revenue cell D5 by 100 which is the price per unit. The ” / “ sign is used for division in Excel.

How to Divide a Column by a Number in Excel

  • Now, Copy the cell by pressing CTRL + C on the keyboard.

How to Divide a Column by a Number in Excel

  • And paste to the remaining cells of the column by pressing CTRL + V on the keyboard.

Divide a Column by a Number Using a Formula

  • Then, you can simply Copy and Paste by double-clicking on the right-down corner of the cell or dragging the point by mouse till the cell you want to paste.

Read More: How to Divide One Column by Another in Excel


2. Divide a Column by a Number with Paste Special Method

If you want to divide a column data by a number then you have to use the paste special method.

Steps:

  • At first, copy the cell containing the number by which the column will be divided.

How to Divide a Column by a Number in Excel

  • Then, to open the Paste Special option, select all the cells of the column and simply press CTRL + ALT + V.

Divide a Column by a Number with Paste Special Method

  • Another method to open the Paste Special option is to press the right button of the mouse after selecting all the cells of the column. One window will appear and here you will find the Paste Special option. Then press on it.

Divide a Column by a Number with Paste Special Method

  • Now, a window named ‘Paste Special’ will appear. Select Divide under the Operation options. And click on OK.

Divide a Column by a Number with Paste Special Method

  • Then, you will see the whole column will be divided. And previous values have turned into divided values.

Read More: Division Formula in Excel for Multiple Cells


3. Divide a Column by a Number Using Cell References

If you want to iterate several times by changing the divisor, using a cell reference for the divisor will be helpful.  You can simply change the divisor and the whole column will be divided with that value automatically.

Steps:

  • At first, input the divisor into a cell. Here C3 is the divisor. Now input this formula in the first cell of the column:
=D5/$C$3

Here, You have to use $ for divisor cell reference to make it absolute.

Divide a Column by Using Cell References

  • Then, copy the formula and paste it into the other cells of the column by using CTRL + C and CTRL + V

Divide a Column by Using Cell References


Error That Occurs While Dividing a Column by Zero: The #DIV/0! Error

We know that nothing can be divided by zero. if we input the divisor value as zero it will give #DIV/0! Error. To avoid this error, you must put a non-zero value in the divisor cell

Read More: [Fixed] Division Formula Not Working in Excel


Download Practice Workbook

You can download the practice workbook from here:


Conclusion

In this article, I have described 3 quick methods to divide a column by a number in Excel. 1st method is the simplest and quickest method. By this method, if you want to change the value of the divisor, you have to change it in all cells individually. In the 2nd method, the column transforms into divided values. And the third one gives the most flexibility as you can change the value of the divisor at any time. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Divide in Excel | Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo