Excel shows formula but not result (2024)

Every once in a while, you might find Excel behaving in a bizarre or unexpected way. One example is when you accidentally trigger the scroll lock feature. Another example is when one or more formulas suddenly stops working. Instead of a result, you see only a formula, as in the screen below:

Excel shows formula but not result (1)

The VLOOKUP formula is correct, why no result?

This can be very confusing, and you might think you've somehow broken your spreadsheet. However, it's likely a simple problem. With a little troubleshooting, you can get things working again. There are two main reasons you might see a formula instead of a result:

  1. You accidentally enabled Show Formulas
  2. Excel thinks your formula is text

I'll walk through each case with some examples.

Show Formulas is enabled

Excel has a feature called Show Formulasthat toggles the display of formula results and actual formulas.Show Formulas is meant to give you a quick way to see all formulas in a worksheet. However, if you accidentally trigger this mode, it can be quite disorienting. With Show Formulas enabled,columns are expanded, and every formula in a worksheet is displayed with no results anywhere in sight, as shown below.

Excel shows formula but not result (2)

Show Formulas disabled (normal mode)

Excel shows formula but not result (3)

Show Formulas enabled

To check if Show Formulas is turned on, visit the Formula tab in the ribbon and check the Show Formulas button:

Excel shows formula but not result (4)

Show Formulas enabled -just click to disable

The reason Show Formulas can be accidentally enabled is because it has a keyboard shortcut (Control +`) that a user might accidentally type. Try typing Control + ` in a worksheet to see how it works. You should see formulas toggled on and off each time you use the shortcut.

Watch a video with over 20 formula tips

Show Formulas toggles the display of every formula in a worksheet. If you are having trouble with a single formula, the problem isn't Show Formulas. Instead, Excel probably thinks the formula is text. Read on for more information.

Excel thinks your formula is text

If Excel thinks a formula is just text and not an actual formula, it will simply display the text without trying to evaluate it as a formula. There are severalsituations that might cause this behavior.

No equal sign

First, you may have forgotten the equal sign. All formulas in Excel must beginwith an equal sign (=).If you leave this out, Excel will simply treat the formula as text:

Excel shows formula but not result (5)

Broken formula example - no equal sign (=)

Space before the equal sign

A subtle variation of this problem can occur if there is one or more spaces before the equal sign. A single space can be hard to spot, but it breaks the rule that all formulas must start with an equal sign, so it will break the formula as shown below:

Excel shows formula but not result (6)

Formula wrapped in quotes

Finally, make sure the formula is not wrapped in quotes. Sometimes, when people mention a formula online, they will use quotes, like this:

"=A1"

In Excel, quotes are used to signify a text value, so if you enter a formula like this, the formula will not be evaluated. You can see an example of this in the screen below:

Excel shows formula but not result (7)

Note: you are free to use quotes inside formulas. In this case, the formula above requiresquotes around criteria.

In all of the examples above, just edit the formula so it begins with an equal sign and all should be well:

Excel shows formula but not result (8)

For reference, here is the working formula:

=SUMIFS(D5:D9,E5:E9,">30")

Cell format set to Text

Finally, every once in a while, you might see a formula that is well-formed in every way but somehow does not display a result. If you run into a formula like this, check to see if the cell format is set to Text.

Excel shows formula but not result (9)

If so, set the formatto General (Control + ~), or another suitable number format. You may need to enter cell edit mode (click into the formula bar, or use F2, then enter) to get Excel to recognize the format change. Excel should then recognize the formula.

After setting the format to General, you may need to enter the formula again to make Excel evaluate it properly.

Tip - Save formula in progress as text

Although a broken formula is never fun, you can sometimes use the "formula as text problem" to your advantage, asa way to save work in progress on a tricky formula. Normally, if you try to enter a formula in an unfinished state, Excel will throw an error, stopping you from entering the formula. However, if you add a single quote before the equal sign Excel will treat the formula as text and let you enter it without complaint. The single quote reminds you that the formula has been intentionally converted to text:

Excel shows formula but not result (10)

Later, you can resume work on the formula again, starting where you left off.See #17 in this list for more info.

Excel shows formula but not result (2024)
Top Articles
Latest Posts
Article information

Author: Rueben Jacobs

Last Updated:

Views: 6130

Rating: 4.7 / 5 (57 voted)

Reviews: 88% of readers found this page helpful

Author information

Name: Rueben Jacobs

Birthday: 1999-03-14

Address: 951 Caterina Walk, Schambergerside, CA 67667-0896

Phone: +6881806848632

Job: Internal Education Planner

Hobby: Candle making, Cabaret, Poi, Gambling, Rock climbing, Wood carving, Computer programming

Introduction: My name is Rueben Jacobs, I am a cooperative, beautiful, kind, comfortable, glamorous, open, magnificent person who loves writing and wants to share my knowledge and understanding with you.