[Fixed]: Excel Formula Not Showing Correct Result (8 Methods)

While using formulas in Excel, you might not get the result you expect the formula will return, or sometimes the formula will even throw an error. Getting such an anomalous result or an unexpected error could be quite frustrating if you do not know how to fix it. In this tutorial, I will show you how to fix an Excel formula not showing the correct result in 8 very easy and simple ways.

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.

8 Easy Ways to Fix Excel Formula Not Showing Correct Result Error

Let’s assume we have an Excel worksheet that has the information about the revenues of different outlets of a chain restaurant for January and February. We will use this workbook to show you how to fix an Excel formula not showing the correct result. The image below shows the worksheet we are going to work with.

Method 1: Change the Formula Calculation to Automatic from Manual in Excel

Sometimes while updating the cell values, you will find that the Excel formula is not updating results automatically. That happens because the formula calculation is set to Manual instead of Automatic. In such a case you will have to switch the calculation option to Automatic from Manual. Let’s see how we can do that.

Step 1:

• First, we will have to click on the File.

• Then, a new window will appear. Next, we will click on the Options from that window.

• Now, another window will appear. We will click on the Formula tab from that window.
• Then, we will select the Automatic as Workbook Calculation option under the Calculation options
• Finally, we will click on OK.

Step 2:

• Alternatively, you can click on the Calculation Options drop-down under the Formulas.
• Then, we will select Automatic from the drop-down list.

Step 3:

• When you are working with a large worksheet with a lot of columns or rows, you might not want the formula you used to automatically updates the results each time you change or add a cell value. In such a case, changing the calculation option to Manual is a good idea. But after changing or adding all the cell values you will need the formula to calculate the final result. You can do that very easily.
• First, you will need to select all the cells containing the formula.
• Then, you will then click on Calculate Now under the Formula The formula will then calculate all the cells and returns the updated result.

Read More: [Solved]: Excel Formulas Not Updating Until Save (6 Possible Solutions)

Method 2: Turn Off the Show Formula Button in Excel to Show Correct Result

After inserting the formula into a cell. You will find that instead of showing a result, Excel is showing the formula as the cell value. That happens because you have accidentally selected the Show Formula option. We have to do the following to undo that.

Steps:

• Click on the Show Formulas option under the Formulas.

• You will now see that the Show Formulas option is not selected anymore and the Excel formulas are showing the correct result in the Total (Outlet) column.

Read More: How to Stop Showing Formulas in Excel (2 Methods)

Method 3: Change the Format of the Formula to Number from Text

If the format of the cells containing formulas is changed to text, then Excel will show the formula as the cell value instead of showing a result. We will follow the below steps to change the format from text to number.

Steps:

• The image below shows the cells containing the format of the cells in the Total (Outlet) column is Text instead of Number and the formulas are shown as cell values instead of the results.

• First, we will select all the cells of the January, February, and Total (Outlet) columns.
• Then, we will change the cell format to Number.

• Next, we will click on cell E5 and press ENTER. We will see that formula is now showing the result.

• Now we will drag the fill handle downward to change the format to number for the rest of the cells in the Total (Outlet) column.

• Finally, we will see that each of the cells in the column is now showing the correct results.

Read More: [Fixed!] Formula Not Working and Showing as Text in Excel

Method 4: Check If there Is Any Circular Reference in Formula

Circular Reference occurs when the cell containing the formula is used in that formula. In such a case, Excel will show zero (0) instead of showing a result. Let’s see how we can fix that.

Steps:

• The image below shows that the cell E5 in the Total (Outlet) column is showing zero (0) instead of results. The formula in that cell has included the cell itself.
• If we look at the sheet tab, we will see that it is showing Circular Reference indicating that cell E5 contains a formula that includes the cell itself.

• First, we will change the formula to =SUM(C5:D5) to remove the cell E5 from the formula that contains it.

• Then, upon pressing ENTER, we will see that the formula is now showing the correct result instead of zero (0).
• Next, we will drag the fill handle downward to remove the circular references from each cell in the Total (Outlet) column.

• Finally, we will see that formula of each of the cells in the column is now showing the correct result.

Read More: [Fixed!] Why Formula Is Not Working in Excel (15 Reasons with Solutions)

Similar Readings

• Excel Show Formula as Text in Another Cell (4 Easy Methods)
• Show Formula in Excel Cells Instead of Value (6 Ways)
• [Solved]: Excel Array Formula Not Showing Result (4 Suitable Solutions)
• [Solved:] Excel Formula Not Working unless Double Click Cell (5 Solutions)
• [Fixed!] Excel Formulas Not Working on Another Computer (5 Solutions)

Method 5: Check If the Equal Sign Is Missing in Formula to Fix the Problem of Not Showing Correct Result

Sometimes while inserting the formula, we might forget to include the equal sign (=) in front of the formula. If we do not add the equal sign (=) in front of the formula, Excel will consider it as text and simply shows the formula as the cell value instead of returning a result. We will follow the below steps to avoid that.

Steps:

