When you’re in the flow of Excel, it’s frustrating when it’s doing something you don’t expect. It often seems completely random when Excel chooses to shaft your flow.
One such problem I regularly encounter is when Excel randomly displays my formulas as text, rather than calculating them and showing the result. Clicking into the cell and pressing enter doesn’t work, and searching help online never seems to answer the problem.
The likely culprit is cell formatting as this blog explains. I also cover some other potential causes. I include my quick recent video below and I hope this fixes your problem. If it has been helpful, please consider buying me a coffee or buying yourself a cool t-shirt to support my work.
Why is Excel Showing Formulas as Text?
Whether you’re using complex functions to categorise things, or simple formulas to calculate totals, they sometimes show as text. Wherever you look for a fix, nothing seems to work. Here’s what the problem looks like below:
The answers you’ll tend to find online either miss the point or are infuriating. Proposed “solutions” seem to assume ‘user error’ and ask the following questions of frustrated users in different ways:
- Did you miss out any brackets / parentheses?
- Are your numbers typed in quote marks?
- Did you make a typo?
- Have you gone out of your way to switch on the ‘Show Formulas’ button?
- Have you switched off Excel’s auto calculation of cells?
- Were you dropped on your head as a child?
So why is Excel showing these formulas as text? The most likely answer is your cell formatting in the ribbon (screenshot below): Check it’s not set to ‘Text’. If so, Excel ignores the contents and just displays literally what is typed in there. You’ll want to change this to ‘General’ or ‘Number’ for your selection of cells.
Once you’ve corrected the formatting for the desired selection, any new formulas will function properly. For existing ones, you’ll need to either re-enter them, or re-apply them by double-clicking in the cell (shortcut = F2) and pressing Enter.
I don’t know how this happens for Excel to show the formulas not the result. It may be you got sent a file from a colleague that had dodgy formatting and then you tried building on it. Or raw data exports from systems tend to default to text. Either way, a good way of avoiding this is to start your spreadsheet or dashboard from scratch each time.
4 Other Reasons Excel Formulas Appear as Text…
The above is the most common reason for functions displaying as text in Excel. However, the following are four more possible faults which cause the same situation to happen. These might aid your troubleshooting, just in case it’s not the text formatting issue…
1. Protected Worksheets
Check for protected worksheets. If the worksheet is protected, Excel prevents formulas from updating or recalculating. You can fix this by unprotecting the sheet using the Review tab of the Ribbon and then select Unprotect Sheet (you’ll need the password if one is set). Then try re-entering the formula.
2. Check ‘Show Formulas’
Check in the Formulas tab of the ribbon that the ‘Show Formulas‘ feature isn’t switched on. This presents all formulas as text, allowing you to review them. While this is useful for auditing and debugging your formulas, it can cause confusion. So if this is on, switch it off!
3. Automatic Calculations
Also under the Formulas tab of the ribbon are ‘Calculation Options’. These can be set to Automatic, Partial, or Manual. Ensuring this is set to Automatic prompts Excel to calculate everything.
4. Formula Construction
There might be something unusual going on with the formula itself. If you haven’t checked this, do so now. Look for unusual characters around the formula or errors. Spaces, quotations, or other characters before the equals sign can cause havoc.
Make sure for example your formula begins with an “=” sign. Check in the formula bar there’s no apostrophe ( ‘ ) before your equals sign. An apostrophe tells Excel to treat anything in that cell as simple text and so won’t recognise it as a formula, even if it looks like one to you.
I hope you’ve found this helpful. If so, you can contribute to my work by buying me a coffee or grabbing yourself an original, funny t-shirt.
All the best, Adrian
I hope this guidance was useful! Subscribe to my blog and EATO YouTube channel for more interesting stuff to help you ‘excel at the office’. Liking the content? Please like and share others who may also find it useful. If so inclined and able, buying me a coffee helps keep free content flowing and I greatly appreciate it. Want fantastic, time-saving templates or dashboards tailored to you and your business? Then please get in touch for a free quote.
