Excel Formulas Not Working– The Ultimate Fixes You Need!

Undoubtedly, Microsoft Excel provides several functions to analyze, audit, and calculate data. Some of the formulas are frequently used by users by a small group of statistical specialists. Formulas make the work easy for maintaining and carrying out complex calculations as well. But what if Excel formulas not working? Well, it’s a very frustrating issue, but it can be fixed by applying the solutions mentioned in this post.

So, let’s delve deep into this post…

To recover Excel sheet data, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

  1. Try Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
  2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
  3. Preview the repaired files and click Save File to save the files at desired location.

Why Are My Excel Formulas Not Working?

The formula not working in Excel due to numerous reasons, including incorrect syntax, data inconsistencies, or referencing errors. However, such problems should be fixed as soon as you can to streamline your data analysis and improve productivity.

How to Fix Formulas Not Working in Excel?

The occurrence of Excel formula not working issue returns the wrong results or sometimes errors too. So, in this section, you will get an idea of how to fix silly mistakes usually made at the time of creating Excel formulas.

1. Enter Numbers Without Any Formatting

While using the Excel formula, don’t add a currency sign eg: $ or € or a decimal separator.

Note: in the Excel formulas, a sign of comma is generally used to separate the function of arguments. Whereas, the sign of dollar is for making an absolute cell reference.

So, you can put a numeric value like this, 50000, instead of putting data like this $50,000. All in all, I just want to say in your Excel formula simply use the numeric value.

2. Numbers shouldn’t be formatted as text values

Another very common reason for the Excel formula not working is that numbers are formatted as text values. It may look like a normal number but MS Excel considers it as a text string and doesn’t include it in the calculations.

Make sure numbers are not formatted as text values

Visual indicators of a text editor are like this:

  • Numbers that are formatted as “text” will by default, get left-aligned while formal numbers are aligned right in the cells.
  • You will see that on the Home tab> number group, the number format box is selected with Text option.
  • When many cells having text numbers have been selected from the worksheet you will see that the Status Bar will only show the Count. whereas generally, it displays the count, sum, and average of numbers.
  • You will see a green triangle shape in the cell’s top-left corner or a leading apostrophe visible in the formula bar.

make-sure-numbers-are-not-formatted-as-text-values

To fix this, just select all the problems having cells and then tap on the yellow warning sign. After that select, the option Convert to Number.

excel-convert-to-number

But in some cases, neither the green triangles nor the warning signs seem in the cells. Then in that case go to the Home tab > Number group >number format box. if this shows the text, then clear all the formatting of the cells having the issue. after then set the format of the cell either general or number.

But if it still won’t work then make a new column and enter the data manually. Means just copy down your text to the notepad and then paste it on your Excel sheet. At the end, delete all broken columns.

3. Match All Opening And Closing Parentheses  In A Formula

Match all Opening and Closing Parentheses  in a Formula

Generally, arguments of the Excel functions are kept under parentheses and in complex formulas; you may need to put more than a single set of parentheses. When making such complex formula make sure that you have opened and closed the parentheses properly.

Well, Excel shows parentheses pair in some different colors when you use it in the formula. Suppose your formula is lacking with some parentheses then Excel will display the error and allow you to correct it by balancing the pair. And due to this, users start getting Excel Formulas Not Working error message.

4. Put All The Required Arguments Within The Excel Function

Enter all Required Arguments in an Excel Function

Each Excel function has single or multiple required arguments. If you are entering optional arguments then enclosed it in the [square brackets] in your formula’s syntax. So, the formula must have all the necessary arguments. Or else your Excel will display the following You’ve entered too few arguments for this functionwarning message.

If the entered arguments are more than the required one then you will encounter with following You’ve entered too many arguments for this function” warning message.

5. Include The Full Path To A Closed Workbook

Include the Full Path in excel

If you are using a formula that references a closed Excel workbook, then your external reference must include the workbook name and the entire path to the workbook. For example:

=SUM(‘D:\Reports\[Sales.xlsx]Jan’!B2:B10)

