Pinterest Pixel

Why Excel Formula giving Wrong Answers?

Ready to present your report but at the last minute Excel formula giving wrong answer or not... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Break Line on Worksheet

Ready to present your report but at the last minute Excel formula giving wrong answer or not updating?

You are not alone!

In this article, we will cover 3 reasons why your formula may not be working properly and how to troubleshoot Excel formula:

  1. Calculation is configured to “Manual”
  2. Formula button is turned on
  3. Formula is stored as text

 

Download this Excel workbook so you can practice along with us:

download excel workbookFormula-not-working.xlsx

 

1. Calculation is configured to “Manual”

Suppose you have a worksheet calculating the total expenses and incomes.

Why Excel Formula giving Wrong Answers?

Say, you update a value in the source data. i.e. electricity for the year 2020 changed from $1944 to $2210. You notice that the total expense contains old values, even though the source data for the formula-bearing cells have been changed.

Why Excel Formula giving Wrong Answers?

This means that your formulas are not updating automatically as and when the source data is changing. It is a simple settings issue why Excel formula giving wrong answers and you can easily fix it

The reason that Excel is not updating your formulas is the calculation method of Excel has been changed from Automatic to Manual. There are two ways to correct this:

 

Method 1: Change Formula setting back to Automatic

STEP 1: Select the File tab.

Top 20 Common Excel Problems Solved

STEP 2: Click Options from the menu on the left side

Top 20 Common Excel Problems Solved

STEP 3: In the dialog box, click on Formulas from the left panel

Why Excel Formula giving Wrong Answers?

STEP 4: Under the Calculation options section, select Automatic under Workbook Calculation.

Why Excel Formula giving Wrong Answers?

Or, you could simply go to the Formulas tab > Calculation group, click the Calculation Options button, and select Automatic.

Why Excel Formula giving Wrong Answers?

After this, your formulas will be calculated as and when the dependant cell values are changed.

 

Method 2: Recalculate the Entire Sheet

You can force Excel to recalculate the entire sheet or workbook by the following steps. To recalculate the entire workbook:

Click the Calculate Now button on the Formulas tab > Calculation group.

Why Excel Formula giving Wrong Answers?

You could simply press F9 to recalculate the entire worksheet and Shift + F9 to recalculate the active worksheet only.

This will force Excel to recalculate all the formulas in the active worksheet or the entire workbook, as the case may be.

 

2. Show Formula button is turned on

If your formula is displaying the function instead of the calculated result, it is because the Show Formula mode is turned on.

Why Excel Formula giving Wrong Answers?

To turn it off:

Go to Formula Tab > Under Formula Auditing group, Click Show Formulas.

Why Excel Formula giving Wrong Answers?

Or, Press Ctrl + `

This will turn off the show formula mode and show the calculated result!

Why Excel Formula giving Wrong Answers?

 

3. Formula is stored as text

Even after turning show formula mode off, the cell is displaying function as shown in the image below:

Why Excel Formula giving Wrong Answers?

This is because your formula is stored as text.

Why Excel Formula giving Wrong Answers?

To change it, go to Home tab> Select General from the dropdown.

Why Excel Formula giving Wrong Answers?

Then, press F2 and then Enter to recalculate the value in the cell. Your calculated result will now be displayed!

Why Excel Formula giving Wrong Answers?

 

Conclusion

So now it must be clear that Excel was not automatically recalculating your formulas due to a settings issue and we hope that after watching this Excel troubleshoot formula tutorial, your problem has been solved and you can continue with smooth functioning on Excel!

Further Learning:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!