Select cell, equal sign, type formula, enter. Now it’s one thing to write a formula that triggers an error, but to see the formula do nothing?
How do you fix something that isn’t doing anything?
It might not look so wrong but there is definitely something wrong when a formula doesn’t return a result and just… sits there.
Problem
The problem we are looking at here is a formula being entered but excel shows the formula itself instead of returning a result like so:
As you can see, there is nothing wrong with the formula (if there was, an error would show up, not the formula itself).
This happens when Excel doesn’t process the formula as a formula. Excel doesn’t evaluate it and displays the formula itself. Luckily, there aren’t too many things that could have gone wrong here so there isn’t a need to panic or delete or overwrite cells. There are a few reasons the formula shows up in place of the result and there’s an easy fix for each. Let’s dive into them.
Recommended Reading: How to Lock and Hide Formulas in Excel
Potential Issue 1 – 'Show Formulas' Option is Enabled
The Show Formulas feature displays all the formulas used on the sheet instead of the results of the formulas. Your sheet, which would normally look something like this:
Will look like this with Show Formulas enabled.
Widened columns with all your formulas skinned to bare the bones.
This is a favorable option for the 1-step ease of seeing all the formulas on the worksheet so you don’t have to individually select each cell and check the formula. As for the problem we are looking at right now, this option can cause confusion especially if it’s not what you need.
If it’s not what you need and you didn’t enable it yourself then how come it’s causing trouble? It may have accidentally been enabled as the keyboard shortcut for Show Formulas is Ctrl + `. It’s quite an easy mistake to make.
If you doubt that this is what has happened, you can check Show Formulas in the Formulas tab.
Indeed Show Formulas is enabled.
Bear in mind that this is a full worksheet feature. If all the formulas on the sheet are being displayed and that is the problem in question, the Show Formulas option needs to be addressed. If the problem is limited to one formula, Show Formulas is not your problem.
What’s the fix?
The fix is as easy as the problem itself. You can either press Ctrl + `. This is a toggle shortcut for Show Formulas and will also switch the option off.
The other way is to hit the Show Formulas button in the Formulas tab.
Both methods will disable Show Formulas.
Potential Issue 2 – Cell is Formatted as 'Text'
The problem is the same but the reason is different. The formula is entered but the formula is displayed instead of the result. A cell in an Excel worksheet can have many formats and the default format of all cells in a new sheet is General (which would not cause this problem). Hence, a possible reason could be that the cell in which the formula is entered is formatted as Text.
What’s the fix?
The fix to this problem is to change the formatting to General. Here are the steps to do this:
- In the Home tab, in the Number section, there is a drop-down menu for cell formatting. Click the arrow to access the drop-down menu. Alternatively, right-click the cell to open the right-click context menu and select Format Cells option from the menu.
- Select General.
- Click on the formula bar (or double-click the cell) and press the Enter key.
For this case, cell formatting will be prospective. The reason for editing the formula is that formatting the cell to General will not have any impact on the cell’s existing contents.
Similarly, if you already had the result of your formula and later the cell is formatted to Text, it will not change to the formula in text. This implies that the Text format must have been applied before the formula was entered, making the formula appear as text.
Potential Issue 3 – Excel Thinks the Formula is a Text
How strange of Excel to think so but there are some very valid reasons for this. Let's see some of them below:
a. Apostrophe Before the Formula
You might be aware of the fact the whenever you add an apostrophe at the start in any cell, Excel considers the cell content as text. This is applicable to any data type like numbers, date-times, and even to formulas.
The apostrophe is proving to be quite a bug for us today.
While this treatment may be useful in some cases, it's a hindrance when you're trying to get the formula to work.
b. Equal Sign Missing or Space Before the Equal Sign
Excel recognizes and evaluates a formula when it has an equal sign in the beginning. Therefore, the lack of it will not persuade Excel to calculate it and the contents will be treated as text to be displayed as they are.
A space character before the equal sign will have a similar effect; since the first character of the formula will not be an equal sign, Excel will not evaluate it as a formula.
c. Formula Wrapped in Quotes
Many times while writing formulas online (on blogs or forums) people wrap them inside quotes to make them stand out. And when you try to use the same formula along with the quotes they do not work as expected.
The reason behind this is that anything that is wrapped within quotes is treated as a text by Excel and hence it does not evaluate such formulas.
Note: Please note that you are allowed to use quotes within the formulas, but using the quotes to wrap the entire formula makes excel think it is a text string and hence will not be evaluated.
What’s the fix?
In all the above cases there is a simple fix – just edit the formula and make sure it starts with an equals sign and not with apostrophes, quotes, or unnecessary spaces.
That’s all folks! Next time you’re faced with the predicament of your formula staring at you in the face, you’ll know how to work things around it. Signing off!
About Mehwish Javaid