MS Excel is one of the prominent applications used for calculations and for maintaining information. However, like all other computer programs, it is also prone to glitches. One such commonly-generated error is - the Excel formula not showing results.
This might get triggered due to several reasons such as if the Show Formula feature is turned on, you have mentioned formula in the wrong format, unnecessary formatting, etc.
To save your time and effort, we have put together some effective solutions to troubleshoot the error in no time.
Part 1: Symptoms and Solutions to Excel Formula Not Showing Result Problem
Facing "Excel formula not showing result" error and wondering about how to resolve it? Here, we have compiled different symptoms of the error, along with the practical solutions to deal with them.
Symptom 1: Excel displays formula rather than the result
Sometimes, a bug in Excel results makes the application unable to display the text of a formula. Execute the solutions mentioned below to fix the error.
Solution 1: Turn off the Show Formulas button
People generally turn on the "Show Formula" button, thinking that it will help them in the calculation. But that’s not true as applied formulas won’t work if the Show Formula button is turned on. This button is mainly designed for auditing formulas so, it displays the formula instead of the result when pressed. Turn it off and check if it resolves the error.
-
Go to the Formula tab and click on Formula Auditing Group.
-
Tap the Show Formulas button and turn it off.
Solution 2: Insert equals to back in the formula
-
Select the cell and right-click it to choose Format Cells. Select "General"
-
Delete the "=" at the beginning of the formula. Press Enter.
-
Insert "=" back in the formula and you are done.
Symptom 2: Excel thinks your formula is text
It might be possible that you have mistakenly formatted the cells containing the formulas as text. In such a case, Excel skips the applied formula when set to the text format and reflects the plain result instead. Below are mentioned two methods to resolve this issue.
Solution 1: Check the formatting
-
Click on the cells and go to the "Number group" in the Home tab.
-
If it displays "text", tap on it and select "General."
-
Double-click on the cell to recalculate the formula.
-
Press the Enter key and you are done.
Solution 2: Force Excel to Re-Consider The Formula
-
Press Ctrl+Alt+F9 keyboard combination and press Enter to force excel to recalculate the formula.
-
Alternatively, you can recalculate an active sheet by pressing Shift + F9 on your keyboard.
Symptom 3: Excel No equal sign/space before the equal sign
If you are extracting data from ERP systems in comma-separated values (CSV) format, your data is likely exposed to plenty of unwanted spaces. These spaces and equal signs are difficult to identify, leading to the Excel formula not showing result error.
Solution 1: Remove "space" at the beginning of the formula
Excel won’t perform calculations if you have mistakenly entered an additional apostrophe, equals to or space at the beginning of the formula. The easiest solution to fix the trouble is just head over to the cell, select it, and remove the space or apostrophe.
Solution 2: Use Trim Formula
In general, the Trim Formula finds out more than one space between words and deletes it. The formula is =TRIM(text). Here, the text is the requirement arguments from which you wish to remove space.
Symptom 4: Formula wrapped in quotes
The users often use double quotes while writing formulas but it’s essential to understand when to use them and when not. Incorrect placement of double quotes leads to Excel formula not showing result error. Use the solutions mentioned below to resolve the trouble.
Solution 1: Place the quotes appropriately
Double quotes are applied according to the argument. For example, if you want to join two numbers, use the formula 1234&5678 = 12345678. However, you cannot use the same logic to join two text strings. Instead, you need to specify them within double quotes like "John" & "Smith".
Solution 2: Remove quote marks around text with Find and Replace function
-
Select the range with quote marks you want to remove.
-
Click Find & Select > Find in the Home tab to open the Find and Replace dialog box.
-
In Find and Replace dialog box, tap on the Replace tab enter a quote mark into the "Find What" box and keep the "Replace with" box blank. Click the "Replace All" button and see if it helps.
Symptom 5: Cell linked to text-formatted cell shows formula not value
Sometimes, when you enter the formula in Excel that links one cell to another that is formatted with the Text Number format, the cell that contains the link is also formatted as text. In this case, when editing the formula in the linked cell, the application displays the formula rather than the value.
Solution 1: Change the format of the cell
-
Select the cell with the formula and click Cells on the Format Menu.
-
Click the Number tab and select the format code that you want the cell to display. Click Ok.
-
Choose the cell with the formula and press the F2 and Enter key.
-
In the same cell, click Cells on the Format menu. The cell format category will be Text.
-
Specify the category and format options for the cells and then click Ok.
Symptom 6: Excel shortcut to show formulas
Formula sheets are displayed in worksheet cells while formulas are shown in the formula bar only. Check the solutions if Excel shortcuts show formulas instead of results.
Solution 1: Show formula in all cells
-
Go to the active Excel sheet and Press Ctrl+ `. Print your worksheet as you normally would when the formulas get visible.
-
Press Ctrl + ` again to switch back to showing formula results.
Solution 2: Showing formula of just one cell
If you are looking to show the formula of just one cell, type a single apostrophe before the formula in the cell to get it done. For instance, ‘=SUM(1+2+3).
Symptom 7: Fix Excel Formulas that are Not Calculating or Updating
The users have to enable automatic recalculation so that Excel recalculates the spreadsheet’s formulas whenever you change a cell value. If you prefer keyboard shortcuts, you can recalculate by pressing the F9 key.
Solution 1: Check for Automatic Recalculation
Follow the steps to enable the automatic calculation.
-
Go to the Formulas Ribbon and move your cursor to the far right.
-
Click on the "Calculations" tab and check the "Automatic" option on the drop-down list.
Solution 2: Check the cell format for text
Execute the following instructions to check the cell format for the text.
-
Go to the Home ribbon and check the number format.
-
If the format shows Text, change it to Number. When a cell is formatted as Text, Excel makes no attempt to interpret the contents as a formula.
-
Re-confirm the formula by clicking on the Formula Bar. Press the Enter key.
A Video Tutorial on how to Fix Common Excel Formula Errors
Part 2: An Ultimate Solution to Fix All Excel Related Problems within Clicks
Have you applied all the methods listed above but the Excel formula not showing result error still persists? It indicates that the Excel file gets corrupted and you need to choose a third-party tool to repair the damaged file. Consider using Wondershare Repairit and get rid of this trouble quickly.
If you only have a few Excel files to repair, I'd like to suggest you use Online File Repair, because it has a free trial, and it's more convenient to use. However, if the file amount is larger than 300 MB, then the desktop version is needed.
Here are some comparisons between the 2 modes:
|
Accessibility: Repairit Online is accessible from any device with an internet connection, allowing users to access them from anywhere at any time. |
Offline Access: Repairit for Desktop can be installed directly on your computer, allowing them to work without an internet connection, which can be beneficial in areas with limited connectivity. |
|
Online Version Offered: If you just have several Excles files smaller than 300 MB to repair, try the online version. And the free version supports repairing a file that is smaller than 5MB. |
More You Can Repair: The desktop version can fix more Excel files larger than 300 MB, and it has no limit to the file size and number. |
|
Automatic Updates: Repairit Online is regularly updated by the provider, ensuring users have access to the latest features and bug fixes without the need for manual installations. |
Data Control: With desktop products, users have more control over their data since it is stored locally on their computer, reducing concerns about data privacy and security. |
For Online PPT Repair access, please enter the official website. Here I will show you how to use the desktop version in detail.
Features
-
Repair damaged Excel files with all levels of corruption, including blank files, files not opening, unrecognizable format, unreadable content, files layout changed, etc.
-
Perfectly repair corrupted files with a very high success rate, without modifying the original file.
-
Quickly and safely repair multiple .xlsx files in a single process.
-
Support Windows 11/10/8/7/Vista, Windows Server 2003/2008/2012/2016/2019/2022, and macOS 10.10~macOS 14.
Step 1: Add the corrupted file
-
Launch the application and go to the More Typea Repair > File Repair section on the left side.
-
Click on the Add button and browse the location where corrupted files are saved. You can select multiple documents and load them on Repairit.
-
The application will let you know if try to upload any incompatible format.
Step 2: Start the repairing process
-
Once the file is loaded, click on the Repair button at the bottom to initiate the process. The tool will scan all the documents and fix all kinds of issues.
-
You can check the progress of the repairing process on the screen and can even cancel it during the ongoing process.
-
As soon as the process completes, the tool will notify you by displaying the relevant prompt.
Step 3: Preview and Save
-
Click on the "Preview" button located adjacent to the repaired files. You can check the results before saving any document.
-
Tap on the Save button if you are satisfied with the results. You can also click the "Save All" button to export all repaired files to the desired location at once.
Final Words
Excel formula not showing the result is the common error message frequently displayed by MS Excel but there is no need to freak out it can be easily resolved by using the solutions listed above. The guide has outlined all the possible symptoms triggered by this error and different methods to resolve them.
Remember that if none of these solutions help resolve the issue, it might be possible that the excel file gets corrupted which might be preventing the formulas from displaying results. In such a case, try Wondershare Repairit and restore the original version of the sheet in three easy steps.
