This tutorial will explain a few ways to fix formulas not showing the correct result in Excel.
Microsoft Excel may return certain errors when the user makes a mistake setting up their formula. This guide will also cover some possible settings that may affect how your formulas work.
Several settings in Microsoft Excel may be the cause of why your formula is not evaluating properly. For example, the Show Formulas mode will force all cells to show the formulas used rather than the final result.
The user may also have to fix an error in the formula itself. For example, the user may need to provide the right number of arguments or spell the formula correctly.
Now that we have an idea of some possible reasons why our Excel formulas are not returning the correct result, let’s take a look at some examples.
A Real Example of a Formula Not Showing Correct Result in Excel
The following section provides several examples of how to use this function. We will also go into detail about the formulas and tools used in these examples.
First, let’s take a look at a real example of a spreadsheet where the formulas are not showing the correct result.
In the example below, we have a list of users containing data such as their first name, last name, country, and email. We tried to use the
CONCATENATE function to combine both the first and last names into a single column.
We encounter an issue when we try to fill the whole column with the Fill Handle feature. The formula does not update because our Excel program is currently in Manual Calculation mode.
In the example below, our formulas are not evaluated because our Excel program is in Show Formulas mode.
These are two of many possible reasons why your Excel formula is not evaluating or showing the correct answer.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
If you must know how to fix your formula to show the correct result, head over to the next section to learn some of the most common fixes for this issue.
How to Fix Formula Not Showing Correct Result in Excel
This section will guide you through each step needed to fix formulas not showing the correct result. We’ll show you how the Manual Calculation and Show Formulas settings could affect your Excel formulas. We’ll also look into common errors you might miss when writing your formulas.
Follow these steps to start fixing your formula:
- If your formula does not update automatically, your Excel program may be set to Manual Calculation mode. To fix this setting, navigate to the Formula tab and click on Calculation Options.
- In the dropdown menu, select the Automatic option. Your worksheet should now update automatically whenever we add a new formula.
- Your formula may also be affected by the Show Formulas option. When your sheet is in this option, cells show the formulas rather than showing the actual result. You can toggle this option by clicking on the Show Formulas option in the Formulas tab.
- Your formula may return a ‘0’ if the formula contains a circular reference. An easy way to determine whether your formula contains a circular reference is by checking the Excel status bar.
- To fix this issue, adjust the formula to remove the circular reference. You may also edit Excel’s calculation options to enable iterative calculations. You can follow our guide on circular references for a detailed explanation.
- You may also check if the formula is missing an equal sign, ‘=’ at the start. Without the equal sign, Excel will treat the entire formula as text rather than an actual function.
- You can also check to see if all the functions used in your formula have the correct spelling. If the user inputs the wrong spelling, Microsoft Excel will return a #NAME? error instead.
These are some of the most common methods you can use when your Excel formula is not returning the correct result.
Frequently Asked Questions (FAQ)
- Why does Excel treat my formula like text?
Your Excel program might consider your formula as text if there is a space before the equal sign. You may also check if your cell formatting is set to Text. If this is the case, you can fix this issue by setting your formula to General.
This step-by-step guide should provide you with all the information you need to fix formulas that are not showing the correct results. We’ve given you several methods that may help you fix this problem in Microsoft Excel on your own.
Formulas not showing the correct result is just one example of a possible issue you may encounter when handling your spreadsheets. Our website offers various tutorials to help you get more out of Microsoft Excel.
For example, you can read our detailed guide on how to fix your sheet when you are unable to move cells. With so many other Excel functions available, you can find one that is appropriate for your use case.
Want to learn more about Excel? Subscribe to our newsletter for helpful guides and tutorials!