Excel is mostly used for doing calculations, and one of its features is the ability to calculate numbers using different calculation modes.
These modes tell Excel how and when to update the numbers as you make changes.
You have four calculation settings:
- Automatic Calculation Mode
- Manual Calculation Mode
- Partial Calculation Mode
- Format Stale Values Calculation Mode
Automatic Calculation Mode
In automatic calculation mode, Excel immediately recalculates the results of formulas when you change any of the cells they depend on. This ensures that the data you see is always up to date based on the latest input.
Automatic Calculation is typically the default setting in Excel. It’s chosen because it guarantees that the results of formulas are always current, which is usually the desired behavior in spreadsheet work.
Automatic calculation is ideal for situations where real-time data updates are important, such as in dashboards or when working with data that changes frequently.
For example, if I’m creating a dashboard and I want to update the entire dashboard based on the value in one cell (say a drop-down list), Then having automatic calculation mode enabled would be useful.
This mode is useful when working with relatively small datasets or less complex spreadsheets, where the recalculation time is minimal and won’t noticeably slow down performance.
If performance becomes an issue, users might switch to ‘Manual Calculation Mode’.
In this mode, Excel recalculates formulas only when you explicitly request it (by pressing F9, for example).
This can significantly improve performance with large files but requires the user to remember to manually recalculate for up-to-date results.
How to Enable Automatic Calculation Mode in Excel?
If automatic calculation mode is not currently activated on your system, you can follow these steps to enable it:
- Go to the ‘Formula’ tab
- Go to the ‘Calculation’ group and expand the ‘Calculation Options’
- Select ‘Automatic’
Partial Calculation Mode (Earlier called Automatic Except for Data Tables)
In Excel, ‘Partial Calculation Mode’, previously known as ‘Automatic Except for Data Tables’, is a specific setting for formula calculation.
This calculation mode is a variation of the standard Automatic Calculation Mode, with a key difference in how it treats data tables. Here’s a breakdown of what this mode entails:
The unique aspect of Partial Calculation Mode is how it handles data tables.
Data tables, often used for what-if analysis, can be quite resource-intensive to recalculate.
In this mode, Excel does not automatically recalculate data tables when other cells change. This approach can significantly improve performance, especially in complex workbooks where data tables are extensive and calculations are heavy.
In this mode, data tables are only recalculated when you manually trigger a full recalculation (for example, by pressing F9).
This gives you control over when these potentially time-consuming calculations occur, which is beneficial for maintaining workbook performance.
How to Enable Partial Calculation Mode in Excel?
To enable Partial calculation mode in Excel, follow the below steps:
- Go to the ‘Formula’ tab
- Go to the ‘Calculation’ group and expand the ‘Calculation Options’
- Select ‘Automatic Except for Data Tables’. If you’re using a newer version of Excel, it may say Partial instead of Automatic Except for Data Tables
If you choose this option, Data Tables will recalculate only when you make a change to the Data Table itself (or you force recalculation in the entire sheet).
Manual Calculation Mode
In Manual Calculation Mode, Excel does not automatically update the results of formulas when you make changes in your workbook. Instead, formulas are recalculated only when you manually trigger the process.
This mode can significantly improve performance in large, complex workbooks with numerous formulas, data tables, or volatile functions (like RAND or NOW).
By avoiding constant recalculations, it reduces the computational load, which is particularly beneficial when making multiple changes or data entries.
Manual Calculation Mode is useful when working on extensive data modeling, complex financial spreadsheets, or scenarios where real-time updates are not necessary. It allows for batch processing of updates, which can be more efficient in certain contexts.
You can recalculate your workbook in several ways while in Manual Mode:
- Pressing F9 recalculates all open workbooks.
- Pressing Shift + F9 recalculates only the active worksheet.
- Pressing Ctrl + Alt + F9 forces a complete recalculation of all formulas in all open workbooks, regardless of whether Excel thinks they need recalculation or not.
A critical aspect of using Manual Calculation Mode is the need to be mindful of recalculating your data to ensure accuracy. Since Excel won’t update formulas automatically, you need to remember to manually trigger a recalculation to reflect the most current data.
How to Enable Manual Calculation Mode in Excel?
To enable Manual calculation mode in Excel, follow the below steps:
- Go to the ‘Formula’ tab
- Go to the ‘Calculation’ section and expand the ‘Calculation Options’
- Select ‘Manual’.
In Manual Mode, Excel recalculates all formulas only when you explicitly instruct it to do so.
Disabling Manual Calculation Mode Before Saving
Even if you’ve chosen ‘Manual’ mode, you will notice that your workbook recalculates every time you save it.
To avoid this, you need to follow these steps.
- Go to the File menu.
- Click “Options”.
- Click ‘Formulas’ in the Excel Options dialog box.
- Select ‘Manual’ and uncheck the box ‘Recalculate workbook before saving’ option.
Recalculate Options in Excel
In case you’re using Manual calculation mode, there are two options Excel gives you to manually recalculate:
- The active sheet
- The entire workbook
Recalculate Only the Active Sheet
- Go to the ‘Formula’ tab
- Go to the ‘Calculation’ group and click ‘Calculate Sheet’
You can also use the Shift + F9 keyboard shortcut to recalculate only the active sheet.
Recalculate the Entire Workbook
- Go to the ‘Formula’ tab
- Go to the ‘Calculation’ group and click ‘Calculate Now’
You can also use the F9 keyboard shortcut to recalculate only the active sheet. If function keys are not enabled, you have to press Fn + F9.
Recalculate Workbook Before Saving
Even if you like manual calculation mode, you may want the workbook to recalculate automatically before saving.
Below are the steps to enable this:
- Go to the File menu.
- Click ‘Options’.
- Click ‘Formulas’ in the Excel Options dialog box.
- Select ‘Manual’ and check the box ‘Recalculate workbook before saving’ option.
Also read: Array Formulas in Excel
Format Stale Values Calculation Mode
The Format Stale Values feature in Microsoft Excel is designed to help users identify when cells contain outdated information due to the workbook being set to manual calculation mode.
In manual calculation mode, Excel does not automatically update cell values when their dependent inputs change, which can lead to a situation where the displayed values are no longer correct based on the current data or formulas.
This feature visually indicates which values are ‘stale’ or potentially incorrect because they have not been recalculated (by striking them off with a strikethrough).
How Excel Calculation Mode Works?
It’s interesting to note that all open Excel workbooks use the same calculation mode, regardless of how you saved each file.
Let’s understand how this works with the example below.
Below, I have three Excel workbooks in a folder.
Automatic 1.xlsx and Automatic 2.xlsx workbooks are saved with Automatic calculation mode active, whereas Manual.xlsx Excel Workbook is saved in Manual calculation mode.
Now, let’s see how calculation mode works when you have multiple open workbooks with different types of calculation modes.
- When you change the calculation mode of one workbook, it applies to all the open workbooks.
- Assume that you have opened Automatic 1 and Automatic 2 both Excel workbooks. Then, you change the calculation mode of the Automatic 1 Excel workbook to ‘Manual’. Now, Excel has changed the calculation mode of the Automatic 2 Excel workbook also to ‘Manual’.
- When you open multiple Excel workbooks, the calculation mode of all workbooks depends on the first open Excel workbook, and the calculation mode of the first book is dependent on the calculation mode it is saved in.
- For example, if you first open the Manual Excel workbook and then open the Automatic 1 Excel workbook, the calculation mode of the Automatic 1 Excel workbook will be changed to ‘Manual’.
- When you create a new Excel workbook when there are no open workbooks, the new workbook uses the same calculation mode that was used for the last closed workbook.
- For example, if you create a new Excel workbook and your last saved workbook is Automatic 2, the new Excel workbook uses Automatic calculation mode.
Other articles you may also like:
