How to divide columns in Excel (Top 8 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

division symbol in excel. forward slash. the number that you want to divide is called dividend. The number you use to divide is called divisor.

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 numbers in Excel

Here are some examples of division in Excel.

how to divide numbers in Excel. multiplication and division have the same order of calculations but performed with left associative.

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

1) How to divide a cell by another cell/number in Excel

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

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

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

how to divide a cell by another cell or number in excel

  • Dividing a cell by another cell: A2/B2
  • Dividing a cell by value: A3/5
  • Dividing a cell by another cell: $A$4/$B$4 (cell reference is absolute)

2) How to divide one column by another column in Excel (copying formula)

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

Columns with numbers. dividing one column by another column in excel

  • In the cell C2, I type formula =A2/B2.

Dividing one column by another column. Placing formula in the cell.

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

Copying formula from one cell to other using the auto fill handle tool in excel.

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

the first column is divided by second column using formula.

3) How to divide one column by another one (using Excel’s Array formula)

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.

How an array formula can benefit us in this case?

If you want to keep safe your Excel formula to be 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.

  • Select the cell range C2 to C11 and I type this formula: =A2:A11/B2:B11

using Array formula to divide one column by another column in Excel

  • 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.

Using Excel array formula to divide one column by another column in excel

A notable thing is: all the cells (C2:C11) 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) How to divide a column by a number (using formula)

Process 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).

  • In the cell B2, I write this formula: =A2/10

dividing a column by a constant or number in excel

  • Press Enter and then use the formula for other cells below. Here is the output.

dividing some numbers by value 10 in excel

So, the values of column A is divided by a specific number 10.

Process 2: Dynamic method

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

We can use this method.

  • In the cell B5, I use this formula: =A5/$B$2. In the cell B2, I have placed the number that will be used to divide the values of column A.

Observe the formula. You see I have made the B2 cell reference absolute as I don’t want it to be changed when I shall use the formula for other cells in the column.

dividing the values of a column by number in dynamic way (in excel)

  • Now press Enter and use the formula for other cells in the column. Here are the results.

dividing the values of a column by numbers in excel in the dynamic way.

5) Dividing a column by a number (using Paste Special)

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.

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

Using Paste Special command to divide a column by a number.

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

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

copy a cell. Then select the cells where you want to paste the value. Then right click and finally choose the Paste Special command from the drop down.

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

Choose the divide option in the Paste Special dialog box.

  • Here is the result.

The existing cells are replaced by the division results in the same place.

All the numbers are replaced by new values (divided by 50). No formulas are seen in the cells.

6) How to divide a column using Excel QUOTIENT function

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

Here is the syntax of Excel’s QUOTIENT function: QUOTIENT(numerator, denominator)

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

Using Excel QUOTIENT function to get only the integer part of a division.

7) How to divide a column by percentage

You know 25% = 25/100 = 0.25

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

Here are some examples of dividing a column by percentages.

divide a column by percentage in excel

  • =50/25% = 200
  • =B6/0.25 = 200
  • =B7/C2 = 200

8) Handling #DIV/0! Error while dividing (using IFERROR function)

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.

Excel returns #DIV/0! Error if you divide a number by zero.

We can handle this error in two ways:

  • Using Excel IFERROR function
  • And using Excel IF function

Handling #DIV/0! Error using IFERROR function

Here is the syntax of Excel’s IFERROR function: IFERROR (value, value_if_error)

See how I have used the IFERROR function to handle the #DIV/0! Error in Excel.

Handling #DIV/0! Error in Excel using IFERROR function

I have used this formula in the cell D2: =IFERROR(B3/C3, "Not allowed")

Handling #DIV/0! Error using IF function

Here is the syntax of Excel’s IF function: IF(logical_test, [value_if_true], [value_if_false])

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

Handling #DIV/0! Error in Excel using IF Function

In the cell D2, I have used this formula: =IF(C3=0,"Not allowed",B3/C3)

Then I copy pasted this formula for other cells in the column.

Related Readings

Conclusion

These are my ways of dividing columns in Excel. Do you know any other methods? I am eager to know. Please let me know in the comment box.


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

2 Comments
  1. Reply
    Prithviraj December 6, 2018 at 5:18 PM

    It’s really amazing sir , you are great.

    • Reply
      Kawser December 6, 2018 at 5:22 PM

      Thanks for your feedback 🙂

    Leave a reply