{"id":24498,"date":"2021-07-02T01:33:42","date_gmt":"2021-07-01T23:33:42","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=24498"},"modified":"2024-02-06T16:06:06","modified_gmt":"2024-02-06T15:06:06","slug":"excel-formula-giving-wrong-answers","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-formula-giving-wrong-answers\/","title":{"rendered":"Why Excel Formula giving Wrong Answers?"},"content":{"rendered":"

\"Break<\/a><\/p>\n

Ready to present your report<\/strong> but at the last minute Excel formula<\/a> giving wrong answer <\/strong>or not updating<\/strong>?<\/p>\n

You are not alone!<\/p>\n

In this article, we will cover 3 reasons why your formula may not be working properly and how to troubleshoot Excel formula<\/strong>:<\/p>\n

    \n
  1. Calculation is configured to \u201cManual\u201d<\/strong><\/a><\/li>\n
  2. Formula button is turned on<\/strong><\/a><\/li>\n
  3. Formula is stored as text<\/strong><\/a><\/li>\n<\/ol>\n

     <\/p>\n

    Download this Excel workbook<\/strong> so you can practice along with us:<\/p>\n

    \n

    download excel workbook<\/strong><\/span>Formula-not-working.xlsx<\/span><\/a><\/p>\n<\/div>\n

     <\/p>\n

    <\/a>1. Calculation is configured to \u201cManual\u201d<\/span><\/h3>\n

    Suppose you have a worksheet calculating the total expenses and incomes<\/strong>.<\/p>\n

    \"Why<\/a><\/p>\n

    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<\/strong>, even though the source data for the formula-bearing cells have been changed<\/strong>.<\/p>\n

    \"Why<\/a><\/p>\n

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

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

     <\/p>\n

    Method 1: Change Formula setting back to Automatic<\/span><\/strong><\/p>\n

    STEP 1:<\/strong><\/span> Select the File<\/strong> tab.<\/p>\n

    \"Top<\/a><\/p>\n

    STEP 2:<\/span><\/strong> Click Options<\/strong> from the menu on the left side<\/p>\n

    \"Top<\/a><\/p>\n

    STEP 3:<\/strong><\/span> In the dialog box, click on Formulas <\/b>from the left panel<\/p>\n

    \"Why<\/a><\/p>\n

    STEP 4: <\/strong><\/span>Under the Calculation options section, select Automatic<\/strong> under Workbook Calculation.<\/p>\n

    \"Why<\/a><\/p>\n

    Or, you could simply go to the Formulas tab > Calculation group<\/strong>, click the Calculation Options<\/strong> button, and select Automatic<\/strong>.<\/p>\n

    \"Why<\/a><\/p>\n

    After this, your formulas will be calculated as and when the dependant cell values are changed<\/strong>.<\/p>\n

     <\/p>\n

    Method 2: Recalculate the Entire Sheet<\/span><\/strong><\/p>\n

    You can force Excel to recalculate the entire sheet or workbook by the following steps. To recalculate the entire workbook:<\/p>\n

    Click the Calculate Now<\/strong> button on the Formulas tab > Calculation group.<\/p>\n

    \"Why<\/a><\/p>\n

    You could simply press F9 to recalculate the entire worksheet<\/strong> and Shift + F9 to recalculate the active worksheet<\/strong> only.<\/p>\n

    This will force Excel to recalculate all the formulas in the active worksheet or the entire workbook, as the case may be.<\/p>\n

     <\/p>\n

    <\/a>2. Show Formula button is turned on<\/strong><\/span><\/h3>\n

    If your formula is displaying the function instead of the calculated result, it is because the Show Formula mode is turned on.<\/p>\n

    \"Why<\/a><\/p>\n

    To turn it off:<\/p>\n

    Go to Formula Tab<\/strong> > Under Formula Auditing group, Click Show Formulas<\/strong>.<\/p>\n

    \"Why<\/a><\/p>\n

    Or,\u00a0Press Ctrl + `<\/strong><\/p>\n

    This will turn off the show formula mode and show the calculated result!<\/p>\n

    \"Why<\/a><\/p>\n

     <\/p>\n

    <\/a>3. Formula is stored as text<\/strong><\/span><\/h3>\n

    Even after turning show formula mode off, the cell is displaying function as shown in the image below:<\/p>\n

    \"Why<\/a><\/p>\n

    This is because your formula is stored as text<\/strong>.<\/p>\n

    \"Why<\/a><\/p>\n

    To change it, go to Home tab<\/strong>> Select General<\/strong> from the dropdown.<\/p>\n

    \"Why<\/a><\/p>\n

    Then, press F2 and then Enter<\/strong> to recalculate the value in the cell. Your calculated result will now be displayed!<\/p>\n

    \"Why<\/a><\/p>\n

     <\/p>\n

    Conclusion<\/span><\/h3>\n

    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!<\/p>\n

    Further Learning:<\/strong><\/p>\n