Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • Gmail
    • Productivity
      • Time Management
      • Task Automation
    Inside The Web
    Home»Excel»Excel Formulas not Calculating? Try these Fixes

    Excel Formulas not Calculating? Try these Fixes

    AsmiBy AsmiMarch 16, 2023 Excel 5 Mins Read

    Formulas are one of my favorite parts of using Excel. You can use one, or nest many in one function to create a formula that gets calculations done in a snap of a finger. However, there are a sea of reasons that could go wrong while performing calculations using formulas in Excel.

    There are multiple cases where your formulas seemingly won’t calculate. You could be met with an error message, or you could just see the formula and not the result when you hit enter. We have covered all cases where formulas don’t calculate in Excel. Keep reading this article to carry on with your calculations in no time!

    Case 1: You Can Only See the Formula, Not the Result

    If your formula does not return a value, there can be two reasons behind this issue. You could’ve either entered the formula in a cell that is formatted as text, or you’ve enabled the Show Formula option.

    When your cell is formatted as text, Excel will treat the content inside your cell as text. This means it will not perform any type of calculations, including running a formula. While you could change the text format to number, you can also change it to general to accommodate other values like dates.

    Similarly, when you enable the Show Formula options, all formulas in the spreadsheet will be displayed. This may appear as the formula not returning any value.

    Convert Cell from Text to General

    1. Select your cell from the grid.
    2. On the Home tab, select the fly-out menu from the Numbers section.
    3. Choose General.
      Convert cell to general Excel

    Remove the Aphostrophe in front of Your Formula

    If you have entered an apostrophe in front of your formula, it will command Excel to treat your formula as text. The apostrophe will not appear with the grid so if you’ve added the symbol, you must remove it from the formula bar.

    Remove Apostrophe from Cell Content Excel

    Select the cell with your formula and head to the formula bar. Remove the apostrophe in front of your formula and hit Enter. Your formula should now work.

    Disable the Show Formulas Option

    1. Head to the Formulas tab.
    2. In the Formula Auditing section, deselect Show Formulas.
      Disable show formulas Excel

    Case 2: Excel is not Calculating After Editing Referenced Cells

    In this case, your formula worked perfectly fine before you edited a cell your formula referenced. Now, the result isn’t updating after you’ve made changes to one of the cells you’ve referenced.

    This is because you’ve set your calculation settings to manual. Your formula will refresh once you hit the F9 key on your keyboard. If you find it a hassle to refresh it every time you make changes, you can change your calculation settings to automatic.

    1. Open your spreadsheet.
    2. Head to the Formulas tab from the menu bar.
    3. In the Calculation section, select the Calculation Options.
    4. Choose Automatic.
      Automatic Calculation Options Excel

    Case 3: Formula Results in an Error

    There are nine errors you may encounter while trying to use formulas in Excel. All of these errors are results of inconsistencies in your formula.

    Excel errors
    ErrorPossible CauseSolution
    #NAME?The function or reference is typed incorrectly or does not exist.Select the cell with your formula and check the function and reference name. If you have entered a text, make sure it is inside double quotes.
    #VALUE!The format of your reference is incompatible with the function.Check the cells you’ve passed in the formula. If they’re stored in a different format, head to Home and change the cell format from the Numbers section.
    #REF!The reference you passed in the formula does not exist.Verify if you’ve accidentally deleted the range you passed in the formula.
    #DIV/0!The formula divides any number with zero.Use Ctrl + Shift + { and check the cells referenced in your formula. See if your formula is using any cells with the DIV/0! error or, if it’s dividing any number with zero.
    ###The cell content does not fit.On your keyboard, use Alt,H,O,I to autofit the content to the cell.
    #N/AThe lookup value is not found within the lookup range.This is a very common instance while using lookup formulas. If you’re using VLOOKUP, only select the lookup value from the first column of the lookup range.
    #NUM!The argument passed in the function is invalid.Check your argument. If you have any currency symbol like $ used, remove it and run the formula again.
    #NULL!An incorrect range operator or, intersection does not exist between specified ranges.Use the correct range operator, which is the semicolon (:). If you intended to specify an intersection, check if it really does exist between the ranges you added the intersection operator (space) in between.
    Circular ReferenceYour formula is referencing the active cell.You cannot reference the cell you’re entering the formula on. If you’re looking to loop your formula several times, you can use the ROW function to create an array.

    Case 4: You’ve Nested More than 64 Functions in a Formula

    You cannot nest more than 64 functions in a single formula in Excel. You can try to use a single formula instead of nesting multiple formulas. 

    For example, you can use the VLOOKUP function instead of nesting the MATCH and INDEX functions. Similarly, you can opt to use one COUNTIFS function instead of nesting multiple COUNTF functions.

    Case 5: Formula is Calculating a Different Result

    So usually if your formula results in something off, you would want to check the arguments first. However, sometimes it’s not the formula that is at fault; it’s the function.

    For example, I created a named range, SUM. Now, as we know SUM is also a function. When I tried using the function to add two cells to the sheet, it gave me a completely different result. It is because I actually used the named range instead of the function.

    Named range used instead of formula

    This situation is also more likely when you create your own function. If you have created a named range with the same name as the function, verify if you’ve in fact used the function or the named range.

    Excel Error Excel Functions Formula
    Asmi

      Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).

      Add A Comment
      Table of Contents
      • Case 1: You Can Only See the Formula, Not the Result
        • Convert Cell from Text to General
        • Remove the Aphostrophe in front of Your Formula
        • Disable the Show Formulas Option
      • Case 2: Excel is not Calculating After Editing Referenced Cells
      • Case 3: Formula Results in an Error
      • Case 4: You’ve Nested More than 64 Functions in a Formula
      • Case 5: Formula is Calculating a Different Result
      Recent Posts
      • How to Lock a Cell in Excel Formula
      • How to Make Gridlines Darker in Excel
      • How to Apply Cell Style in Excel
      • How to Apply Top and Bottom Border in Excel
      • How to Retrieve Last Value in Column Excel
      • Home
      • Privacy Policy
      © 2023 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.