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

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Excel Formula Not Showing Correct Result


Fix 1: Changing 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.

Change If the Formula Calculation to Automatic from Manual in Excel

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

Change If the Formula Calculation to Automatic from Manual in Excel

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

Change If the Formula Calculation to Automatic from Manual in Excel

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.

Change If the Formula Calculation to Automatic from Manual in Excel

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.

Change If the Formula Calculation to Automatic from Manual in Excel

Read More: Excel Formulas Not Calculating Automatically


Fix 2: Turning Off Show Formula Button in Excel to Display 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.

Turn Off the Show Formula Button in Excel to Show Correct Result

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

Turn Off the Show Formula Button in Excel to Show Correct Result


Fix 3: Changing Format of Excel 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.

Change the Format of the Formula to Number from Text

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

Change the Format of the Formula to Number from Text

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

Change the Format of the Formula to Number from Text

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

Change the Format of the Formula to Number from Text

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

excel formula not showing correct result

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


Fix 4: Checking If There Is Any Circular Reference in Excel 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 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 a circular reference indicating that cell E5 contains a formula that includes the cell itself.

Check If there Is Any Circular Reference in Formula

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

excel formula not showing correct result

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

Check If there Is Any Circular Reference in Formula

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

Check If there Is Any Circular Reference in Formula


Fix 5: Checking If Equal Sign Is Missing in Formula to Fix Problem for 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.

excel formula not showing correct result

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

Check If the Equal Sign Is Missing in Formula

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

excel formula not showing correct result

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

Check If the Equal Sign Is Missing in Formula

Read More: [Fixed!] SUM Formula Not Working in Excel


Fix 6: Finding Out If There Is Any Hidden Rows or Columns to Fix Excel Formula for Not Showing 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.

Find Out If There Is Any Hidden Rows or Columns in Excel

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

excel formula not showing correct result

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

Find Out If There Is Any Hidden Rows or Columns in Excel

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

excel formula not showing correct result


Fix 7: Checking 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.

Find Out If There Is Any Hidden Rows or Columns in Excel

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

excel formula not showing correct result

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

Find Out If There Is Any Hidden Rows or Columns in Excel

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

Find Out If There Is Any Hidden Rows or Columns in Excel

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

excel formula not showing correct result

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

Find Out If There Is Any Hidden Rows or Columns in Excel


Fix 8: Placing Parenthesis Correctly to Fix Excel Formula for 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.

excel formula not showing correct result

  • We will rewrite the formula like below to insert parentheses around the cell references in the correct way.
=(C5+D5)*10%
  • 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.

Correctly Place Parenthesis in Excel Formula


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,
=SUM(‘Tax Calculation’!B2:B10)

Reference to another workbook,

=SUM(‘[Restaurant Sales.xlsx]Tax Calculation’!B2:B10)
  • 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,
=SUM(‘E:\Reports\[Restaurant Sales.xlsx]Tax Calculation’!B2:B10)

Download Practice Workbook

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


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


<< Go Back To Formulas not Working in Excel | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
ASM Arman
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo