The Comprehensive Guide to Fix Formula in Excel Not Working Issue

Using Excel with no formulas is almost unimaginable. So, nothing compares to the annoyance of discovering that a formula in Excel is not working. When that happens, one of the questions that will flash across your mind is, what is causing the formula not to work? Well, there's likely nothing wrong with your Excel and there's no need to get worried.

This guide will tell you the possible causes of this inconveniencing issue. But most importantly, you'll discover the solution for the problem. Let's jump right into it.

Part 1. Excel Formula Does Not Work: Overview

The problem of the Excel formula not working can present itself in three different ways:

• Not working - Formula returns a wrong result or error
• Unable to calculate - A cell displays a formula instead of the result
• Not updating - A formula shows an old value after the value of the dependent cells has been updated.

There are many causes of these problems, some of which include:

• Cells are formatted as text
• You've entered different values, and the result is different
• There's a space before the Excel Formula
• You've enclosed numbers in double quotes

You may also interest: How To Fix Excel Problem With Formula Stuck Issue

Part 2. How to Fix the Formula in Excel Not Working?

If a formula is not working on Excel, there are many things you can do to try to fix the problem. The exact solution depends on the problem scenario. And as mentioned, there are three scenarios: "Not working", "not updating," and "not calculating." Here's how to deal with each.

Scenario 1: Formulas Are Not Working in Excel

This problem appears in a rather strange way: the formula returns a wrong outcome or an error. But the good news is that it's easy to fix, and here's how:

Check that the Opening and Closing Parentheses in a Formula Match

Excel functions' arguments are entered with parentheses. If you're dealing with a complex formula, you may need more than one set of parentheses - one within another. That helps to show the order in which the calculations should happen.

When creating such a formula, you might have mismatched the opening and closing parenthesis. Check that you've properly paired the parentheses in the formula.

Ensure Proper Nesting

Nesting Excel functions into each other, for example, creating a nesting IF formula? You should remember the following:

1. Excel 2003 and older; you can only use up to 7 nested functions.
2. Excel 2007, 2010, 2013, and 2016; you can use up to 64 nested functions.

Enter all Important Arguments in an Excel Function

All functions in Excel have at least one required argument. Some functions also include optional arguments. These are enclosed in square brackets in the formula's syntax.

If a formula doesn't have all the required arguments, Excel displays "You've entered too few arguments for this function." And if you enter more arguments than needed, you'll get the "You've entered too many arguments for this function" alert.

Check that You've Not Enclosed Numbers in Double Quotes

Excel interprets any value you enclose in double quotes as a text string. That means if you enter a formula like =IF(A3>0. "2"), Excel treats number 2 as text. Thus, you will be unable to use the returned 3's in other computations. You can quickly fix this by removing the double quotation marks around "2" so that the formula reads =IF(A3>0,2).

So, check that you've not enclosed numbers in double-quotes. Otherwise, they will be treated as text, returning an error or wrong result.

Remove Any Formatting on Numbers

Formatting on numbers is a big no in Excel. For example, you might have added a currency sign like $ or a decimal separator. Remember that a comma in Excel formulas is used to separate a function's arguments.

For example, input 50000 instead of 50,000, then use the Format Cells dialog to format the result as you want it.

Ensure You've Not Formatted Numbers as Text Values

At a glance, numbers formatted as text values look like normal numbers. However, Excel interprets them as text strings and excludes them in calculations.

Watch out for the following visual indicators of text numbers:

• The Number Format box on the Home tab in the Number group shows the Text format.
• Numbers formatted as text, by default, are left-aligned.
• Normal numbers are right-aligned in cells.
• There may be a preceding apostrophe within the formula bar.
• Or green marks in the top-left section of the cells
• When you select several cells with text numbers on the sheet, the Status Bar will only show Count. But it usually displays Average, Count, and SUM for numbers.

You can fix this by selecting all problematic cells and then clicking the warning sign. After that, click Convert to Numbers.

• Enclose Workbook and Worksheet Names in Single Quotes

Are you referring to other workbooks or worksheets with spaces or non-alphabetical characters in their names? Enclose the names in single quotation marks. For instance,

Referring to another workbook:

=SUM('[2024 Sales.xlsx]Oct Sales'!B3:B9)

Referring to another sheet:

=SUM('Oct Sales'!B3:B9)

Include the Entire Path to Closed Workbook

Suppose you’re writing a formula referencing a closed Excel workbook. In that case, the external reference must include the workbook name and the entire path to the workbook. For example:

=SUM('F:\Reports\[Sales.xlsx]Oct'!B2:B10)

Use the Right Character to Separate Function Arguments