6. Don’t Nest More Than 64 Functions Within A Formula

Do not Nest more than 64 Functions in a Formula

If you are nesting more than two Excel functions with each other for example suppose to create a nested IF formula. Then follow these limitations:

  • In Excel 2016/2013/2010/2007, you can make use of max 64 nested functions.
  • While in Excel 2003 and lower, you can only make use of a maximum of 7 nested functions.

7. Don’t Enclose Numbers In Double Quotes

Do not Enclose Numbers in Double Quotes in excel formula

In the Excel formula, the value encoded within the double quotes is counted as a text string.

That means, if you enter an Excel formula like=IF(A1>0, “1”), then Excel will consider 1 as a text, and thus you won’t be able to make use of the returned 1’s in any other calculations. So, don’t put the double quotes and always write the formula for numeric value, don’t enclose numbers in double-quotes until and unless you want it to count as text.

8. Enclose Workbook And Worksheet Names In Single Quotes

When referring to other worksheets or workbooks that have spaces or non-alphabetical characters in their names, enclose the names in single quotation marks.

For example, Reference to another sheet:
=SUM(‘Jan Sales’!B2:B10)

Reference to another workbook:
=SUM(‘[2015 Sales.xlsx]Jan sales’!B2:B10)

9. Use Right Charcter For Separating The Function ArgumentSeparate Function Arguments with a Proper Character

Most of you separate the Excel function arguments with the comma; however, this doesn’t work on all Excel workbooks. The character that you generally use to divide arguments, moreover, depends on the List Separator, which is set with the Regional Settings.

European countries use for assigning the decimal symbol. Whereas, for the list separator semicolon is used.

For e.g: North American Excel user would write =IF(A1>0, “OK”, “Not OK”), whereas European Excel users would put the same formula like this =IF(A1>0; “OK”; “Not OK”).

 If you suddenly start getting Excel Formulas Not Working issue because of the “We found a problem with this formula…” error. in that case just go to your Regional Settings, which is present in:

(Control Panel > Region and Language > Additional Settings)

After that make a check of the character that is set for List Separator over there. Make use of the same character for dividing the arguments in the formula of Excel.

Try the given tricks to solve the formula not working in Excel and fix the error easily.

How To Fix Excel Formulas Not Updating Automatically?

How To Fix Excel Formulas Not Updating Automatically

When you are noticing that the Excel formula is not updating automatically the reason can be your Excel calculation setting is somehow got changed. Generally, it is found that when the calculation setting is changed from automatic to manual. At that time Excel formula is not updating automatically as issues were being faced.

Here is how to change the calculation setting to fix the Excel Formula Is Not Updating issue.

  • Go to the Excel ribbon, and then to the Formulas.
  • From this formula tab, choose the Calculation group.
  • Tap to arrow key present next to Calculation Options. From the drop-down, choose the Automatic option.

Excel formulas not updating 1

Or else, you can modify the calculation setting through these steps:

  • For Excel 2019, Excel 2016Excel 2013, and Excel 2010  user:

Open Excel worksheet <  Formulas <  Calculation options group. After then choose the Automatic option present within Workbook Calculation.

  • For Excel 2007 user:

Tap to the Office button <  Excel options <  Formulas <  Workbook Calculation Automatic.

Excel formulas not updating 2

  • For Excel 2003 user:

From your Excel ribbon, choose the Tools < OptionsCalculationCalculationAutomatic.

How To Fix Excel Formulas Not Calculating Automatically?

So, check out the reasons along with their fixes to troubleshoot Excel formulas not calculating issues.

1. Show Formulas Mode Is Left On

One very common reason behind Excel formula not calculating error is that by mistakenly show formula mode has been activated in the worksheet.

So, to fix this Excel formula not calculating issue it’s compulsory to turn off Show Formulas mode.

Here is the step to be followed:

  • Go to the Excel Formulas tab and from the Formula Auditing group tap to the Show Formulas

