Technology

Easy and Effective Ways to Fix Excel Formula Not Working

Are you struggling with Excel formula errors? Do you find yourself unsure of the cause and how to fix it? Don't worry, in today's article, we will address all your concerns. Stay tuned to find out!

Administrator

26 December, 2023

154

Causes of Excel formula not working error

The Excel formula not working error is a common issue when using Excel. Even if we enter the formula correctly, sometimes we don’t see the result when pressing Enter, only the formula content.

Or if we finish a calculation formula in one cell and drag the formula to apply to other data cells, the calculation won’t happen automatically. However, in this case, do not re-enter the formula into each cell as it is time-consuming and labor-intensive.

There are many reasons why Excel doesn’t skip formulas, such as:

Your Excel is currently in formula display mode.
Error when entering formulas into cells formatted as text (this is the most common error).
Excel is in manual calculation mode.
Your calculation formula falls into a circular reference mode.

Next, let’s learn how to fix this error!

How to fix Excel formula not working error

Change the format of the formula entry cell

This is a common situation for Excel users. In this case, the cell where you enter the formula is still in text format, so there is an error. With just 4 simple steps, you can fix this error. Specifically:

Step 1: If only one cell is affected, select the cell that contains the formula in text format. If you have multiple cells that need to be fixed, select all the cells that need to be fixed. Then click the “Home” tab.

Step 2: Next, go to the “Number” section and then click on “Text” as shown in the image below.

Step 3: In the dropdown list, select the “General” format.

Step 4: Now, double-click the cell that has been modified in format, then press “Enter” to successfully fix the error!

Reset the calculation format

In this method, the issue is that you set the calculation mode to manual. This makes Excel display only formulas and results for one cell. If you want to calculate in another cell, you will need to re-enter the formula as before. To fix this error, you can follow the steps below.

Step 1: First, open the Excel file, then click the “File” tab.

Step 2: If your computer doesn’t have the “Options” section available outside, click “More” and then click “Options”. Otherwise, if your computer has the “Options” section available outside, select it directly!

Step 3: Then, in the dialog box that appears, select “Formulas”.

Step 4: Continue to go to the “Calculation Options” section, uncheck the “Manual” option and then check the “Automatic” option and click “OK” to set the calculation format when dragging formulas to other cells.

Activate the formula display feature in Excel

When you encounter problems with Excel only displaying formulas, you can use the method I share below to fix this issue. Here are the steps to activate the formula display feature in Excel.

Step 1: First, open the Excel file, then click the “File” tab.

Step 2: If your computer doesn’t have the “Options” section available outside, click “More”, then click “Options”. Otherwise, if your computer has the “Options” section available outside, select it directly!

Step 3: Then, in the dialog box that appears, select the “Advanced” section.

Step 4: In the “Display options for this worksheet” section, uncheck the “Show formulas in cells instead of their calculated results” option, then click “OK” to turn off formulas in Excel. In addition, you can also press “Ctrl + `” to hide formula display and fix the Excel formula display error.

Fix the loop error

By using this method, the issue you are encountering may be caused by a cell that contains your result having a loop error, also known as a circular reference error. This is quite an annoying error and the result only shows as the number 0. To fix this error, you can follow the steps below to fix the loop error.

Step 1: If there is a loop error, identify the error cell by selecting the “Formulas” tab.

Step 2: Then go to the “Formula Auditing” section and click on “Error checking”.

Step 3: Next, from the dropdown list, select “Circular References”. Excel will display the cell with the loop error (as shown in the image, in this case, it is cell F3), then click on the displayed cell and you will be directed to the error cell.

Step 4: After knowing the position, we proceed to delete, move the formula, edit the reference in the formula, and check if the error has been fixed or not.

Conclusion

Above are some methods that can help you fix the Excel formula not working error easily and effectively. Hopefully, this article is truly helpful to you. If you have any questions about this, please leave a comment below. Thank you for reading this article and see you in the next ones!