• The image below shows that Excel is showing the formula as the cell values in the Total (Outlet) The formulas do not have equal signs in front of them. So, Excel considers them as texts.

• First, we will insert an equal sign (=) in front of the formula in cell E5. Now, we will see that cell references in the SUM formula are colored in blue indicating that formula is working.

• Then, upon pressing ENTER, we will see that the formula in cell E5 is showing the correct result.

• Now we will drag the fill handle downward to insert an equal sign (=) in front of each cell in the Total (Outlet).

• Finally, we will see that formula of each of the cells in the column is now showing the correct result.

Read More: [Fixed!] SUM Formula Not Working in Excel (8 Reasons with Solutions)

Method 6: Find Out If There Is Any Hidden Rows or Columns in Excel to Fix the Formula Not Showing the Correct Result

Sometimes we might accidentally hide the rows and columns that are included in the formula, Excel will take those columns or rows into consideration even if they are hidden and can not be seen. So, you might get confused by the result such a formula will generate taking the hidden rows or columns into consideration. Let’s see how we can unhide such rows or columns to make them visible again.

Steps:

• The image below shows that the formulas in the cells under the Total (Outlet) column are not showing the correct result. The formula includes column E but there is no column E in the worksheet as you can see. But if you look carefully, you will see that there is a small gap between columns D and F. The gap indicates that there is a hidden column and that column is column E. We will now unhide column E.

• First, we will select columns D and F. Hidden column E is between these two columns.
• Next, we will go to Home > Format > Hide & Unhide > Unhide Columns.

• Now, we will see that column E is visible.

• Upon pressing ENTER, we will see that all the formulas in Total (Outlet) are showing the correct result.

Method 7: Check If there are Any Deleted Rows or Columns Leading to the #REF! Error

If you accidentally delete a column or row that is included in the formula, you will get an #REF! error as the formula will not get the input it needs to calculate the result. In such a case, we will do the following.

Steps:

• The image below shows that formulas in the Tax Amount column throw #REF! errors as they do not have tax rates to calculate the tax amounts. That happens because we have accidentally deleted the column that has the tax rates.

• First, we will select cell C5 and right-click on it to bring up the context menu.
• Then, we will select Insert from that menu.

• Now, another window titled Insert will appear. We will select the Entire column option from that window.
• Then, we will click on OK.

• A new column will now appear. We will title it Tax Rate.
• After that, we will rewrite the formula in cell E5 like below.

• Then, upon pressing ENTER, we will see that the formula in cell E5 is showing the correct result.
• Now we will drag the fill handle downward to apply the change to each formula in the Total (Outlet).

• Finally, upon pressing ENTER, we will see that all the formulas in Total (Outlet) are showing the correct result instead of showing #REF! error.

Read More: How to Show Value Instead of Formula in Excel (7 Methods)

Method 8: Correctly Place Parenthesis in Excel Formula to Fix Formula Not Showing Correct Result

If the formula we are using in Excel has different types of arithmetic calculations and we do not insert the parenthesis around the cell values and numbers correctly, the formula can show an anomalous result instead of showing the correct result. Let’s see how we can solve this problem.

Steps:

• The image below shows that the formula in cell E5 is not showing the correct total tax amount as we do not insert parentheses around the cell references.

• We will rewrite the formula like below to insert parentheses around the cell references in the correct way.

• Then, upon pressing ENTER, we will see that the formula in cell E5 is showing the correct tax amount.
• Next, we will do the same for the rest of the formulas in the Total Tax Amount.

Quick Notes

• You should include all the arguments required for a specific formula to calculate the result. You do not have to worry about the optional arguments. A formula still can calculate the result without the optional ones.
• While nesting one formula inside another, you should not nest more than 64 functions inside a single formula.
• If you enclose a number in double-quotes, Excel will consider it as a text instead of a number.
• While entering or inserting numbers in an Excel formula, you should not use any sort of formatting. Remember that, a comma (,) is used in an Excel function to separate the arguments of the function. A dollar sign ($) is used to make a cell reference absolute.
• You should separate the arguments of an Excel function with the correct separator. Users in North America should separate the arguments using the comma (,) while those in Europe should do that using the semi-colon (;). Go to Control Panel > Region and Language > Additional Settings which character is set as List Separator and use that character to separate the arguments of a function.
• When referring to other worksheets or workbooks with titles that contain spaces or non-alphabetic characters, use single quotation marks around the name of that worksheet or workbooks. For Example, reference to another sheet,

Reference to another workbook,

• When referring to a closed workbook that is closed, you must include the name of that workbook along with the full path of that workbook. For example,

Conclusion

In this article, we have learned how to fix an Excel formula not showing the correct result. I hope from now on you can fix an Excel formula not showing the correct result easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!

Related Articles

• How to Show All Formulas in Excel (4 Easy & Quick Methods)
• Show Formulas When Printing in Excel
• [Fixed!] Formula Result Showing 0 in Excel (3 Solutions)
• How to Refresh Formulas in Excel (2 Easy Methods)
• Excel Showing Formula Instead of Result (8 Reasons)
• How to Convert Text to Formula Using the INDIRECT Function in Excel