Excel formulas not calculating 1

2. A Formula Is Entered As Text

The second reason behind the occurrence of Excel formula not calculating error is that Excel formulas is been formatted like text. For checking this out, follow these steps:

  • From the Excel ribbon go to the home tab and then choose the Number group.
  • Now choose the formula cell after then have a look over the Number Format box.
  • If it’s showing text in the number group then change it to General and press F2 button from the keyword, being in the cell.
  • Now enter any Excel formula so that it will recalculate the data again and show the calculated data.

Excel formulas not calculating 2

Steps To Recalculate Excel data

If due to any reason, you want to set the Calculation option to Manual. In excel you have the option to force the Excel formulas to recalculate the data again. For this, you need to click on the calculate button.

So, check this out how to force the Excel formulas to recalculate:

For Recalculating Entire Workbook Data:

  • From the Formulas tab choose the Calculation group.
  • From the opened Calculation group tap to Calculate Now.

For Recalculating  Any Specific Active Sheet:

  • From the Formulas tab choose the Calculation group.
  • From the opened Calculation group tap to Calculate Sheet.

Excel formulas not updating 3

If you to perform this recalculation task in all sheets of your opened Excel workbooks. Then for this just press the Ctrl + Alt + F9 button simultaneously.

In case you want to recalculate one single formula on the sheet then choose the formula cell. After that, get into editing mode by double-tapping the cell or by hitting the F2 button. At last hit Enter key from your keyboard.

Automatic Solution: MS Excel Repair Tool

Apart from the manual solution, sometimes the Excel file gets corrupted and starts showing various errors; then, in this case, make use of the MS Excel Repair Tool. This is the best repair utility to fix all sorts of issues, corruption, and even errors in the Excel file.

With the help of these users can repair the Excel file easily and also restore the entire corrupted data, including cell comments, charts, other data, and worksheet properties. This is a professionally designed program that can easily repair .xls and .xlsx files and is easy to use.

Try Now       Buy Now
* Free version of the product only previews recoverable data.

Steps to Utilize MS Excel Repair Tool:

excel-repair-main-interface-1
stellar-repair-for-excel-select-file-2
stellar-repair-for-excel-repairing-3
stellar-repair-for-excel-preview-4
stellar-repair-for-excel-save-5
stellar-repair-for-excel-saving-6
stellar-repair-for-excel-repaired-7
previous arrow
next arrow

Frequently Asked Questions:

How Do I Enable Formulas in Excel?

To enable the formulas in Microsoft Excel, follow the below steps:

  • Go to the Protection tab, clear a Hidden check box.
  • Then go to the Formulas >> choose Show Formulas to enable the feature.

Why Are My Formulas Not Working in Excel Value?

It might be possible that your formula refers to other cells that hold spaces, comma, or even trickier: hidden spaces.

How to Unfreeze Formula in Excel?

If you want to unfreeze formula in Excel, click on the View tab > Window > Unfreeze Panes option.

What To Do If Excel Is Not Taking Formulas?

If Microsoft Excel is not taking formulas, ensure that automatic calculation feature is enabled.

How Do I Refresh Formulas in Excel?

To refresh formulas in MS Excel, you can either recalculate all the formulas in a spreadsheet or a specific range. However, you can use the F9 key, Ctrl + Alt + F9, Shift + F9, or manually recalculating via the ‘Formulas’ tab.

How to Restore Formula in Excel?

To restore a lost formula bar in Excel, you have to follow the below steps carefully:

  • Click on the File (or the Office button in earlier Excel versions).
  • Then, click on the Options.
  • Click on Advanced in the left pane.
  • Now, scroll down to the Display section >> select the Show Formula bar option.

Final Thoughts

When Excel formulas don’t work, don’t panic. Most issues come from minor settings or entry errors. All you need to do is to go through these steps methodically mentioned above, and you’ll have your spreadsheet back on track in minutes.

Keep your formulas clean, review inputs carefully, and Excel will do the heavy lifting.



Priyanka is a content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.