How to Fix Division Formula Not Working in Excel

This article will go over some methods you can try to fix division formulas not working in Excel.

Division in Excel requires only the use of the ‘/’ operator between two numeric values. While easy to use, users may encounter some issues when performing division in Microsoft Excel.

Let’s take a look at a simple example where you might encounter a division error.

Suppose you have two columns of values. The first column refers to a list of dividends, and the other column refers to a list of divisors.

We can use the ‘/’ operator to create a third column that outputs the quotient of the first two columns. Upon setting up this column, however, you encounter an issue with obtaining the right result.

There are a few possible reasons why you’re not receiving the right result. In the next section, we’ll explore some of the most common reasons why the division formula is not working in Excel.

 

 

Real Examples of Division Formulas Not Working in Excel

In the following section, we will provide a few examples of the division operation not working in Excel. We’ll also provide an in-depth explanation on why these errors occur.

First, let’s take a look at an example where the division fails because of a formatting issue.

In the table below, our division operation in cell C2 returns an error. The #VALUE! error is a result of the dividend being an invalid numeric value. While Excel typically converts numerical strings automatically, the inclusion of ‘USD’ prevents Excel from parsing the value as a number. 

division formula not working in Excel because of string

 

To fix this issue, we’ll have to remove the ‘USD’ currency marker from the string. If the currency must be represented in the string, a better alternative would be adding a ‘Currency’ or ‘Accounting’ formatting to the cell.

Next, we may also encounter the #DIV/0! error when dividing in Excel. While this error often occurs when the divisor is explicitly 0, you may also encounter this error if the divisor references a blank cell.

division formula not working in Excel because of blank cell

 

Since Excel considers blank cells as having a value of 0, the only way to avoid this error is to catch it with a function such as IFERROR.

In this third example, our division operation fails because the divisor is now a ‘#REF!’ string. This often occurs when deleting rows and columns in Excel. We can fix this issue by typing in the appropriate cell reference.

#REF! error

 

In this last example, we’re unable to evaluate the division formula across the entire row. The cells are not updating to the appropriate answer, even when using the Fill Handle tool. If you are encountering this error, your Excel program may be set to Manual Calculation mode.

calculation mode is manual

 

To fix this issue, we must adjust our program’s settings from Manual to Automatic.

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try out our methods to fix the division formula, head over to the next section to read our step-by-step breakdown on how to do them!

 

 

How to Fix Division Formula Not Working in Excel

This section will guide you through each step needed to fix the division formula when it is not working properly. The methods you’ll learn include changing the calculation options, formatting cells to numbers, and handling #REF! errors.

Follow these steps to learn how to fix division errors in Excel:

  1. If your division fails to update automatically, your Excel program may be running on Manual Calculation mode. We can change this setting by heading over to the Formulas tab and selecting the Calculation Options dropdown menu.
    select calculation options
  2. In the Calculation Options dropdown menu, select the Automatic option.
    set to automatic calculation
  3. Once your Excel program is in Automatic Calculation mode, the division operator should update automatically.
  4. If the issue still persists, there may be an issue with cell formatting. Both your dividend and divisor must be formatted as a numerical value. You may find the list of formatting options under the Number group in the Home tab.
    fix division formula not working in Excel by converting format
  5. Your division formula may also fail if one of the cell references no longer exists. This may occur if you’ve deleted the row or column of the referenced cell. To fix this issue, modify the formula to direct to the new cell reference.
    fix #REF! error

These are some of the most common methods to fix cases where the division formula is not working in Excel. 

 

 

This step-by-step guide should be all you need to fix issues with your division formula in Microsoft Excel. Our guide shows how to handle issues related to calculation modes, missing cell references, and improper formatting. 

The division operation is one example of a mathematical function you can use in Google Sheets. If you are having difficulty working with Excel formulas not updating in general, we also have a step-by-step guide that may help.

Overall, our website covers hundreds of other Excel methods and functions that you can apply to your own spreadsheets. With so many other Excel functions available, you can surely find one that suits your use case. 

Are you interested in learning more about what Google Sheets can do? Subscribe to our newsletter to look into the latest Google Sheets guides and tutorials from us. 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like