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.
- Now, Copy the cell by pressing CTRL + C on the keyboard.
- And paste to the remaining cells of the column by pressing CTRL + V on the keyboard.
- 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.
- Then, to open the Paste Special option, select all the cells of the column and simply press CTRL + ALT + V.
- 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.
- Now, a window named ‘Paste Special’ will appear. Select Divide under the Operation options. And click on OK.
- 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$3Here, You have to use $ for divisor cell reference to make it absolute.
- Then, copy the formula and paste it into the other cells of the column by using CTRL + C and CTRL + V
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.
