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

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:

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

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 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:

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.

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

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.

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.

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

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:

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?

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

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

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