How to Divide Columns in Excel (8 Easy Ways)

In this article, I will show how to divide columns in Excel. I will show several ways. Choose one that best suits your job.

So, let’s start.


Division Symbol in Excel

In mathematics, to divide two numbers we use an obelus symbol (÷).

15 ÷ 5 = 3

But in Excel, we use the forward-slash (/) to divide two numbers.

15/5 = 3

In Excel, we write a division expression like the above image, where:

  • ‘A’ is the dividend or numerator – a number you want to divide by another number (divisor).
  • ‘B’ is the divisor or denominator – a number by which you want to divide another number (dividend).

How to Divide in Excel

Here are some examples of division in Excel.

To know more about operator precedence and associativity in Excel, read this article: What is the Order & Precedence of Operations in Excel?


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


8 Handy Ways to Divide Columns in Excel

In the following methods here, we will demonstrate to you how to divide columns in Excel using 8 different ways, such as dividing a cell by another cell or number, copying the formula, applying the Array formula, using the formula, utilizing the Paste Special feature, inserting the QUOTIENT function, using percentage, and applying VBA code. Let’s suppose we have a sample data set.

Handy Ways to Divide Columns in Excel


1. Dividing a Cell by Another Cell or Number in Excel

Dividing a cell by another cell or number is the same as dividing two numbers in Excel.

In the place of numbers, we just use cell references. Cell references can be relative, absolute, or mixed.

Step 1:

Here are some examples of dividing cells by another cell or number (image below).

Dividing a Cell by Another Cell or Number in Excel

  • Dividing a cell into another cell.
=B5/C5
  • Dividing a cell by value.
=B6/5
= $B$7/$C$7 

Read More: How to Divide Without Using a Function in Excel (with Quick Steps)


2. Copying Formula to Divide Columns in Excel

Step 1:

We want to divide the values of column B by the values of column C.

Copying Formula to Divide Columns in Excel

Step 2:

  • Firstly select cell D5,  and use this formula.
=B5/C5

Step 3:

  • Then, press Enter. The formula outputs 10 as 50 divided by 5 returns 10. Then, we select cell D5. And copy the formula in the D5 to the other cells below holding and dragging the Excel’s autofill handle tool. Or you can just double-click on the autofill handle tool.

Step 4:

  • And, here is the result. On the right side, the formula is shown.

Copying Formula to Divide Columns in Excel


3. Applying Array formula to Divide Columns

Let’s divide a column by another one using Excel’s array formula. Most general Excel users fear of Excel’s Array Formula. If you’re one of them, read this article: Excel Array Formula Basic: What is an Array in Excel?

If you want to keep safe your Excel formula from being altered or deleted by your users, you can use the array formula.

Let me show how you could perform the above calculations using an array formula.

Step 1:

  • Firstly, select the cell range D5 to D14 and I type this formula.
=B5:B14/C5:C14

Applying Array formula to Divide Columns in Excel

Step 2:

  • Now, press CTRL + SHIFT + ENTER (I remember it with the short form CSE) simultaneously. This is the way to make an array formula in Excel. You see the results.

Notes

  • All the cells (D5:D14) are holding the same formula. So, you cannot change the formula of a single cell. To change the formula, you have to select all the cells, and then you can edit or delete the formula. After Editing or Deleting the formula, you have to press simultaneously CTRL + SHIFT + ENTER again.

4. Using Formula to Divide Columns

4.1.Using the Hard-coded Number

Suppose you want to divide the values of a column by a specific number 10 (it can be any).

Step 1:

  • Firstly, select cell C5,  and use this formula.
=B5/10

Using Formula to Divide Columns in Excel

Step 2:

  • So, hit Enter and then use the formula for the other cells below.
  • Finally, you will see the output here.

So, the values of column B are divided by a specific number, 10.

4.2. Using Dynamic Method

Maybe one day, you might want to divide those numbers by another number, say 5. It is not wise to edit the formula to get results.

We can use this method.

Step 1:

  • Firstly select cell D5,  and use this formula
=B5/$C$5
  • Therefore, from cell C5 to C14, I will place the number that will be used to divide the values of column B.
  • Then, observe the formula. You see I have made the from cell C5 to C14 cell reference absolute from cell C5 to C14 as I don’t want it to be changed when I use the formula for other cells in the column.

