Excel Formulas Show in Cell

imageLast year, I showed that you could combine text in Excel by using the ampersand (&) operator, instead of the CONCATENATE function. That makes it much easier for those of us who are lazy, or can’t remember how to spell concatenate.

There was a combine text video too, and I’ve it added to the bottom of this post, so you can see it again. It’s more interesting than most of the new programs that are starting on television this season!

Formula is Showing

After watching that video on YouTube, someone asked why his worksheet was showing those combine text formulas, instead of the results.

cellformulashow01

There are two likely causes for this, described below. And the good news is that it’s easy to fix both!

Show Formulas

Excel has a feature that lets you switch between seeing the formulas, and formula results, by using the keyboard shortcut:  Ctrl +’

That character is the grave accent, and it’s at the top left on my keyboard, just above the Tab key .

Or, use the Ribbon command to make the switch – on the Formula tab, click Show Formulas

cellformulashow02

Cell Formatting

If the problem isn’t fixed by clicking the Show Formulas button, then the cell formatting is causing the problem.

To fix the formatting:

  1. Select the cells where formulas are showing
  2. On the Excel Ribbon, click the Home tab
  3. In the Number group, change the cell format from Text, to General

cellformulashow03

With the cells still selected,

  1. Click the Home tab on the Ribbon
  2. Click Find & Select, and click Replace
  3. In the Find What box, type an equal sign: =
  4. In the Replace With box, type an equal sign: =
  5. Click Replace All, then click OK, and close the dialog box.

Combine Text Without Concatenate Video

Or watch it on YouTube: Combine Cell Data in Excel Without Concatenate

_________________

You may also like...

9 Responses

  1. JP says:

    The key combination is actually Ctrl+’ (gravé accent). You would need to hold the Shift key for Ctrl+~ and that doesn’t do anything in Excel.

  2. Thanks JP! It’s fixed now.

  3. Lee Townsend says:

    There are occasions that I am so focussed on the formula, I forget the equal sign. The formula shows up as text.

  4. Thanks Lee! That’s happened to me a few times too. Usually I notice the problem when I try to link to another cell, and that doesn’t work without the equal sign at the start.

  5. Bob Ryan says:

    Debra – On my computer, after changing the formatting to General, I also have to edit the cell (double-click the cell, click the formula bar, or press F2) in order for the formula to work. I’ve tested this a number of ways, but if you find I’ve still missed something please feel free to not post this comment.

  6. Bob, you’re right, thanks! If it’s just a couple of cells, you can double-click the cell, then press Enter, to calculate it.
    If there are more than a few cells, select them, and use the Replace command to replace all the equal signs:
    Replace: =
    With: =
    Click Replace All

  7. Clarity says:

    Just as a bit of an aside, if you ever want to see both the formula (eg: =5+2) and the result (eg: 7) side by side this can be acheived by:

    Selecting range A1
    Open a new window (View > New Window)
    Arrange the windows (View > Arrange All > Tiled)
    Click a cell in the lefthand window
    Ctrl +’

    You should now have the formulae showing in the lefthand window and the results in the righthand window.

    This is a simple way of auditing the formulae in your Excel spreadsheets.

  8. Thanks for the reminder about showing formulas in a second window. There’s a blog post and short video here:
    Troubleshoot Excel With Formula View

  9. Bianca B B says:

    Debra,

    I have run into this problem so many times. I struggled by changing the cell format from text to general, then also to number without any success. Finally, today, I came across your posting and this did the trick:

    “Excel has a feature that lets you switch between seeing the formulas, and formula results, by using the keyboard shortcut: Ctrl +’
    That ‘ character is the grave accent, and it’s at the top left on my keyboard, just above the Tab key .
    Or, use the Ribbon command to make the switch – on the Formula tab, click Show Formulas”

    You are a genius!!!!!

    I can’t believe that after all this wasn’t really an issue, but rather a feature (Show Formula) that was perhaps “clicked” by mistake, probably by inadvertently using/typing the keyboard shortcut Ctrl + ‘

    Thank you so much! You made my day!

    Sincerely,

    Bianca

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *