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.
Download Practice Workbook
You can download the practice workbook from here:
3 Easy Methods to Divide a Column by a Number in Excel
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 Without Using a Function in Excel (with Quick Steps)
2. Divide a Column by a Number with Paste Special Method
If you want to divide the column inside that column 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 Paste Special option is, after selecting all the cells of the column Press the Right button of the mouse. 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 Press 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$3
Here, 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
Read More: How to divide columns in Excel (Top 8 easy & quick ways)
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 (6 Possible Solutions)
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. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Divide for Entire Row in Excel (6 Simple Methods)
- Divide with Decimals in Excel (5 Suitable Examples)
- How to Divide a Value to Get a Percentage in Excel (5 Suitable Examples)
- Divide a Number by a Percentage in Excel (3 Suitable Examples)
- How to Add and Then Divide in Excel (5 Suitable Examples)