Excel Formula Not Working

6 Main Reasons for Excel Formula Not Working (with Solution)

  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
Excel Formula Not Working

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Excel Formula Not Working (wallstreetmojo.com)

#1 Cells Formatted as Text

Now let’s look at the solutions for the reasons given above for the excel formula not working.

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

Excel Formual Not Working Example 1

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

Excel Formual Not Working Example 1-1

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

Change the format of the cells to General or Convert to Numbers.

  1. Select the cells, and on the left-hand side, you will see one small icon, click on that icon, and choose the option “Convert to Numbers.”


    Excel Formual Not Working Example 1-2

  2. Now we must see the result of the formula.


    Excel Formual Not Working Example 1-3

  3. Oh, hang on; we are still not getting the result we are looking for. Now we need to examine the formula cell, whether it is formatted as text or not.


    Excel Formual Not Working Example 1-4

  4. Yes, it is formatted as text, so change the cell format to GENERAL or NUMBER. We must see the result now.


    Excel Formual Not Working Example 1-5

#2 Accidentally Typed the keys CTRL + `

Often in excel, when we are working in a hurry, we tend to type keys that are not required, and it is an accidental incident. But if we don’t know which key we typed, we may end up getting an unusual result.

One such moment is SHOW FORMULAS in excelSHOW FORMULAS In ExcelIn Excel, we can display formulas to investigate the procedure's relationship. To begin, select the formula tab, then formula auditing, and finally show formulas. In addition, there is a keyboard shortcut for it.read more shortcut key CTRL + `. If you have typed this key accidentally, we may the result like the below picture.

Excel Formual Not Working Example 2

As I told 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.

Excel Formual Not Working Example 2-1

#3 Values are Different & Result is Different

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

Excel Formual Not Working Example 3

In cell 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 cell D1, D2, and D3. But the result says 40 instead of 30.

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

Excel Formual Not Working Example 3-1

Solution

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

Excel Formual Not Working Example 3-2

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.

Excel Formual Not Working Example 3-3

#4 Don’t Enclose Numbers in Double Quotes

In situations inside the formula, we require to pass the numerical values to get the desired result. Take a look at the below image; it shows cities and the average temperature in the city.

Working Example 4

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

Working Example 4-1

I have supplied the numerical results double-quotes =IF (B2>25,”25″,”20″). When the numbers are passed in double-quotes, excel treats them as text values; we cannot do any kind of calculation with text numbers.

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

Working Example 4-2

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

#5 Check If Formulas are Enclosed in Double Quotes

We need to make sure formulas are not wrapped in double-quotes. This happens when we copy formulas from online websites, and we paste as it is. If the formula is mentioned in double-quotes for understanding, we need to remove double quotes and paste; otherwise, we will end up getting only the formulas, not the result of the formula.

Working Example 4-3

#6 Space Before the Excel Formula

We all humans make mistakes. Typing mistake is one of the errors for excel formula not working; we usually commit day in day out in our workplace. If you type one or more space before you start your formula, it breaks the rule of the formulas in excel. We will end up with only the excel formula, not the result of the formula.

Working Example 4-4

Recommended Articles

This has been a guide to Excel Formula Not Working and Updating. Here we discuss the Top 6 Reasons and Solutions of those Excel Formulae not working and updating along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>