How to fix Excel formulas that are not calculated or updated - Excel Campus (2023)

Point:Learn about the different ways to calculate in Excel and what to do if a formula isn't calculated when you edit the cell in question.

SKILL level:beginner

watch the tutorial

Download the Excel file

You can do this with the same workbook I use in the video. I am attaching below:

Why doesn't my type count?

If you have ever come across a spreadsheet formula that isnot calculated automaticallyAs it should be, you know how frustrating it is.

This happened to my friend Brett. He told me he was working on the file and the file was not recalculating the formulas while entering the data. He realizes he needs to edit each cell and presses Enter to update the formula in the cell.

And this only happens on his home computer. His work computer is working normally. It drives him crazy and wastes a lot of time.

The most likely cause of this problem is the computer options pattern, which is a basic setting every Excel user should know.

To check which calculation mode Excel is in, go toofficiallytab and then clickcalculation capabilitiesThis will bring up a menu with three options. thisRight nowThere will be a check mark next to the mode. In the image below you can see this Excelmanual calculation method.

When Excel is inmanual calculationDrawing, formula on the worksheetnot calculated automatically.You can quickly and easily solve the problem by changing the mode toautomatic.In some cases you may want to use the manual calculation method, which I will explain in more detail below.

Setting up calculations is confusing!

isreally importantLearn how computing patterns are changing. Technically, it is aapplication level settings.This means that the setting will apply to all workbooks you open on your computer.

As I mentioned in the video above, this is a question from my friend Brett. Excel on his home computer is in manual calculation mode and his file is not calculated. When he opens the same files on his work computer, they calculate just fine because Excel on that computer is in automatic mode.

However, they existThere is a basic nuance hereThe .work workbook (Excel file) also stores the last saved calculation settings and can change/undo settings at the application level.

This should only happen insideThe first file was opened during an Excel session.

For example, if you switch Excel to manual calculation mode before saving and closing the file, the setting will be saved with the workbook. If you then open the workbook as the first workbook in an Excel session, the calculation mode will change to manual.

Any subsequent workbooks you open during this session will also be in manual calculation mode. If these files are saved and closed, the manual calculation function will also be saved with the files.

thisThe confusing part of this behaviorYes, it only happens for the first file you open in a session. After you completely close the Excel application and then reopen it, Excel will return to automatic calculation mode if you first open a new blank file or any file that was in automatic calculation mode.

Therefore, the calculation method of the first file you open in an Excel session determines the calculation method of all files opened in that session. If you change the calculation method in one file, all open files will be changed.

notes:I made a mistake in the video when I said that the calculation settings do not move with the workbook, I will update the video.

3 calculation options

There are three calculation options in Excel.

Automatic calculationIndicates that when a cell value or formula changes, Excel will recalculate all related formulas.

manual calculationmeans that Excel will only recalculate if you force a recalculation. This can be done by pressing a button or a keyboard shortcut. You can also recalculate individual cells by editing the cell and pressing Enter.

Automatic, except for data sheetsmeans that Excel will automatically recalculate all cells except those used in the data table. This isYesreference normalExcel TablicaYou will probably work a lot with it. This is a situational analysis tool that not many people use. Are youdatacard, uHypothetical scenariobutton. Therefore, if you are not using these datasheets, it is unlikely that you will intentionally change the setting in this option.

except indatacard, you can also useExcel optionsMenu. to godocument, Afterwardchoices, AfterwardofficiallySee the same setting options atExcel Options window.

lobe, belowhandbookoption, you will see a check boxRecalculate the workbook before saving,This is the default setting. This is good because you want the data to be calculated correctly before you save the file and share it with colleagues.

Why use the manual calculation method?

If you are wondering why someone would want to change the calculationautomaticreachhandbook, for one key reason. when you use itLarge files with slow calculations, the constant recalculation every time a change is made can sometimes slow down your system. well people sometimesswitch to manual modeWhen it comes to changes in data-heavy sheets, thenturn back.

When in manual calculation mode, you canforce calculationuse anytimedo the math nowactive buttonofficiallyLet me underline.

keyboard shortcutsdo the math nowAndF9, he reckonsthe entire workbook.if you just want to countcurrent sheet, you can select the following button:calculator.The keyboard shortcut for this option isTRANSPORT+F9.

Here is a list of all recalculated keyboard shortcuts:

The macro has been switched to manual calculation mode

If you find that your workbook is not automatically calculated, but you did not intentionally change the mode, another possible reason for the change isbecause of the macro.

now i want to sayNot all macros cause the problem.This is a specific line of code that a programmer can use to make a macro run faster.

The next line of VBA codeTell Excel to switch to manual calculation mode.

Application.Calculation = xlCalculationManual

Macro writers sometimes add this line at the beginning so that Excel doesn't try to calculate while the macro is running. The setting must then be changed again at the end of the macro using the following line.

Application.Calculation = xlCalculationAutomatic

This technique works well for large workbooks where calculations are slow.

However, a problem ariseswhen the macro does not complete— It may be due to a bug, program error, or unexpected system problem. The macro changes the setting to "Manual" and does not change it again.

As I mentioned in the video, this is exactly what happened to my friend Brett without his knowledge. He is in manual calculation mode and doesn't know why or how to get Excel to recalculate.

So if you use this technique in your macros, I encourage you to consider ways to mitigate this problem. andNotify your users that Excel may be in manual calculation mode.

I also recommend not changing the Calculate property in code unless you absolutely have to. This will help prevent frustration and mistakes from your macro users.

in conclusion

I hope this information is helpful, especially if you are currently experiencing this issue. if you have themquestion or commentFor calculation methods, seeComment.