How to divide in Excel and handle #DIV/0! error

The tutorial shows how to use a division formula in Excel to divide numbers, cells or entire columns and how to handle Div/0 errors.

As with other basic math operations, Microsoft Excel provides several ways to divide numbers and cells. Which one to use depends on your personal preferences and a particular task you need to solve. In this tutorial, you will find some good examples of using a division formula in Excel that cover the most common scenarios.

Divide symbol in Excel

The common way to do division is by using the divide sign. In mathematics, the operation of division is represented by an obelus symbol (÷). In Microsoft Excel, the divide symbol is a forward slash (/).

With this approach, you simply write an expression like =a/b with no spaces, where:

  • a is the dividend - a number you want to divide, and
  • b is the divisor - a number by which the dividend is to be divided.

How to divide numbers in Excel

To divide two numbers in Excel, you type the equals sign (=) in a cell, then type the number to be divided, followed by a forward slash, followed by the number to divide by, and press the Enter key to calculate the formula.

For example, to divide 10 by 5, you type the following expression in a cell: =10/5

The screenshot below shows a few more examples of a simple division formula in Excel:
Formulas to divide numbers in Excel

When a formula performs more than one arithmetic operation, it is important to remember about the order of calculations in Excel (PEMDAS): parentheses first, followed by exponentiation (raising to power), followed by multiplication or division whichever comes first, followed by addition or subtraction whichever comes first.

How to divide cell value in Excel

To divide cell values, you use the divide symbol exactly like shown in the above examples, but supply cell references instead of numbers.

For example:

  • To divide a value in cell A2 by 5: =A2/5
  • To divide cell A2 by cell B2: =A2/B2
  • To divide multiple cells successively, type cell references separated by the division symbol. For example, to divide the number in A2 by the number in B2, and then divide the result by the number in C2, use this formula: =A2/B2/C2

Dividing cells in Excel

Divide function in Excel (QUOTIENT)

I have to say plainly: there is no Divide function in Excel. Whenever you want to divide one number by another, use the division symbol as explained in the above examples.

However, if you want to return only the integer portion of a division and discard the remainder, then use the QUOTIENT function:

QUOTIENT(numerator, denominator)

Where:

  • Numerator (required) - the dividend, i.e. the number to be divided.
  • Denominator (required) - the divisor, i.e. the number to divide by.

When two numbers divide evenly without remainder, the division symbol and a QUOTIENT formula return the same result. For example, both of the below formulars return 2.

=10/5

=QUOTIENT(10, 5)

When there is a remainder after division, the divide sign returns a decimal number and the QUOTIENT function returns only the integer part. For example:

=5/4 returns 1.25

=QUOTIENT(5,4) yields 1
Excel division formula examples

3 things you should know about QUOTIENT function

As simple as it seems, the Excel QUOTIENT function still has a few caveats you should be aware of:

  1. The numerator and denominator arguments should be supplied as numbers, references to cells containing numbers, or other functions that return numbers.
  2. If either argument is non-numeric, a QUOTIENT formula returns the #VALUE! error.
  3. If denominator is 0, QUOTIENT returns the divide by zero error (#DIV/0!).

How to divide columns in Excel

Dividing columns in Excel is also easy. It can done by copying a regular division formula down the column or by using an array formula. Why would one want to use an array formula for a trivial task like that? You will learn the reason in a moment :)

How to divide two columns in Excel by copying a formula

To divide columns in Excel, just do the following:

  1. Divide two cells in the topmost row, for example: =A2/B2
  2. Insert the formula in the first cell (say C2) and double-click the small green square in the lower-right corner of the cell to copy the formula down the column. Done!

Since we use relative cell references (without the $ sign), our division formula will change based on a relative position of a cell where it is copied:
Dividing two columns by copying a formula

Tip. In a similar fashion, you can divide two rows in Excel. For example, to divide values in row 1 by values in row 2, you put =A1/A2 in cell A3, and then copy the formula rightwards to as many cells as necessary.

How to divide one column by another with an array formula

In situations when you want to prevent accidental deletion or alteration of a formula in individual cells, insert an array formula in an entire range.

For example, to divide the values in cells A2:A8 by the values in B2:B8 row-by-row, use this formula: =A2:A8/B2:B8

To insert the array formula correctly, perform these steps:

  1. Select the entire range where you want to enter the formula (C2:C8 in this example).
  2. Type the formula in the formula bar and press Ctrl + Shift + Enter to complete it. As soon as you do this, Excel will enclose the formula in {curly braces}, indicating it's an array formula.

As the result, you will have the numbers in column A divided by the numbers in column B in one fell swoop. If someone tries to edit your formula in an individual cell, Excel will show a warning that part of an array cannot be changed.

