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

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.

Table of Contents hide

Download Practice Workbook

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

1. Divide a Column by a Number Using a Formula

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

3. Divide a Column by a Number Using Cell References

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

Conclusion

Download Practice Workbook

You can download the practice workbook from here:

Dividing Column by a Number.xlsx

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.

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

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

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.