Sometimes, while working in Excel we feel that the software has started behaving in a weird way. One such case could be when you open up a worksheet (working fine previously) and discover that some formulas in the sheet are not showing the desired results. This could be very annoying since the last time you opened the sheet it was working fine.
There could be two possible cases when Excel formulas stop showing the results but the cells show the formulas instead.
- Formula containing cells are formatted as Text
- Excel’s Show Formula option is turned ON
In this tutorial we’ll discuss both cases one by one and then present the solutions to fix these issues.
Example 1 Cells Formatted as Text
Let’s take a look at the dataset given in the example above. We can see that some formulas are not working fine and showing the formula instead of the desired result. This could happen when the formula containing cells is mistakenly formatted as Text.
Step 1 – Select the problematic cells and observe the format
- To solve the problem, first we have to diagnose it properly. For this purpose, select all the cells which are not showing the results.
- If the cells are adjacent then just select the first cell and expand the selection until you select all cells.
- If the cells are non-adjacent then hold down the CTRL key and select all the cells.
- Now use the shortcut key CTRL+1. This will open up the format cells dialog box and will also show you the current format of the selected cells as well.
Step 2 – Change the format of problematic cells to General
- If the format of the cells is set to Text, then change it back to General.
- Now while all the cells are still selected, keep pressing F2 and then Enter keys until all selected cells show the desired number results. This will resolve the issue.
Example 2 Show Formulas Option is turned ON
Let’s take a look at the dataset given in the example above. We can see that all formulas are not working fine and showing the formulas instead of the desired result. Furthermore, all data formatting has been turned OFF as well. This could happen when the option of Show Formulas is turned ON accidentally. It can also be done purposefully by someone to see which formulas are being implemented in the sheet and then they forget to turn the option OFF.
Step 1 – Turn OFF the Show Formulas Option
- To turn OFF the Show Formulas option, go to the Formulas tab on the main menu.
- Locate the Formula Auditing group.
- If you see the option of Show Formulas in gray color and pressed down. Click on it to turn this option OFF.
- This will resolve the issue immediately.
- Alternatively, you can also use the shortcut key CTRL + ` to achieve the same goal.