To delete or modify the formula, you will need to select the whole range first, and then make the changes. To extend the formula to new rows, select the entire range including new rows, change the cell references in the formula bar to accommodate new cells, and then press Ctrl + Shift + Enter to update the formula.
Dividing one column by another with an array formula

How to divide a column by a number in Excel

Depending on whether you want the output to be formulas or values, you can divide a column of numbers by a constant number by using a division formula or Paste Special feature.

Divide a column by number with a formula

As you already know, the fastest way to do division in Excel is by using the divide symbol. So, to divide each number in a given column by the same number, you put a usual division formula in the first cell, and then copy the formula down the column. That's all there is to it!

For example, to divide values in column A by the number 5, insert the following formula in A2, and then copy it down to as many cells as you want: =A2/5
Divide a column by the same number using a formula.

As explained in the above example, the use of a relative cell reference (A2) ensures that the formula gets adjusted properly for each row. That is, the formula in B3 becomes =A3/5, the formula in B4 becomes =A4/5, and so on.

Instead of supplying the divisor directly in the formula, you can enter it in some cell, say D2, and divide by that cell. In this case, it's important that you lock the cell reference with the dollar sign (like $D$2), making it an absolute reference because this reference should remain constant no matter where the formula is copied.

As shown in the screenshot below, the formula =A2/$D$2 returns exactly the same results as =A2/5.
The result of dividing a column by a number

Divide a column by the same number with Paste Special

In case you want the results to be values, not formulas, you can do division in the usual way, and then replace formulas with values. Or, you can achieve the same result faster with the Paste Special > Divide option.

  1. If you don't want to override the original numbers, copy them to the column where you want to have the results. In this example, we copy numbers from column A to column B.
  2. Put the divisor in some cell, say D2, like shown in the screenshot below.
  3. Select the divisor cell (D5), and press Ctrl + C to copy it to the clipboard.
  4. Select the cells you want to multiply (B2:B8).
  5. Press Ctrl + Alt + V, then I, which is the shortcut for Paste Special > Divide, and hit the Enter key.

Dividing a column by a number with Paste Special

Alternatively, right-click the selected numbers, select Paste Special… from the context menu, then select Divide under Operation, and click OK.
Excel Paste Special dialog

Either way, each of the selected numbers in column A will be divided by the number in D5, and the results will be returned as values, not formulas:
Selected numbers are divided by the specified number, and the results are values, not formulas.

How to divide by percentage in Excel

Since percentages are parts of larger whole things, some people think that to calculate percentage of a given number you should divide that number by percent. But that is a common delusion! To find percentages, you should multiply, not divide. For example, to find 20% of 80, you multiply 80 by 20% and get 16 as the result: 80*20%=16 or 80*0.2=16.

In what situations do you divide a number by percentage? For instance, to find X if a certain percent of X is Y. To make things clearer, let's solve this problem: 100 is 25% of what number?

To get the answer, convert the problem to this simple equation:

X = Y/P%

With Y equal to 100 and P to 25%, the formula takes the following shape: =100/25%

Since 25% is 25 parts of a hundred, you can safely replace the percentage with a decimal number: =100/0.25

As shown in the screenshot below, the result of both formulas is 400:
Dividing a number by percentage

For more examples of percentage formulas, please see How to calculate percentages in Excel.

Excel DIV/0 error

