Top 7 Ways to Fix Formulas Not Working in Microsoft Excel for Windows

Excel formulas are valuable for various calculations and data analysis, from simple arithmetic operations to complex financial models. At times, you might find it confusing when a formula fails to work or calculate as expected in Excel. If you face the same, this guide has some tips that will help.

Top Ways to Fix Formulas Not Working in Microsoft Excel for Windows

Figuring out why your Excel formulas are not calculating can be a challenge, especially when you are working with a large amount of data. This guide contains useful tips that will help you navigate through the maze of formula troubles and get them working again in Excel. So, let’s take a look.

1. Double-Check the Formula

Your first course of action is to review your formula for syntax errors. If you make any mistakes while entering the formula, Excel will treat the entry as text and won’t display results. This commonly occurs when working with lengthy formulas.

Carefully review and correct your formula in Excel, and check if that solves the problem.

2. Enable Automatic Calculation

By default, Microsoft Excel automatically updates formula results when change to the dependent values. However, that might not happen if you set the calculation option to manual. Here’s what you can do to change that.

Step 1: Open your Excel workbook and switch to the Formulas tab.

Formulas Tab in Excel

Step 2: Click on Calculation Options and select Automatic.

Calculation Options in Excel

Once you make the above changes, your formula results should update automatically.

3. Force Excel to Recalculate

Excel may sometimes fail to display or update formula results due to temporary glitches. To fix this, you can force Excel to recalculate your cells. You can press Shift + F9 keyboard shortcut to force Excel to calculate your current sheet. If you want Excel to calculate the entire workbook, press F9 instead.

4. Check if the Formula Is Formatted as Text

Are your Excel formulas appearing as plain text? This can happen if you inadvertently format the cells containing the formulas as text. Here’s how you can change that.

Step 1: Open your Excel workbook and select the cell that displays your formula as text.

Select Cell in Excel

Step 2: In the Home tab, click the drop-down menu in the Number section and select General.

Format Cell in Excel

Step 3: Double-click your cell and press Enter to apply the formula again.

Apply Formula to Cell

5. Disable the Show Formulas Button

The Show Formulas option in Excel is useful when you want to review formulas in your spreadsheet. However, if you leave this option turned on, Excel will display the formulas and not their results. To disable it, head to the Formulas tab in your Excel workbook. Then, click the Show Formulas button in the Formula Auditing section.

Disable Show Formula Option in Excel

6. Trace and Edit Dependent Cells

Your formulas may not work correctly in Excel if the dependent cells contain unexpected values or are formatted as text. To fix this, you need to review and edit the cells that the formula references. Here’s how you can go about it.

Step 1: Open your Excel workbook and select the cell containing the formula that isn’t calculating correctly.

Select a Cell in Excel

Step 2: Under the Formulas tab, click the Trace Dependents button. Excel will then display an arrow pointing toward the dependent cells.

Trace Dependents in Excel

Step 3: Double-click the dependent cells to edit them. Then, click the Remove Arrows button at the top.

Remove Arrows in Excel

7. Check for Circular References

Another reason why your formulas may stop working in Excel is due to circular references. This happens when a formula indirectly refers to itself. Here’s how you can identify circular references in your Excel spreadsheet and fix them.

Step 1: Launch Excel on your PC and open your workbook.

Step 2: Under the Formulas tab, click the Error Checking button and select Circular References. In the submenu, Excel will display all the cells containing the circular reference formula.

Check Circular References in Excel

Step 3: Select the problematic cell from the submenu to locate it on the sheet. Then, double-click it to edit it.

Edit a Cell in Excel

From Chaos to Clarity

Often, small and seemingly insignificant errors, such as typos or incorrect formatting, cause your formulas to stop working in Microsoft Excel. One of the above tips should help fix the problem and get your Excel formulas to calculate again. Apply them all, and let us know which works for you in the comments below.

Last updated on 26 June, 2023

The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.