Excel Formulas not Working, not Calculating: Quick Ways to Fix Them

Microsoft Excel is a spreadsheet application that has been used by millions of people worldwide for more than three decades. Pivot tables, graphing tools, calculations, and a macro programming language are included. In contrast to other apps such as Word, spreadsheet software calculates values using mathematical formulas and data in cells. However, there are times when the Excel formulas do not work properly. These issues can arise due to various reasons, such as incorrect syntax, referencing errors, or data inconsistencies This article will assist you in fixing Excel formula issues helping you streamline your data analysis and improve productivity.

How to Fix Excel Formulas Not Working

If an Excel formula is not working properly and returns an error or incorrect result. To fix it, follow the steps below:

Problem: Change the Calculation Options from Manual to Automatic

If you are unable to update the value you have input and it returns the same value, Excel's calculation option may be set to manual rather than automatic. 

Fix: Change the Calculation Option from Manual to Automatic

Step 1: Open the spreadsheet that is giving you problems. Go to the Formulas tab, then select the Calculation option

Step 2: From the dropdown option, select Automatic

Problem: Cell Set to Text Format

You may have formatted the cells containing the formulas incorrectly as text. When Excel is set to text format, it ignores the applied formula and does not display the result.

Fix: Change the Cell from Text Format to either General or Number

To resolve this issue, follow the procedures below:

Step 1: Select the cell where the formula is not being calculated

Step 2: Navigate to Home, and you'll see a Dropbox with multiple data kinds in the center. Choose General or Number

Step 3: Now, click the cell once more and Select Enter. The formula will be automatically calculated, and the result will be displayed in the cell

Problem: Show Formulas Button Is Turned On

If show formulas button is turned on accidently then the applied formulas will not work.

Fix: Turn off the Show Formulas Button

The Show Formula button is intended for auditing formulas, therefore when selected, it displays the formula rather than the result. As a result, if you previously turned it on, turning it off may help remedy the problem. Here's how to go about it:

Step 1: Navigate to the Formula Tab and click the Show Formulas button under the Formula Auditing Group

Excel formulas Not Updating

The value provided by your Excel formula does not automatically update, which means that the formula displays the old value even after you modify the values of the dependent cells. When Excel formulas do not update automatically, it is most likely because the Calculation setting has been set to Manual rather than Automatic.

To resolve this, simply return the Calculation option to Automatic:

Step 1: Navigate to the Formulas tab on the Excel ribbon, then to the Calculation group, click the Calculation Options button, and then select Automatic.

Excel formulas Not Calculating

If your Excel formula isn't working, the reason can be that a cell displays the function rather than the calculated value, it could be due to one of the following issues.

Problem: Incorrect Excel Formula

While working with Excel, even if you are new to using Functions, a common issue that may arise is the presence of missing or extra characters. For Example, if you use (=) an equal sign or apostrophe(') in a cell can prevent calculations from being performed correctly. This problem may occur when users haven't typed the formula and copied it from other sources.

Fix: Check your Excel Formula

You can fix this problem by following the below tips:

Step 1: Select the cell with the formula and remove any unnecessary apostrophes or spaces at the beginning of the formula

Step 2: You should take care of parenthesis while using lengthy formulas. Ensure that all opening and closing parenthesis match correctly so that the opening and closing parenthesis match correctly so that calculations happen in the right order. Excel can help you with this by displaying parenthesis in different colors to make it easier to track them

Excel will show an error if there is a missing or extra parenthesis in your formula. Keep in mind these common formula problems to make your Excel experience smoother and more productive.

Problem: Wrong Characters Usage

You have to use separate function arguments to use for the excel formulas to calculate and give desired correct outputs.

Fix: Use Proper Characters to Separate Arguments

To get the desired results in Excel, it's essential to know how to separate functions and arguments correctly. Depending upon the Excel formula you're using and your regional settings, you may need to use commas or semicolons to separate arguments.

Using the wrong separator can lead to a "We Found a Problem with this Formula" error in Excel. There's a way to adjust Excel settings to avoid repeating this error:

Step 1: Open Excel's File menu

Step 2: Go to More and select Options

Step 3: Select Language from the left pane

Step 4: In the office display language section, click on the" Add a language" button

Step 5: Choose the desired language to install and click Install

Step 6: Select the newly installed language and click "Set as Preferred"

Step 7: Test your Excel Formulas to see if they are now working correctly

To sync Office settings across your device when logging in with your Microsoft account, check the Store my authoring languages in the Cloud for My Account option.

Force Excel to Recalculate

If users don't prefer to use automatic calculation settings, Excel allows users to Recalculate formulas manually. You can do this by following below methods:

For Recalculating the entire sheet, you can press F9 on your keyboard or choose to Calculate Now under the Formula tab.

You can also recalculate an active sheet by pressing Shift +F9 on your keyboard or by selecting the Calculate sheet from the Calculation group under Formula Tab.

You can also recalculate all formulas across all worksheets by pressing the Ctrl+ Alt +F9.

Check for Circular Reference

If Excel uses circular reference it might sometimes show wrong results. Most of the time, Excel will notify you about a circular reference, but it might not do it every time.

Note: Circular reference refers to the same cell where is does the calculations.

You can manually check for this type of reference if you continuously getting wrong results out of Excel formulas.

Follow the below steps:

Step 1: Open the Formulas Tab

Step 2: Go to Formula Auditing

Step 3: Select Error Checking Circular Reference

Now Excel will show which cells have added a circular reference to the Formula.

Go to the indicated cells and edit the formula so it doesn't include the cell showing the results.

1. Check for any syntax errors.
2. Check missing or extra characters
3. Ensure correct cell reference.

Use Excel's Formulas auditing tools to trace precedents and dependents, and evaluate complex Formulas to identify the issue.

Why Excel Formulas can show errors message?

Excel Formula error message occurs due to the following reasons:

• Division by zero
• Value not found
• Incorrect data types.

To correct these errors, you can use functions such as IFERROR or ISERROR to display custom messages or handle specific situations.

What are the steps that need to be followed to troubleshoot Excel formulas that are not working?

Follow the below steps to troubleshoot Excel Formulas:

Step 1: Check the Formula syntax

Step 2: Use formula auditing tools

Step 3: Handle Error values

Step 4: Verify data types

Step 5: Ensure correct argument separators

Step 6: Review conditional formatting and data validation rules.

How Common Excel Formulas errors can be avoided?

To avoid common Excel formulas:

1. Double- Check formula syntax
2. Ensure correct cell reference
3. Match parenthesis carefully
4. Use correct argument separators
5. Be careful about the data types.
6. Use formula auditing tools

Is it possible to customize error messages in Excel Formulas?

Yes, Error messages can be customized in Excel Formula using Functions like IFERROR and ISERROR. By providing specific messages or alternative values.