Have you ever been working on an Excel spreadsheet, only to find that the formula you entered isn’t showing the result you expected?
Instead of giving you the calculated value, sometimes Excel shows the formula instead of the value/result.
Below is an example where I entered the formula in the Profit column, and I see the formula instead of the value.
This is something that’s quite common and happens to a lot of users.
There could be multiple reasons behind Excel showing the formula instead of the result, and in this tutorial, I will show you the potential fixes that will make this issue disappear.
So let’s get started!
Reason #1 – Show Formulas May Be Enabled (Top Reason for Formulas Showing as Text)
In some cases, people actually want to see all formulas as text in the worksheet.
There could be multiple reasons for this – the most common one is when you’re auditing the worksheet and need to know where and what formulas are being used.
This problem has an easy fix – you just need to disable the ‘Show Formulas’ option (which might be enabled and is causing this issue)
Below are the steps to do this get the formulas to show the result:
- Click the Formulas tab in the ribbon
- In the ‘Formula Auditing group, click on the ‘Show Formulas’ option.
In most cases, this should fix the problem, and your formulas should show the result and not show up as text itself.
The keyboard shortcut to enable/disable showing formulas as text options is Control + ` (hold the Control key and then press the tilde key (it’s below the Escape key)
And what if this doesn’t solve the problem, and you’re still seeing formulas as text?
Keep reading and try the other fixes!
Also read: Remove Formulas in Excel but Keep the Data
Reason #2 – Cells are Formatted as Text
Another issue that you may face is that when you insert a formula, it shows the formulas and not the value.
One possible culprit could be the cell being formatted as Text. When a cell is formatted a Text, entering the formula will keep showing you the formula and not show the result of that formula.
And it has a really easy fix – change the formatting of the cells.
Here are the steps to do this:
- Select the cells where you have the formulas or where you plan to enter the formulas.
- Click the Home tab.
- In the Number group, click on the Formatting drop-down and select ‘General’ (you might see Text being the option that is already selected).
Once you have changed the format of the cells, any formula you enter now will show the result of that formula.
However, what about the formulas that are already in these cells and are not showing the result? For them, go to the cell, press the F2 key or double-click on the cell to get into the editing mode, and then hit the Enter key.
Also read: How To Hide Formulas In Excel Without Protecting the Sheet?
Reason #3 – There is Apostrophe Before the Formula
Another issue (not as common) you may face is having an apostrophe before the formula.
When you add an apostrophe at the beginning of any cell, Excel considers the cell content as a text string.
So if you start with an apostrophe and then enter a formula, Excel will not calculate that formula and show you the result. Instead, it would show you the formula itself
This also sometimes happens when you get a data dump from a database, where an apostrophe is added automatically at the beginning.
To fix this, you can go to the cell and manually remove the apostrophe.
Also read: How to Auto Format Formulas in Excel
Reason #4 – The Equal-To Sign is MIssing
Another common issue where people see the formula instead of the result is when the equal-to sign (=) at the beginning of the formula is missing.
Every formula needs to start with the equal-to sign (which is how Excel knows that whatever follows the equal-to sign needs to be calculated).
And the fix is…. you guessed it!
Add that missing equal-to sign before the formula.
Also read: How to Clear Contents in Excel without Deleting Formulas
Reason #5 – The Formula is Wrapped in Quotes
Another possible reason (although less common) is when your formulas are wrapped in single or double quotes.
Something like:
"=SUM(B1:B10)"
or
'=SUM(B1:B10)'
This can sometimes happen when you copy data from some website or when getting a download from a database.
As soon as you remove these extra quotes, the formulas should work fine.
Also read: Circular References in Excel - How to Find and Fix it!
FORMULATEXT – Right Way to Show Formulas as Text
In some scenarios, you want to show the formula alongside the calculated value.
The right way of doing it would be to use the FORMULATEXT formula, which takes the cell reference of the cell that has the formula and shows the formula in that cell.
Below is an example where I have the formula result in column D and then the formula used in the cells in column D is shown in column E using the FORMULATEXT function:
So these are five possible reasons that may make Excel show formulas instead of the result, and these all can easily be sorted using the methods converted in this article.
Hope you found this tutorial useful!
You may also like the following Excel tutorials:
