How to use Calculation Options in Excel (3 ways)

In this article, you will learn how to use Calculation Options in Excel step by step.

Table of Contents:

What is Calculation Option in Excel?

The Calculation Option in Excel determines the timing and method used to update formulas. It controls whether Excel recalculates formulas automatically after every change or waits for a manual trigger. This setting helps manage performance, especially in large or complex workbooks.

How to Change Calculation Options in Excel?

📑 Step 1: Open the Formulas Tab:

Go to the Formulas tab located on the Ribbon at the top of Excel.

⚙️ Step 2: Access Calculation Options:

In the Calculation group, click on Calculation Options to view available settings.

✅ Step 3: Choose a Calculation Mode:

There are 3 different types of Calculation Options in Excel. You can select any one of the following options:

• • Automatic,
  • Partial (Automatic Except for Data Tables), or
  • Manual.

Automatic Calculation Option in Excel

🔍 What It Does:

• In Automatic calculation option, Excel recalculates all the formulas instantly whenever you make a small change in the worksheet.
• For example, if you change a number in a cell, the Excel will immediately update all formulas which are depended on that cell.
• This is the default setting in Microsoft Excel.

✅ Advantages:

• Formulas are always up to date because Excel recalculates automatically.

• This mode is very convenient for regular spreadsheet tasks where real-time updates are needed.

• It works best for small to medium-sized workbooks with standard formulas.

• It is ideal for users who prefer quick results without needing to press any keys.

❌ Disadvantages:

• The Excel can become slow if the file has:
  • Lots of formulas,
  • Large data, or
  • Volatile functions like NOW, RAND, OFFSET etc.
• The Excel may lag, since it recalculates after every little change.

Partial Calculation Option in Excel

🔍 What It Does:

• In Excel, the Partial calculation option recalculates all formulas automatically, except for data tables.
• The data tables (used in What-If Analysis) must be updated manually using the F9 key or menu options.
• This is also called as Automatic Except for Data Tables in Excel.

✅ Advantages:

• It helps improve performance when your workbook includes large or multiple data tables.

• All regular formulas still update automatically without delay.

• This mode is best for workbooks that include What-If Analysis or financial models.

• It is suitable for users who want speed improvements without losing automatic updates for most formulas.

❌ Disadvantages:

• The data tables can show outdated values if you forget to manually recalculate them.

• Relying on old data from data tables may lead to incorrect conclusions.

Manual Calculation Option in Excel

🔍 What It Does:

• In Manual calculation option, the Excel does not update formulas automatically.
• You must select the manual option or use the keyboard shortcut keys to manually trigger recalculation when needed.

🔑 Helpful Shortcuts:

• F9: Recalculates all open workbooks in Excel.
• Shift + F9: Recalculates the active worksheet only in Excel.
• Ctrl + Alt + F9: Recalculates all formulas, even if they have not changed in Excel.
• Ctrl + Shift + Alt + F9: Forces Excel to rebuild all formulas and dependencies, then recalculate everything.

✅ Advantages:

• The manual calculation option gives you full control over when formulas are updated.
• It significantly boosts performance in large, complex workbooks by avoiding constant recalculations.
• It is best for experienced users working on advanced models, dashboards, or large data sets.
• It allows you to make multiple edits before triggering recalculation, which saves time in heavy workbooks.

❌ Disadvantages:

• It is easy to forget to recalculate, which can result in outdated or incorrect formula results.
• It is not recommended for beginners or users unfamiliar with manual calculation features.

Tips for using Calculation Options in Excel

⚡ Volatile Functions:

• Some formulas always recalculate, even if nothing changes.
• For example: You should use the functions like NOW, TODAY, RAND, OFFSET, INDIRECT, etc. carefully in large workbooks.

🔁 Data Tables:

• Data tables don’t update automatically in Partial or Manual modes.
• Press F9 to update them if needed.

⚠️ Circular References:

• If a formula refers to itself (directly or indirectly), Excel may give a warning.
• This can affect calculations and you need to fix the error.

Shortcut Keys

Below are the keyboard shortcut keys to use Calculation Options in Excel:

Conclusion

The calculation options in Excel help you to control when formulas are updated. You can choose Automatic to update formulas right away, or Manual if you want to decide when to calculate. There is also an option to skip data tables. Using the right calculation option can make the Excel run faster and help you work more smoothly, especially with large files.

Learn More