Excel Formula Not Working

Excel Formulas not working is a problem that can be rectified by knowing the reasons in Excel. Users use MS Excel to create and manage our data and calculations. But, since Excel is a sensitive application, certain actions, words and symbols can affect our results.

For example, consider the below table. The user has tried to add the numbers using SUM formula. But we can see that the formula is not working properly.

It is because, there is an additional space unnecessary in the formula, as highlighted in the below image.

In this article, let us discuss the reasons why Excel formulas may not work.

• Excel formulas not working is the scenario where formulas in excel may not work as they want to.
• Some of the reasons why Excel formulas may not work are:
  • Cells Formatted as Text
  • Accidentally Typed the keys CTRL + `
  • Values are Different & Result is Different
  • Don’t Enclose Numbers in Double Quotes
  • Check If Formulas are Enclosed in Double Quotes
  • Space Before the Excel Formula
• As the first step, check if there are any empty spaces or symbols if excel formulas are not working.

1. Reason #1 – Cells Formatted as Text
2. Reason #2 – Accidentally Typed the keys CTRL + `
3. Reason #3 – Values are Different & Result is Different
4. Reason #4 – Don’t Enclose Numbers in Double Quotes
5. Reason #5 – Check If Formulas are Enclosed in Double Quotes
6. Reason #6 – Space Before the Excel Formula

#1 - Cells Formatted as Text

Now let us look at the solutions for the reasons given above for the Excel formula not working.

Now take a look at the first possibility of the formula showing the formula itself, not the result of the formula. For example, look at the below image where the SUM function in excel shows the formula, not the result.

The first thing we need to look into is the format of the cells; these cells are D1, D2, and D3. So, now take a look at the format of these cells.

It is formatted as text. When the cells are formatted as text, Excel cannot read numbers and return the result for your applied formula.

Solution

#2 Accidentally Typed the keys CTRL + `

Often in Excel, when working in a hurry, we tend to type keys that are not required, which is an accidental incident. But if we do not know which key we typed, we may get an unusual result.

One such moment is SHOW FORMULAS in excel shortcut key CTRL + `. If we have accidentally typed this key, we may see the result like the picture below.

As we said, the reason could be the accidental pressing of the show formula shortcut key.

Solution

The solution is to try typing the same key again to get back the results of the formula rather than the formula itself.

#3 Values are Different & Result is Different

Sometimes, we see different numbers in Excel, but the formula shows different results. For example, the below image displays one such situation.

In cells D1, D2, and D3, we have 10 as the value. In cell D4, we have applied the SUM function to get the total value of cells D1, D2, and D3. But the result says 40 instead of 30.

All the Excel file calculations are set to automatic. But to enhance the speed of the large data files, the user might have changed the auto calculation to a manual one.

Solution

We can fix this in two ways. One is we can turn on the calculation to “Automatic.”

Either we can do one more thing. We can also press the shortcut key F9, which is nothing but “Calculate Now” under the “Formulas” bar.

#4 - Don’t Enclose Numbers in Double Quotes

We must pass the numerical values to get the desired result in situations inside the formula. For example, please take a look at the below image; it shows cities and the average temperature in the city.

If the temperature is greater than 25, then the average should be 25, and if the temperature is less than 25, then the average should be 20. Finally, we will apply the  IF condition in excel to get the results.

We have supplied the numerical results double-quotes =IF (B2>25,” 25″,” 20″). Unfortunately, when the numbers are passed in double-quotes, Excel treats them as text values. Therefore, we cannot do any calculations with text numbers.

Always pass the numerical values without double quotes like the below image.

Now, we can do all sorts of calculations with these numerical values.

#5 - Check If Formulas are Enclosed in Double Quotes

We need to ensure formulas are not wrapped in double quotes. It happens when we copy formulas from online websites and paste them as it is. If the formula is mentioned in double quotes for understanding, we need to remove double quotes and paste them. Otherwise, we may get only the formulas, not the result of the formula.

#6 - Space Before the Excel Formula

We all humans make mistakes. Typing mistake is one of the errors for the Excel formula not working. We usually commit day in and day out in our workplace. If we type one or more spaces before we start our formula, it breaks the rule of the formulas in Excel. As a result, we may end up with only the Excel formula, not the result of the formula.

Excel formulas may not update because users may not have enabled ‘Automatic ‘ calculation mode. Checking for automatic calculation is important to make sure Excel formulas get updated in real-time.

If Automatic mode is not enabled or if it is not working, we can force Excel to update formulas by using the shortcut keys Ctrl + Alt + F9.

Excel formulas may not perform calculations as we desire to if unnecessary spacing is mentioned in the formula.

For example, consider the below image. We can see additional space before the formula.

In this scenario, we can simply remove the extra space to make sure the excel formula works.

• Excel is an application used to manage our data.
• We can use in-built formulas and functions as per our needs but sometimes, excel formulas may not work.
• Simply checking the formula if it has any unnecessary symbols or spaces will rectify the problem and ensures the function works properly.

This article has been a guide to Excel Formula Not Working and Updating. Here, we discuss the Top 6 reasons and solutions for those Excel formulas not working and updating, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: -

• Excel Minus Formula
• Excel VBA IFERROR
• Formula Excel Errors
• Max Formula in Excel