Excel formulas not working

Microsoft is a great tool for storing and manipulating data in sheets. Thousands of people use Microsoft Excel to manage and organize their data, create charts and graphs, etc. It uses mathematical, logical, statistical formulas on sheets of data to calculate values.

Sometimes, the Excel users face the formulas are not working properly or returning an incorrect result. It will be very annoying and frustrating when the Excel formula stops working all of a sudden. With the help of this chapter, we will help you to resolve the issue occurred due to the formulas that are not working properly.

What if formula stops working?

When the Excel formula stops working suddenly, several questions come to users mind immediately. A question like, has my Excel formula had correct syntax? Is my Excel corrupted due to malicious? How can I resolve the formula error?

You will show you different scenarios in which Excel formulas stop working. This will help you to resume the Excel formula again working. Besides the Excel formula not working, you may face two other issues with the Excel formula. These are –

Excel formula not working – The formula either returns the error or incorrect result.

Excel formula not updating – The value in the Excel sheet does not update and shows the older value even after updating the value of the dependent cell.

Excel formula not calculating – The formula is showing itself instead of the result.

1. Check the cell format to number

It might be possible that you are trying to perform an arithmetic operation on the data looking as numeric, but it is actually formatted as text. In such a case, the Excel formula does not work properly. It either returns an error or an incorrect value.

So, check the cell format and if it is text change it to the Number. Once again, try to perform the operation and see the result. This time, the Excel formula will return the correct result.

For this, just perform a small step to change the cell format –

Select the cell on which you are applying the Excel formula and check the format of cell inside the Home tab and change it to Number.

Note: Try to set the format only to Number for numeric data. If you set it to General, Excel will automatically change to Text format.

2. Check the circular reference

It might be possible that your sheet has a circular reference; that is why the Excel formula is not working fine. You have to check for the circular reference in the sheet if containing and try to remove it.

A circular reference is a formula that includes the cell itself on which it is applying. Circular reference prevents the formula to calculate and return the correct resultant value.

If any cell has a circular reference in an Excel sheet, you will see Circular References keyboard written at the bottom of the Excel sheet window.

If you found a circular reference in your sheet, correct the formula and remove the circular reference. Recalculate the formula after correction and this time, it will return the correct result.

3. Match all opening and closing parenthesis

All the Excel users know that the Excel formula has an opening and closing braces. In case the formula is lengthy and you forget to put one of the braces (parenthesis), you might get an error or incorrect result.

While using any Excel formula, the parenthesis must be in pairs. The order of the opening and closing parenthesis will also be correct. When creating the Excel formula, make sure parenthesis must be in pairs.

4. Do not enclose number in double quotes

If a cell contains the numeric data, do not enclose it in double quotes. Only text or string type of data is enclosed between double-quotes. If you have enclosed the numeric data in double quote inside the formula, your formula may not work fine. It can return an error or incorrect value.

5. Provide all parameters properly

Excel formulas have some optional and some required parameters. The formula must contain all required parameters. They should be provided correctly and should not be less as required. Otherwise, you may get an error message like “You have entered too few arguments for this function.”

In case you provide more parameters than asked by the function, you will get the error message like “You have entered too many arguments for this function” in a dialogue box.

6. Extra space may cause unexpected result

When you write the formula for some data in an Excel sheet and your data contain some extra space, that formula may return an unexpected result or an error. So, the formula does not work properly. If you do not want to face this error, check your data does not contain the extra space as they can lead to an unexpected result.

For example, when you use the VLOOKUP() formula, it may return an unexpected result if your data contain extra space. Due to the extra space, the formula considers the value as a mismatch. Trailing space inside the data is hard to find, but it can be removed. So, check them properly to make the formula and its result correct.

7. Accidently type Ctrl+ shortcut key

Often when you work in a hurry, you sometimes press the Ctrl+ key any shortcut key that misleads the resultant value and you get unexpected results. This can be due to either all have done in a hurry, or you are not aware with the correct shortcut key. So, the users should know when to use which shortcut key.

8. Space before equal sign

When you write a formula in an Excel sheet and mistakenly a space is typed before the formula. This can return an error or unexpected result. For example, if there is one or more space before the formula equal sign, a subtle variation of this problem can occur.

An Excel user should know that a formula written in an Excel sheet must start with an equal (=) sign. But if the formula has space before this, it breaks the rule of formula that may cause the error to occur.

9. Do not use formatting with number

If you are entering the price of any product or thing, do not use any symbol like a dollar ($) or euro with it. Also, do not use any decimal separator with the number. In MS Excel formula, the $ symbol is used to make the cell reference absolute.

You can enter the numeric value directly in the Excel sheet without any symbol. You do not need to place any currency symbol to define the particular value is price type. For example, write 4000 in the Excel cell or any formula instead of $4000.

10. Separate the arguments using correct symbol

Most of the time, values or arguments inside the Excel formula are separated by a comma (,) symbol. You should use the correct separator to separate the arguments inside the Excel formula. It does not work all the time. Sometimes, a semicolon is also used for separation. So, you should wisely choose the separator to separate the values inside the Excel formula so that it does not return an incorrect result.

Excel formula not updating

Sometimes, the Excel users face the problem that the formula is not updating the value after applying the formula on some data. The formula still shows the older value even after updating the cell values used in the formula.

When the Excel formula does not update the value based on the formula, it can be because of the Calculation setting is set to Automatic instead of Manual. To resolve this problem, you have to again set the Calculation setting to Automatic.

For this, follow the steps:

Step 1: Navigate to the Formula tab in the Excel ribbon where click the Calculation Option inside the Calculation group.

Step 2: From the Calculation Option dropdown button, select Automatic.

If it is already set to Automatic mode, you do not need to do anything. Just continue working on your sheet.

Excel formula not calculating

When the cell in an Excel sheet shows the formula rather than its result, it means there is something wrong with the formula. Your formula does not work properly. It can be due to the following reason –

Reason 1: Show formula mode is turned ON

You may sometimes see that the Excel formula is not calculating. The most common reason for this can be that the formula mode is ON. When the Show Formula mode is on, the formula is visible inside the sheet instead of its result. So, you have to turn off the Show formula mode.

To fix the error and show the result for the calculated formula, turn off the Show Formula mode. Follow the given steps to achieve this:

Step 1: Navigate to the Formula tab in the Excel ribbon where click the Show Formula option inside the Formula Auditing group.

Once you click the button, the Show Formula mode will be turned off and your formula will start calculating the values.

Reason 2: Formula is used as text

Another reason why the formula is not calculating can be that the formula is formatted as text. To check that the formula is not formatted as text, select the formula cell and look at the format inside the Number group in Home tab.

If it is Text, then change it to General by selecting it from the Format dropdown list. Once the format is set, recalculate the formula and you see that the formula is perfectly calculating now.

Reason 3: A leading space or apostrophe sign before formula

While writing the formula, if any leading space or apostrophe sign is placed before the formula equal sign, your formula may not calculate. Excel treats the formula inside the cell as text. Consequently, the formula does not evaluate anything.

Note: A leading space often occurs when someone copies a formula from the web and places it into the Excel cell.

Due to the leading space or apostrophe sign, the formula does not evaluate anything. So, remove space or apostrophe, if any before the equal sign in the Excel formula. The Excel users can deal with the formula that is not working by using this.