Division by zero is an operation for which there exists no answer, therefore it is disallowed. Whenever you try to divide a number by 0 or by an empty cell in Excel, you will get the divide by zero error (#DIV/0!). In some situations, that error indication might be useful, alerting you about possible faults in your data set.

In other scenarios, your formulas can just be waiting for input, so you may wish to replace Excel Div 0 error notations with empty cells or with your own message. That can be done by using either an IF formula or IFERROR function.

Suppress #DIV/0 error with IFERROR

The easiest way to handle the #DIV/0! error in Excel is to wrap your division formula in the IFERROR function like this:

=IFERROR(A2/B2, "")

The formula checks the result of the division, and if it evaluates to an error, returns an empty string (""), the result of the division otherwise.

Please have a look at the two worksheets below. Which one is more aesthetically pleasing?
Suppressing the DIV/0 error with IFERROR

Note. Excel's IFERROR function disguises not only #DIV/0! errors, but all other error types such as #N/A, #NAME?, #REF!, #VALUE!, etc. If you'd like to suppress specifically DIV/0 errors, then use an IF formula as shown in the next example.

Handle Excel DIV/0 error with IF formula

To mask only Div/0 errors in Excel, use an IF formula that checks if the divisor is equal (or not equal) to zero.

For example:

=IF(B2=0,"",A2/B2)

Or

=IF(B2<>0,A2/B2,"")

If the divisor is any number other than zero, the formulas divide cell A2 by B2. If B2 is 0 or blank, the formulas return nothing (empty string).
IF formula to replace the DIV/0 error with an empty string

Instead of an empty cell, you could also display a custom message like this:

=IF(B2<>0, A2/B2, "Error in calculation")
IF formula to replace the DIV/0 error with a custom message

How to divide with Ultimate Suite for Excel

If you are making your very first steps in Excel and do not feel comfortable with formulas yet, you can do division by using a mouse. All it takes is our Ultimate Suite installed in your Excel.

In one of the examples discussed earlier, we divided a column by a number with Excel's Paste Special. That involved a lot of mouse movement and two shortcuts. Now, let me show you a shorter way to do the same.

  1. Copy the numbers you want to divide in the "Results" column to prevent the original numbers from being overridden.
  2. Select the copied values (C2:C5 in the screenshot below).
  3. Go to the Ablebits tools tab > Calculate group, and do the following:
    • Select the divide sign (/) in the Operation box.
    • Type the number to divide by in the Value box.
    • Click the Calculate button.

    Divide in Excel by using the Calculate tools

Done! The entire column is divided by the specified number in the blink of an eye:
The entire column is divided by the specified number.

As with Excel Paste Special, the result of division is values, not formulas. So, you can safely move or copy the output to another location without worrying about updating formula references. You can even move or delete the original numbers, and your calculated numbers will be still safe and sound.

That is how you divide in Excel by using formulas or Calculate tools. If are curious to try this and many other useful features included with the Ultimate Suite for Excel, you are welcome to download 14-day trial version.

To have a closer look at the formulas discussed in this tutorial, feel free to download our sample workbook below. I thank you for reading and hope to see you on our blog next week!

Available downloads

Excel Division formula examples (.xlsx file)
Ultimate Suite - trial version (.exe file)

Table of contents

69 comments

  1. How do I correctly write this formula without errors? The formula is =sum(b3:j3)/(s3*9).

  2. Hi, I want to develop a dynamic table/row, the total sum of the row should always be 100. But the sequence inside the column should be larger to smaller num.

    Example if have 4 row, the the number should be 40,30,20,10.

    Example if have 5 row, the the number should be 40,30,15,10,5.

    Dragging down the row will automatically recalculate the sequence but total must always be 100. Not sure if this is possible because i also have a hard time coming out with the sequence pattern.

  3. i searched google but didn't find any solution i want to distribute an amount 1,000,000/- randomly in 51 different cells in a column result is equal to distributed amount not less or exceed. i tried randbetween but i have to fix the total manually i want it with just one formula how could i do that?

  4. Hello,
    I need help. I want to be able to type in a value in a cell and then the divided outcome comes out automatically in the next column. I am not sure if that is possible. I already know how to type in the formula into the second column and drag it down so it applies to every cell, but it needs to be quicker.

    For example,

    Column A | Column B
    $100 | $50
    $350 | $175
    $15000 | $7500
    $60 | $30

    I am trying to get the values in column A divided by 2 automatically showing in column 2. Thank you.

  5. Wish to to derive the Maintenance cost/ hour of a vehicle by dividing the sum of Maintenance costs in multiple vehicles of same model with the sum of hours utilized in Excel. Please help with any formula.

    Please be informed that Average of Averages from Pivot table is not yielding the desired result.

  6. Hello everybody.
    I want to ask how can we divide a whole number or decimal into two partitions and get the result into two columns so that each column contains one partition??
    Eg: 10/2 the result is two partitions and shows in two columns..
    Thanks everybody

      • Thanks alot
        But I want to ask you .. does this formula work with numbers??
        Thanks in advance.

        • I mean I want to divide a number (eg 13). The result of the division must be divided on two (13/2) and distributed in two cells (one cell is eg. 9 the other one is 4 ) ( each cell has a partition of the result).( (the sum of both cells is 13)
          What would be the formula?

          Thanks alot in advance.

            • I am wondering the same. Let me see if I can explain what I think ALAA3DARTS is trying to communicate.

              Essentially what I (or we) want is for a formula that will distribute the content of a cell between a set of counted cells along one row or column. This formula should render a sum that is either an equal distribution between counted cells or a set parameter.

              Using the numbers supplied: 13 should be either equally divided between two cells (4,5 and 4,5) or specified divided (9 which is 2/3 of 13 and 4 which is 1/3 of 13)

              Assuming that 13 is placed in c2, 9 is places in a2 and 4 is placed in b2.

              We are looking for the exact opposite of =sum(a2:b2). Normally you would have cell a2 as 9, b2 as 4 and c2 would be =sum(a2:b2) and return 13 as the summation of 9+4.

              BUT, what we want (or at least I do) is the following:

              have the number 13 in cell c2, and then reverse the formula to distribute c2 content equally or by a set scale between a2 and b2.

              Now with two cells it makes no sense to to this but I am looking to distribute 2440 among multiple cells sometimes up to 16 cells, where half the cells (every 2nd cell) need to be empty for the budget to make sense. This means that the formula must pick out the empty cells and only count the cells that have a formula in it in order to divide by the correct number.

              Trying to recreate an Excel sheet in text format made it look something like this:
              a b c d e f g h i
              1
              2 610 610 610 610 2440

              a2 c2 e2 and g2 contains an equal share of 2440 when you have 8 cells, but only 4 with actual content. 610 must be derived from a formula.

              I tried using these formulas: =i2/count(a2:h2) and =i2/countif(a2:h2, "")) . Moving around the paramenters I get either 0 or "circulatory reference in formula". So not working.

              Which formula may I use in cell a2 to give me the quotient of 2440 based on a count of the cells in the row that a2 belongs to?

  7. Hello,
    I was wondering if it is possible to have a formula in a cell such as this =C2-SUM(E2:AI2)/$B$24 Where B24 has formula =EOMONTH(B22,0)-B22?
    The first part of the equation works fine until I divide by cell B24 then the calculation is off.
    Could you please explain what I am doing wrong. I don't know if you can have one cell with a formula divided by another cell with a formula.
    Thanks for the help in advance.

  8. Hi sir
    I want to total value split into some parts but not exceed to 900 hundred
    Example:
    1000(units) x 6 (each unit rate) = 6000
    Above 6000 will divide and split into below 900 hundred (150 x 6 = 900) auto fill
    is there any formula tell me please

  9. Sir,
    If the total sum value is 100, I want to divide by 5 to all sum values of 10 and above. What will be a formula in excel for this?

      • No sir,
        If the sum total value is 100, I want to divide the next 90 values by 5, excluding the first 10. This requires a formula.

        It means if the sum of the number is greater than 10, I want to divide the number from 11 to 100 by 5. Please suggest a formula for this.

        The total sum of values is 100 ( SUM A1:J1 is 100) greater than 10 ....... means from 11 to 100 values divided by 5.

      • Hi I am Lara and I need help on this What formula would you use to divide the value in cell A1 by the value in cell B7?

  10. Hi Dear,
    Need Help.
    First, one (1) will be added to the specified number, then it will be divided by 3, if it is not evenly divided by three then it will be automatically rounded up, then the rounded up result will be multiplied by three to show the final result. All the formulas have to come in the same cell.

  11. I'm trying return an all encompassing excel formula for below.
    I think it has to be a nested if statement but can't work it out!

    Example:-
    250 / 100 = 150% growth
    100 / 200 = -50% decline
    0 / 100 = -100% decline
    100 / 0 = 100% growth
    0 / 0 = 0%

    Thanks in advance!

  12. please monthly wise yearly attendance sheet and month wise all data updated .

  13. =(B71+(B71*0.3)+E71+F71)/223 output of this formula I want to multiply

  14. I think I have the same problem as Mindy Banks who on the 21st of February posted : "All my cells worked as numbers with no issue using the / division until I sorted the range. Now the cells say #VALUE! - Function ADD parameter 1 expects number values. But 'score' is a text and cannot be coerced to a number
    However it's only 1 row, the other rows are fine despite all cells being number cells with division in them. Any help?"
    I selected the approriate area then put in the formula =(C3:I82)/(C84:I163), expecting each of the first set of values to be divided by the corresponding ones in the second set, no text involved. I got the #VALUE etc on the top row and numbers, incorrect ones, in the cells below.
    I don't know whether your suggestion solved Ms Banks' problem but it doesn't seem applicable to mine. Thanks for any help you can provide.

    • Hello!
      If you have sorted the range of numbers in which all the values of your formulas are located, then there will be no problem.
      Your formula will work in Excel365.

  15. > How to divide a cell in Excel

    *PLEASE* stop saying you are "dividing a cell". Ugh.
    You are dividing the numbers, you are NOT splitting the cell into 2 cells.

  16. I have a whole column "V" with a sum. I need that column or value divided by three represented on the U column.

    For clearer reference, column V is the price column, the U column is the cost. I need the cost to represent 1/3 of the value of the price.

    Help is much appreciated!

    • Hello!
      If I understand your task correctly, the following formula should work for you:

      =V1/3

      After that you can copy this formula down along the column.

  17. How to add multiple cells to a cell and divide a given number by the result obtained by it? Tell me quickly if you know the solution.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)