Using the wrong character for separating function arguments could be another reason your Excel formula is not working. Most people are used to using commas to separate function arguments. But this doesn't always work in Excel.

The character used to separate arguments depends on the set of List Separators in your Regional Settings. For example, a Comma is a list separator mainly used in North America. However, in Europe, the semicolon is used as the list separator, while the comma is used as a decimal symbol.

If your formulas return the "We found a problem with this formula..." error, go to Control Panel > Region and Language > Additional Settings. See what character is set as List Separator there. Use that exact character to separate arguments in your formulas.

Scenario 2: Excel Formulas Do Not Update Automatically

In this scenario, your formulas continue to show the old value even after changing the values of the dependent cells. The most common cause of the "Excel formulas not updating automatically" issue is the Calculation setting. If it has been set to Manual instead of Automatic. So, to fix this, all you need is to set the Calculation to Automatic.

Here's how to do it:

1. On your Excel ribbon, open the Formulas tab, then click Calculation group.
2. Click Calculation, then choose Automatic.

   

The steps may be a little different, depending on the Excel version you're using:

If you have the 2003 version, follow the path: Tools > Options, then click Calculation, choose Calculation and finally click Automatic.

For the 2007 version:

1. Click the Office button, then choose Excel options.
2. Click Formulas, then go to Workbook Calculation > Automatic.

In Excel 2010, 2013, and 2016:

1. Go to File > Options > Formulas.
2. Choose the Calculation options, then select the Automatic option, which is below the Workbook Calculation.

   

You may also interest: Solving Excel 365 Freezes: 10 Quick and Easy Fixes

Scenario 3: Excel Formulas Not Calculating

If you’re in this scenario, your formula is not working in Excel because a cell shows the formula but fails to display the result.

Here's how to resolve the issue:

Turn off the Show Formulas Mode

A common reason for Excel formulas not calculating is when you've accidentally turned on the "Show Formulas " mode in a worksheet. To resolve this problem, turn on this feature by clicking Show Formulas on the Formulas tab. Next, choose the Formula Auditing group.

Check that You've Not Entered a Formula as Text

Entering a formula as text could also be why your Excel IF formula is not working. Select the formula cell and check out the Number Format box in the Number group on the Home tab.

If you've indeed entered a formula as text, change the cell format to General. While in the cell, press F2 + Enter to force the formula to recalculate and return the calculated value.

Remove an Apostrophe/Space Before the Equal Sign in a Formula Cell

If you have accidentally entered an apostrophe (') or space before the equal sign, Excel will treat the cell contents as text. As a result, it won't evaluate any formula within that cell. You can quickly fix this by removing the leading single quote or space.

You can’t miss:
5 Ways to Fix "We found a problem with some content" Error in Excel
How to Resolve "Excel Running Slow"?

Part 3. How to Repair Corrupted Excel Files in the Easiest Way?

You've effectively fixed the "my formula is not working in Excel" issue, worked on a file, and saved it. But upon opening it, you're disappointed; the file is corrupted and doesn’t open. Many things can cause Excel file corruption. These include corrupt storage, malware attacks, logical errors, or app-related issues.

Whatever the cause, your most important task right now is to fix the file. You can do that using Repairit File Repair. Repairit is a user-friendly application that can fix multiple Excel files by following a simple click-through process.

Wondershare Repairit - File Repair

Try It Free Try It Free

1,010,994 people have downloaded it.

• Repair damaged Excel files with all levels of corruption, including blank files, files not opening, unrecognizable format, unreadable content, files layout changed, etc.

• Repairs all kinds of data stored in damaged Excel files such as tables, charts, formulas, comments, images, etc.

• Support all formats of Excel files: XLSX/XLSM/XLTX/XLTM/XLS

• Perfectly repair corrupted files with a very high success rate, without modifying the original file.

• No limit to the number and size of the repairable files.

• Support Windows 11/10/8/7/Vista, Windows Server 2003/2008/2012/2016/2019/2022, and macOS 10.12~macOS 15.

• Except Excel, Repairit also supports all formats of PDF, Word, Excel, PowerPoint, ZIP, and Adobe files.

Follow these simple steps to repair your corrupted Excel file.

1. Install the software on your Mac or Windows computer. After the installation, open Repairit and go to File Repair. Click +Add to add the file(s) that you want to repair.

   

2. Select the location and start the Repair. The software will fix the selected locations and provide a list of Excel files that have been repaired.

   

3. Click the Preview button to preview the repaired XLSX files. Then, decide which one you want to save.

   

Conclusion

You now know how to deal with the issue of formulas in Excel not working. You've discovered how to correct each of the most common scenarios: not working, where a formula returns the wrong result or error, not updating, and not calculating. Use the tips above to fix your situation and get your Excel to work again.