How-To

How to Fix Excel Not Dividing Correctly

By Adam Davidson

Published July 24, 2023

Are your Excel formulas misbehaving? If you’re having issues with Excel not dividing correctly, follow this guide to fix the problem.

Excel is a powerful spreadsheet application that includes a wide range of functions that you can use in your formulas. Some of these functions are quite complex and require multiple arguments. If you miss out something important from your formula’s syntax, the function won’t work.

Sometimes, however, even the simplest functions can go wrong. If you’re just trying to divide one number by another in Excel, you may still find that you’re not seeing the answer that you would expect.

Here are some options to try if you’re having issues with Excel not dividing correctly.

Dividing by Zero

One of the most common reasons for Excel not dividing correctly is if you’re trying to divide by zero. It’s not that Excel doesn’t want to give you the answer—it’s that it can’t.

That’s because dividing by zero is undefined in mathematics; there is no ‘right’ answer. Division is the inverse of multiplication, but any number multiplied by zero gives zero. 5 × 0 = 0, 10 × 0 = 0, 156314 × 0 = 0. This means it’s impossible to reverse the process when dividing by zero because the answer could legitimately be 5, 10, 156314, or any other number in existence.

Thankfully, if you try to divide by zero in Excel, you won’t destroy the space-time continuum. You’ll just get an error reading #DIV/0! which indicates that the value you’re trying to divide by is zero. You’ll also see this error if the cell that you’re dividing by in your formula is empty; Excel will treat the empty cell as having a value of zero.

Check your formula and determine which cell is the divisor in your formula. Ensure that this cell is not a zero, or blank, and the error should disappear.

Replacing #DIV/0! Errors

If you’re working with a large amount of data, and performing a division across all of it, you may intentionally have some instances where you’re dividing by zero. If this is the case, you can use a simple formula to remove all the #DIV/0! errors and replace them with whatever you want.

To replace #DIV/0! errors in your Excel spreadsheet:

1. Select the first cell where you want your division results to appear.
2. Type =IF(
   
   
3. Select the cell that you are dividing by.
   
4. Type =0, “N/A”,
5. Select the cell you want to divide, type / and select the cell you are dividing by.
   
6. Type a final closed bracket, and press Enter.
   
7. To apply the formula to other cells, click the drag handle in the bottom right corner and drag down over the cells where you want the formula to appear.
   
8. If the formula is divided by zero, the result will now read N/A. Any other result will give the correct division result as usual. You can change N/A to anything you wish.
   

Cells Don’t Display All Digits

If you don’t think that a division calculation looks correct in your Excel spreadsheet, you may be tempted to check the result on a calculator. You might then find that the answer on your calculator doesn’t match the answer in your spreadsheet. However, this doesn’t necessarily mean that Excel is not dividing correctly.

It may simply be the case that your cells are currently too narrow to show the full range of digits. By expanding the cells, you can see that the division is accurately correct.

To expand cells in Excel:

1. Select your first column header, hold down Shift, and select any other headers of the columns you want to expand.
2. Click and hold the right-hand side of one of the column headers.
   
3. Drag to the right until all the digits of your cell values are visible.
   
4. Check your calculation again.

Check the Cell Format

If instead of the result of the division you expect, you get a #VALUE error in the cell, then it may be that one or more of your values are not formatted as numbers. Excel is capable of using the value of a cell in a calculation even if the number is formatted as text. If you do see a #VALUE error, however, then it may be that your cell being formatted as text is causing things to go wrong.

To format a cell as a number:

1. Check the format of your cells by clicking on them and looking at the cell format in the Home ribbon.
   
2. Select any cells that are in the wrong format by holding down Shift and selecting the relevant cells.
3. Right-click and select Format Cells.
   
4. Under Category select Number.
   
5. Choose the number of decimal places you want to use and click OK.
   
6. See if your division calculation works correctly now.

Turn off Manual Calculation

When you’re working with large spreadsheets that contain a lot of calculations, Excel can start to slow down when it has to perform all the calculations in your document. It may take several seconds for the calculations to be performed, making your document less responsive. By default, your formulas will recalculate every time you make a change to a cell value, but you might not want this to happen if it’s causing your spreadsheet to grind to a halt.

That’s why there’s an option to turn on manual calculation so that the formulas will only calculate when you manually click a button.

However, if this setting is turned on, your division calculation may seem like it’s not working, because it won’t actually calculate until you force it to. In this case, you can use the manual calculation button, or just turn off manual calculation altogether.

To turn off manual calculations in Excel:

1. Click the File menu.
   
2. At the bottom of the left-hand menu, click Options.
   
3. Select the Formulas menu.
   
4. Under Calculation Options, select Automatic.
   
5. Alternatively, to manually perform calculations, click the Formulas menu and select Calculate Now.
   

Fix Incorrect References

If instead of the answer to your division calculation, you see the #REF error message, this indicates that one of the cell references in your formula no longer exists. If you delete a column from your spreadsheet, for example, you may accidentally remove data that is being referenced by your calculation.

You can check your formula to see which parts of your formula are causing the problem.

To fix incorrect references in an Excel formula:

1. Click the cell that contains your formula.
2. In the Formula Bar look for any sections of your formula that say #REF instead of a cell reference.
   
3. Highlight the section that reads #REF.
   
4. Select the cell that contains the value that should appear in your formula.
5. If the cell no longer exists, you may need to enter the data back into your spreadsheet again.

More Excel Tips and Tricks

If you were having issues with Excel not dividing correctly, then one of the options above will hopefully help. If you find any other useful ways to fix your problem, then please let us know in the comments below.

There are plenty of other useful tips to know if Excel is causing you difficulties. You can learn how to fix Excel not sorting numbers correctly or fix the ‘retrieving data’ error. You can also learn how to find and remove external links in Excel if they’re the cause of your reference errors.