Step 2:

  • Then, press Enter, and use the formula for other cells in the column.
  • As a result, you will observe the results here in the below image.

Using Formula to Divide Columns in Excel


5. Utilizing Paste Special Feature to Divide Columns

In this method, you can divide a column by a specific number without using an Excel formula.

We shall use Excel’s Paste Special feature.

Step 1:

  • Firstly, we will place the divisor in a cell. Suppose in our case, the divisor is in cell C3. Select the cell and copy the value of the cell (keyboard shortcut CTRL + C) command.

Step 2:

  • Similarly, we will select the numbers under column B -> Right-click on my mouse. A menu will appear -> From the menu, click on the Paste Special.

Notes

  • Keyboard shortcut to open the Paste Special dialog box: CTRL + ALT + V.

Step 3:

  • Then, Paste Special dialog box will appear. In this dialog box, select the Divide option (lower right corner of the dialog box). Finally, click on

Step 4:

  • Therefore, here is the final result.

Utilizing Paste Special Feature to Divide Columns in Excel

All the numbers are replaced by the new values (divided by 50). As a result, no formulas are seen in the cells.

Read More: How to Divide for Entire Row in Excel (6 Simple Methods)


6. Inserting QUOTIENT Function to Divide Columns

Excel’s QUOTIENT function returns only the integer portion of a division.

Steps:

=QUOTIENT(numerator, denominator)

Check out the differences between the results done with the general Excel formula and using the QUOTIENT function.

 Inserting QUOTIENT Function to Divide Columns in Excel

Read More: How to Divide with Decimals in Excel (5 Suitable Examples)

7. Using Percentage to Divide Columns

You know 25% = 25/100 = 0.25

So, dividing a column by percentage is actually the same as dividing a column by a number.

Steps:

  • Here are some examples of dividing a column by percentages.
=B5/25% = 200
=B6/0.25 = 400
=B7/D3 = 600

Using Percentage to Divide Columns in Excel

Read More: How to Divide a Value to Get a Percentage in Excel (5 Suitable Examples)


8. Applying VBA Code to Divide Columns

VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate a VBA code that makes it very easy to divide columns in Excel.

Step 1:

  • Firstly, we will open the Developer tab.
  • Then, we will select the Visual Basic command.

Applying VBA Code to Divide Columns in Excel

Step 2:

  • Here, the Visual Basic window will open.
  • After that, from the Insert option, we will choose the new Module to write a VBA code.

Step 3:

  • Firstly, paste the following VBA code into the Module.
  • Then, click the “Run” button or press F5 to run the program.
Sub Divide_Columns()
Range("D5:D14").Formula = "=B5/C5"
End Sub

Applying VBA Code to Divide Columns in Excel

Step 4:

  • Finally, as you can see, that the D column will demonstrate all the results here.

Applying VBA Code to Divide Columns in Excel

Special Notes to Remember

  • You cannot divide a number by zero. This is not allowed in mathematics.
  • When you try to divide a number by zero, Excel shows #DIV/0! Error.

Therefore, we can handle this error in two ways:

  1. Using the IFERROR function.
  2. Using the IF function.

Handling #DIV/0! Error Using IFERROR function

Here is the syntax of the IFERROR function:

=IFERROR (value, value_if_error)

Here, you will see how we apply the IFERROR function to handle the #DIV/0! Error in Excel.

Then, I will use this formula in cell D2.

=IFERROR(B5/C5, "Not allowed")

Handling #DIV/0! Error using the IF function

Here is the syntax of the IF function:


=IF(logical_test, [value_if_true], [value_if_false])

Besides, instead of using the IFERROR function, you can also use the IF function to handle the #DIV/0! Error (following image).

Then, in cell D2, I used this formula.

=IF(C5=0, "Not allowed", B5/C5)

Then, I will copy-paste this formula to other cells in the column.

Read More: [Fixed] Division Formula Not Working in Excel (6 Possible Solutions)

Related Articles

Conclusion

In this article, we’ve coveredhandy methods to divide columns in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

6 Comments
  1. It’s really amazing sir , you are great.

  2. Thank you for introducing me to the word “obelus” and the quotient function.

  3. Hi,

    The description is very well organized and thanks to the developer for provising such knowledge.

Leave a reply

ExcelDemy
Logo