Excel formula not calculated? 14 reasons and how to solve it (2023)

We've all been there, and often at the worst times. For whatever reason, Excel formulas are not being calculated correctly. The good news is that it's usually pretty simple. Once we know the most likely cause, it is easier to fix the problem. So in this post, we will understand the most likely reasons why Excel formulas fail to calculate.

The 14 causes and solutions in this article should give you all you need to know. So if you're worried that Excel has stopped calculating or the numbers aren't updating, we've got the answer for you.

watch the video

Watch the video on YouTube

Calculation options - automatic or manual (#1)

Excel has two calculation options,automaticIhandbook.Most users don't even know these two features exist. But, unfortunately, in some cases they change without our knowledge.

Automatic calculation is the default mode. Here, the formula is recalculated after any changes that affect the calculated result.

Excel formulas handle calculations efficiently. Behind the scenes, Excel knows which cells affect which formulas. Therefore, the automatic calculation function recalculates the minimum number of cells. This ensures that Excel runs quickly and keeps all values ​​up to date. This is the behavior we understand and expect.

However, due to the size and complexity of some spreadsheets or third-party plugins, calculations can become very slow. Because of this, some users have changed the Excel options to manual calculation mode. Therefore, Excel does not recalculate anything. In manual calculation mode, we can change the cell value, but nothing happens. So we need to explicitly tell Excel when to recalculate.

So if Excel is in manual calculation mode, there is a problem because we see that the Excel formulas are not calculated.

notes:Unfortunately, Excel can switch between manual and automatic calculation mode without our knowledge. So check out this article:Why does Excel's calculation method keep changing?

To determine which calculation method is enabled, clickTypes > Calculation Options (dropdown menu)A check mark .in the drop-down list indicates which calculation option is applied.

clickautomaticto ensure that the calculation is done automatically.

The calculation method is an application-level setting. Therefore, it applies to all open workbooks.

If you want to stay in manual calculation mode, here are some useful shortcuts.

• F9: Calculates formulas in all open workbooks that have changed since the last calculation, as well as formulas that depend on them.
• Shift+F9: Calculates the formulas on the active sheet that have changed since the last calculation and the formulas that depend on them.
• Ctrl + Alt + F9: Calculates all formulas in all open workbooks, regardless of whether they have changed since the last time.
• Ctrl+Shift+Alt+F9: Rechecks linked formulas, then evaluates all formulas in all open workbooks, regardless of whether they've changed since the last time.

This is probably the first reason why we see Excel formulas not calculating.

important tip:Add auto/manual calculation option to your QAT. This is an easy way to switch between modes and determine which mode is active.

wrong number format (#2)

When we look at a cell value, we decide what kind of data it is. We instinctively know that numbers can add up, but text can't. Unfortunately, Excel isn't that smart.

withinHome > Number,We can choose the cell format.

If the cells are formatted as text instead of numbers, Excel may not be able to calculate and may not display the values ​​we expect.

See the screenshot below.

In cell B4, the sum of B2:B3 is equal to 1. 1+1 = 1 is definitely wrong. This is because B3 is formatted as text and the SUM function ignores the text. Although the calculation may look like 1+1 = 1 (which is wrong), it is actually 1+0 = 1 (which is correct).

To fix this, there are 3 options:

1. manual change
   • Change cell format from text to another format (Generally a good choice to start with)
   • Double-click the problematic cell to go into edit mode
   • Press enter to submit the formula
2. If the cell has a green triangle:
   • click the cell
   • chooseconvert to numberfrom the notification drop-down menu
     
3. add zero to the number
   • Select any empty cell.
   • copy the cell
   • Select cells to convert to numbers
   • clickHome > Pasta > Special pasta...
   • from the insidespecial pastewindow, selectAdd to,then clickWell

#1 applies specifically to numbers formatted as text, but #2 and #3 can be used in many scenarios where numbers are stored as text.

leading apostrophe (#3)

The apostrophe (‘) is a special character in Excel. Whenever you insert an apostrophe at the beginning of a value or formula, it tells Excel that everything that follows is text.

This special character ensures that we can store numbers as text. For example, if we want to store employee numbers that contain zero initials, Excel can drop the zeros in the data entry.

So we insert an apostrophe to make sure Excel understands it as text.

However, sometimes apostrophes appear when we don't need them. This usually happens when data is imported from an external system.

The screenshot below shows that the calculation is not working as expected. The formula in cell B4 is SUM(B2:B3), so 1 + 1 = 2, but the value in B4 is equal to 1. This is because cell B3 contains a value preceded by an apostrophe.

Remove the apostrophes manually or use techniques #2 and #3 in the previous section to fix the problem.

It has a similar effect on formulas. For example, in the screenshot below, the SUM function has an apostrophe at the beginning. therefore it is treated as text.

Since it is type, you must manually remove the apostrophes. Then you are ready.

Leading and trailing spaces (#4)

Leading and trailing spaces are a big problem, they often appear in the input text, but we can't see them with the naked eye.

Let's look at an example.

In the screenshot above, the formula in cell B13 is:

=SUMIFS($B$2:$B$9,$A$2:$A$9,A13)

We implement the basic SUMIFS function to add the value of the Alpha product. According to the data, the value should be 50, but it is evaluated to zero.

The problem is that all the values ​​in cells A2:A9 have spaces at the end. Instead"ONE", for the SUMIFS function in cell A13 the value in the data is"ONE"(with spaces at the end). Excel treats them as different values. As a result, there is no match.

There are several options for solving this problem. Some suggestions are:

• Manually remove the space from the data
• Add transmission intervals to the SUMIFS template
• Use the TRIM function to remove extra spaces from the beginning/end of text
• If there are no spaces in the text, we can use search and replace to remove the extra spaces.

Numbers in double quotes (#5)

Another text/number problem occurs when numbers are enclosed in double quotes.

See the screenshot below. There is a problem. 100+50+100 definitely does not equal 200.

In this scenario, the minimum sales volume is 50. Therefore, the value in row 3 is correctly increased from 30 to 50 using the IF function. So the total should be 250. So what happened?

The problem occurs because of the calculation in cell C3.

The type is:

=IF(B3<50;"50";B3)

In cell B3, the value is less than 50. Therefore, the text value "50" is returned in cell C3. The double quotes tell Excel that this is text, not numbers.

To fix this, remove the double quotes around the number 50. The correct total will then be calculated.

Bad type parameter (#6)

Excel functions are a programming language used to calculate results. Each function has its own syntax (ie the parameters required to calculate the result). Getting this syntax wrong can lead to unexpected results.

VLOOKUP is a very error-prone function. caught many unsuspecting users.

Let's look at an example.

In the example above, the formula in cell E3 is:

=VLOOKUP(D3,A2:B4,2)

Charlie's score is 40, which is correct.

Now let's change the value on D3 to Bravo:

40 more coming back!! Well done it should be 20. How can it be?

In our formula, we exclude the fourth parameter of the VLOOKUP function, the Range_Lookup parameter.

• If we type TRUE or omit this parameter, we tell Excel:
  • The first column in our data is sorted in ascending order
  • We want to return a value that is closer to, but not greater than, the requested value (called an approximate match).
• If we enter FALSE as the fourth argument, Excel will return only an exact match.

Our first column is not in ascending order, but excluding the 4th argument, Excel thinks it is sorted. As a result, Excel calculated incorrect results.

If we go back and change our formula so that the fourth parameter is FALSE, it works.

This shows the importance of understanding what all function parameters do.

Characters not printing (#7)

Non-printable characters are letters used in computer codes that cannot be read by humans. To give a simple example, we can insert line breaks in Excel usingAlt + EnterNot for printing.

In the screenshot above, the LEN function displays 6 characters in cell C2. But the word "hello" has only 5 characters.

There is no space in cell C2, why? This is because there is a newline at the end.

Using the CLEAN function, we can remove non-printable characters.

See the screenshot above. After using the CLEAN function, the value is now correct again.

Circular Reports (#8)

A circular reference is where a formula refers to cells in its own calculation chain. Here is an example.

The formula in cell B5 is:

=zbroj(B2:B5)

If you notice, the cell reference for the formula (B5) is in the cell range (B2:B5). Therefore, Excel cannot calculate the result (unless iterative calculation is enabled).

When we create or open a workbook with circular references, Excel often displays an error message:

"One or more circular references exist when a formula directly or indirectly refers to its own cell. This can lead them to misjudge.

Try removing or changing these references or moving the formula to another cell."

However, the error message will not stop us from clickingWelland carry on as if nothing happened.

If there are circular references, it is difficult to find them manually. Fortunately, Excel provides us with a toolTypes > Type Review > Error Checking > Circular ReferencesShows circular references in any of our open workbooks.

As shown above, cell B5 has a circular reference. Once we remove it it will calculate correctly.

Show Type (#9)

In Excel, we usually look at the results of calculations. However, with a single click or using a shortcut, we can quickly switch to displaying formulas instead of results.

Activating the Reveal type can simply be done by mistake or saved in this state by a previous user.

To see the result of the formula again, click onFormulas > View Formulas > Show Formulasor use the shortcut keyCtrl + `(the key to the left of the 1 key on a standard Windows keyboard).

Incorrect cell references (#10)

One of the things we learned very early in Excel is to use the dollar sign ($) to lock cell references. For example:

• If we put =A1 in a cell and copy it, it becomes =A2.
• If we type =A$1 and copy the cell down, =A$1 will remain.

We quickly get used to this citation syntax. However, this makes it easy to make mistakes when copying formulas to other cells.

See the screenshot above. There must be something wrong with the calculation in cell D6.

Double-click a cell and the problem will quickly become apparent.

Yep...someone forgot to use the $ sign in the formula in cell C6, so they copied the formula into cells D6 through F6.

Unfortunately, this is easy to do. I've done it myself many times.

How to find these kinds of errors? The only way to do this is to check our work as we go and have a healthy skepticism about the results of any calculations. It is better to examine yourself and find fault than to sit in a meeting with a manager and find fault.

Automatic data entry transformation (#11)

In order to reduce the number of errors, Excel is programmed to automatically correct/change some data as we type it. This is great most of the time and who knows how many times it has saved us from embarrassing mistakes.

datum

However, autocorrect can cause problems in other cases. For example, if we put 1/1 in Excel, what do we want? Do we want a result of 1? Or do we want January 1st? In this case, Excel assumes we want January 1 of the current year.

Dates are the most common type of automatic conversion. Dates in Excel are actually numbers, based on the number of days since December 31, 1899. So, assuming the current year is 2022, if we type 1/1, Excel thinks we need January 1, 2022 (which is the number 44562). We can see this when we change the date to number format.

We tried entering a value of 1/1 and got a value of 44562. Hmm... that could cause us problems.

leading zeros (#12)

We saw in #3 above that leading zeros can be a problem.

If we try to enter a number with leading zeros, such as 0005321, Excel assumes we don't need leading zeros and converts the number to 5321.

To keep the original zeros, we should:

• add an apostrophe at the beginning
• Convert the data type to text before entering the value

Hidden Rows and Columns (#13)

It's one of my pet peeves. Hidden rows and columns can cause many problems for Excel users. In my opinion, they should be avoided at all costs.

Let's look at an example:

See the screenshot above. The formula in cell B5 is:

=sum(B2:B4)

Is this result correct? Actually it is. But it turned out that the covert exercise looked wrong. This is because the hidden row contains an additional value, as we can see when we reveal row 3.

If we want to exclude hidden cells from the calculation, we should consider usingIn totalor the subsum function.

Binary to Decimal Conversion (#14)

Computers store numbers in binary (a mixture of 1s and 0s). However, we learn numbers using the base-10 decimal system. As a result, Excel must convert binary to decimal numbers and vice versa, which can lead to some unexpected discrepancies.

We know that one-third (1/3) cannot be expressed as a decimal. gives 0.333333... back to infinity. Therefore, we tend to use bit-reduced approaches.

In the binary system, there are also numbers where the circular number becomes infinity. One tenth (1/10) can easily be represented as a decimal number: 0.1. But in binary, it's 00011001100110011... it goes back to infinity.

Excel calculates to 15 significant figures. Therefore, there may be slight rounding differences when converting from binary to decimal. While this probably doesn't matter, it could lead to different results if used in a boolean statement.

See this article for more details:Excel may calculate incorrect results: warning

in conclusion

In this article, we present to you the most likely reasons we have found that Excel formulas are not calculated as expected. I believe these 14 techniques helped to solve your problem.

If you find other simple problems that cause incorrect calculations, please add them in the comments below.

Relevant Articles:

• Why does Excel's calculation method keep changing?
• 7 Ways to Remove Extra Spaces in Excel

About the Author

Hi, I'm Mark and I run Excel Off The Grid.

My parents told me that at the age of 7 I declared that I would become a chartered accountant. I'm either clairvoyant or imaginative because that's exactly what happened. However, it wasn't until I was 35 that my journey began.

In 2015 I started a new job where I often work after 10pm. As a result, I rarely see my children during the week. So I started looking for tips on automating Excel. I've found that by creating a few simple tools, I can combine them in different ways to automate almost all of my daily tasks. This means I can work fewer hours (and get a raise!). Today, I'm passing these tips on to our fellow professionalsEducational programmeThis way I can also spend less time at work (and more time with my kids and doing things they enjoy).

Need help adapting this article to your needs?

I guess the examples in this post don't exactly fit your situation. We all use Excel in different ways, so it's impossible to write an article that suits everyone's needs. Take the time to understand the techniques and principles in this article (and elsewhere on this site) and you should be able to adapt them to your needs.

However, if you still struggle, you should:

1. Read other blogs or watch YouTube videos on the same topic. You get even more by discovering your own solutions.
2. Ask the "Excel Ninjas" in your office. It's amazing what other people know.
3. Ask questions in forums likeYour Excellency, theMicrosoft Answers Community.Remember that people on these forums are usually giving their time for free. So be careful when crafting your questions and make sure they are clear and concise. List everything you've tried, with screenshots, code snippets, and workbook examples.
4. useExcellent storage, who is my consulting partner. They help by offering solutions to small Excel problems.

What's next
Don't go, there's a lot more to learn about Excel Off The Grid. See the last post:

(Video) Excel Formula Not Calculating | Excel showing formula not result | Formula Not Working | Solved !

How to dynamically expand columns in Power Query

How to run Excel macros from Power Automate Desktop

How to create a worksheet for each item in a pivot table field

Power Query: Get data when sheet/table name changes (2 ways)

How to Create a QR Code in Excel for Free (3 Easy Ways)

Move data between workbooks (Power Automate+Scripts Office)

How to filter by lists in Power Query (4 methods)

How to run any macro from a button (Magic Macro)

How to get data from a SharePoint list using Power Query

Excel automatically writes formulas for comments - the magic LAMBDA

View file paths using VBA: Enter values ​​into cells