Have you ever found yourself in a situation where instead of showing the results of your calculations, Excel is displaying the formulas themselves?
Don’t worry; you’re not alone! This is a common issue that many Excel users face, and it’s usually a simple fix.
To resolve this, check if you have the Show Formulas feature enabled and disable it. Also, check if your formulas contain any syntax errors and fix them.
It can also mean that the cells containing the formula are formatted as text. To resolve this, you can change the cell’s format to General and refresh them.
In this article, we’ll discuss the reasons why Excel may display formulas instead of results, and we’ll show you 5 quick ways to fix this issue.
Let’s get started!
Reasons for Excel Showing Formula Instead of Result
First of all, you need to know the source of the problem.
Now, there are several reasons why Excel might display a formula instead of the expected result, let’s look into them.
The most common reasons Excel shows formulas but not the result include:
Show Formulas Option Enabled: Excel has a built-in feature that allows you to view all the formulas in your worksheet. This feature being enabled will lead to Excel showing formulas instead of their results. You can check and disable this option in the Excel settings.
Using Single Quotation Marks (‘): If you start a formula with a single quotation mark, Excel will interpret the entire formula as text. Check your formulas to ensure there are no single quotation marks at the beginning, as they are used to indicate text values.
Calculation Mode Set to Manual: Excel has different calculation modes, including automatic and manual. If your calculation mode is set to manual, Excel will not update formulas automatically, and you’ll see the formula instead of the result. You can change the calculation mode in the Excel settings.
Cells Formatted as Text: When cells containing formulas are formatted as text, Excel will display the formula instead of the result. This can happen if you manually change the cell format to text or if you import data that includes numbers formatted as text.
Error in the Formula: If a formula contains an error, such as a reference to a non-existent cell or a division by zero, Excel will display the formula as a way to alert you to the error. Check the formula for any mistakes and correct them.
By carefully checking these factors, you can quickly identify the cause and take the necessary steps to display the expected results in your Excel worksheet.
Now to the important part, let’s fix things up!
5 Quick Methods to Fix Excel Showing Formula Instead of Result
Now that you’ve learned about the reasons why Excel might display a formula instead of a result, let’s move on to the practical part of the article.
In this section, we’ll show you 5 quick methods to fix the issue at hand. We’ll also include step-by-step instructions to make the process as clear and straightforward as possible.
Here are the methods we’ll cover:
Disabling the Show Formulas Option
Enabling Automatic Calculation Mode
Fixing Errors in Formulas
Changing the Format of Cells
Removing Single Quotation Marks
Let’s get started with the first method!
1. Disabling the Show Formulas Option
Microsoft Excel has a built-in feature that allows you to view all the formulas in your worksheet. If this feature is enabled, Excel will display formulas instead of their results.
To disable the Show formulas mode and show the actual results, follow these steps:
Go to the Formulas tab on the Excel ribbon.
In the Formula Auditing group, click on the Show Formulas button. You can also use the Ctrl + ` keyboard shortcut.
This will toggle the option on or off. When the option is off, Excel will display the results of the formulas instead of the actual formulas.
By following these steps, you can disable the Show Formulas option in Excel and display the results of the formulas in your worksheet.
2. Enabling Automatic Calculation Mode
Excel has different calculation modes, including automatic and manual. If your calculation mode is set to manual, Excel will not update formulas automatically, and you’ll see the formula instead of the result.
To enable automatic calculation mode, follow these steps:
Go to the Formula tab on the Excel ribbon.
In the Calculation group, click on Calculation Options.
Select Automatic. This will enable the automatic calculation mode, and Excel will update the formulas and display their results.
By following these steps, you can enable the automatic calculation mode in Excel and display the results of the formulas in your worksheet.
3. Fixing Errors in Formulas
If there is an error in a formula, such as a reference to a non-existent cell or a division by zero error, Excel will display the formula as a way to alert you to the error.
To fix the error and display the result, follow these steps:
Select the cell containing the formula with the error.
Review the formula and look for any mistakes or references to non-existent cells.
Fix the error in the formula by correcting the references or adjusting the formula logic.
Press Enter to apply the changes. Excel will now calculate the result and display it in the cell.
Some common errors include:
Placing a Space Before the Equal Sign: When you’re writing an Excel function, make sure you avoid placing spaces before the equal sign or you’ll run into the error.
Missing Arguments: Check your function to see if all of its arguments are complete as incomplete arguments will lead to it being displayed as text or other errors.
Quotes Around Formulas: Check your formula to see if it is surrounded by double quotes.
Mismatched Parentheses: Make sure each you add a corresponding closing parenthesis for each one you open.
4. Changing the Format of Cells
When Excel displays a formula instead of a result, it could be due to the cells being formatted as text. To fix this issue, you can change the cell format to General or Number format.
To do this, follow the steps below:
Select the Cells: First, select the cells where you want to display the results instead of the formulas.
Open the Format Cells Dialog Box: Right-click on the selected cells and choose Format Cells from the context menu. Alternatively, you can press Ctrl + 1 to open the Format Cells dialog box.
Select the General Format: In the Format Cells dialog box, go to the Number tab, and then select General or the appropriate number format from the list. If you choose the General format, Excel will display the result without any specific number format. If you choose a specific number format, Excel will display the result with that format.
Click OK: After selecting the desired format, click the OK button to apply the changes.
For the change to take effect, you’ll need to reload the cell. To do this, press the F2 + Enter shortcut.
By following these steps, you can change the cell format to display the results of the formulas in your Excel worksheet.
5. Removing Single Quotation Marks
If an excel formula contains a single quotation mark at the beginning, Excel will interpret the entire formula as text.
To fix this issue, follow these steps:
Select the cell containing the formula with the single quotation mark.
Press F2 to enter the edit mode.
Delete the single quotation mark at the beginning of the formula.
Press Enter to apply the changes. Excel will now treat the formula as a formula and calculate the result.
By following these steps, you can remove a single quotation mark at the beginning of a formula and ensure that Excel treats it as a formula and calculates the result.
Final Wrap Up
Understanding why Excel displays a formula instead of a result is crucial for maintaining the accuracy and clarity of your spreadsheets.
Furthermore, by learning the common causes of this issue and applying the appropriate solutions, you can ensure that your formulas are functioning as intended.
So, the next time you encounter this issue, don’t panic – take a step back, identify the cause, and use the tools and techniques we’ve discussed to set things right!
If you’d like to step up your data analysis skills, check out our helpful video on Developing Data Models In The Real World!
Frequently Asked Questions
How can I convert formulas to values in Excel?
To convert formulas to values in Excel, follow these steps:
Select the cells with formulas you want to convert.
Copy the cells by pressing Ctrl + C.
Right-click on the selected cells, choose Paste Special, and select Values.
Click OK to apply the changes.
How do I get Excel to show results instead of formulas?
To get Excel to show results instead of formulas, you can use the following methods:
Change the cell format to General or Number.
Disable the Show Formulas feature.
Set the calculation mode to Automatic.
Remove the single quotation mark from the beginning of the formula.
Fix any errors in the formula, such as incorrect references or division by zero.
What is the shortcut to change formulas to values in Excel?
There is no direct shortcut to change formulas to values in Excel. However, you can use the following steps as a workaround:
Select the cells with the formulas you want to convert.
Copy the cells by pressing Ctrl + C.
Right-click on the selected cells, choose Paste Special, and select Values.
Click OK to apply the changes.
How can I display the formula in a cell in Excel?
To display the formula in a cell in Excel, you can use the following methods:
Press Ctrl + ` (the grave accent key, usually located above the Tab key).
Go to the Formulas tab, click on Show Formulas in the Formula Auditing group.
Double-click on the cell with the formula to enter the edit mode and display the formula in the formula bar.
