Omgest

I'm new to Excel 2007. I'm familiar to change between Automatic and Manual calculation by Tool - Option, and on Calculation tab I can select Automatic or Manual calculation. I can't find 'Tool' in Excel 2007? Now I have to press F9 to trigger the calculation all the time. Pls advise how to change to Automatic calculation.

Applies to: Excel 2013 | Office 2013 | Visual Studio

The user can trigger recalculation in Microsoft Excel in several ways, for example:

• Entering new data (if Excel is in Automatic recalculation mode, described later in this topic).

• Explicitly instructing Excel to recalculate all or part of a workbook.

• Deleting or inserting a row or column.

• Saving a workbook while the Recalculate before save option is set.

• Performing certain Autofilter actions.

• Double-clicking a row or column divider (in Automatic calculation mode).

• Adding, editing, or deleting a defined name.

• Renaming a worksheet.

• Changing the position of a worksheet in relation to other worksheets.

• Hiding or unhiding rows, but not columns.

Note

This topic does not distinguish between the user directly pressing a key or clicking the mouse, and those tasks being done by a command or macro. The user runs the command, or does something to cause the command to run so that it is still considered a user action. Therefore the phrase 'the user' also means 'the user, or a command or process started by the user.'

Dependence, Dirty Cells, and Recalculated Cells

The calculation of worksheets in Excel can be viewed as a three-stage process:

1. Construction of a dependency tree

2. Construction of a calculation chain

3. Recalculation of cells

The dependency tree informs Excel about which cells depend on which others, or equivalently, which cells are precedents for which others. From this tree, Excel constructs a calculation chain. The calculation chain lists all the cells that contain formulas in the order in which they should be calculated. During recalculation, Excel revises this chain if it comes across a formula that depends on a cell that has not yet been calculated. In this case, the cell that is being calculated and its dependents are moved down the chain. For this reason, calculation times can often improve in a worksheet that has just been opened in the first few calculation cycles.

When a structural change is made to a workbook, for example, when a new formula is entered, Excel reconstructs the dependency tree and calculation chain. When new data or new formulas are entered, Excel marks all the cells that depend on that new data as needing recalculation. Cells that are marked in this way are known as dirty . All direct and indirect dependents are marked as dirty so that if B1 depends on A1, and C1 depends on B1, when A1 is changed, both B1 and C1 are marked as dirty.

If a cell depends, directly or indirectly, on itself, Excel detects the circular reference and warns the user. This is usually an error condition that the user must fix, and Excel provides very helpful graphical and navigational tools to help the user to find the source of the circular dependency. In some cases, you might deliberately want this condition to exist. For example, you might want to run an iterative calculation where the starting point for the next iteration is the result of the previous iteration. Excel supports control of iterative calculations through the calculation options dialog box.

After marking cells as dirty, when a recalculation is next done, Excel reevaluates the contents of each dirty cell in the order dictated by the calculation chain. In the example given earlier, this means B1 is first, and then C1. This recalculation occurs immediately after Excel finishes marking cells as dirty if the recalculation mode is automatic; otherwise, it occurs later.

Starting in Microsoft Excel 2002, the Range object in Microsoft Visual Basic for Applications (VBA) supports a method, Range.Dirty, which marks cells as needing calculation. When it is used together with the Range.Calculate method (see next section), it enables forced recalculation of cells in a given range. This is useful when you are performing a limited calculation during a macro, where the calculation mode is set to manual, to avoid the overhead of calculating cells unrelated to the macro function. Range calculation methods are not available through the C API.

In Excel 2002 and earlier versions, Excel built a calculation chain for each worksheet in each open workbook. This resulted in some complexity in the way links between worksheets were handled, and required some care to ensure efficient recalculation. In particular, in Excel 2000, you should minimize cross-worksheet dependencies and name worksheets in alphabetical order so that sheets that depend on other sheets come alphabetically after the sheets they depend on.

In Excel 2007, the logic was improved to enable recalculation on multiple threads so that sections of the calculation chain are not interdependent and can be calculated at the same time. You can configure Excel to use multiple threads on a single processor computer, or a single thread on a multi-processor or multi-core computer.

Asynchronous User Defined Functions (UDFs)

When a calculation encounters an asynchronous UDF, it saves the state of the current formula, starts the UDF and continues evaluating the rest of the cells. When the calculation finishes evaluating the cells Excel waits for the asynchronous functions to complete if there are still asynchronous functions running. As each asynchronous function reports results, Excel finishes the formula, and then runs a new calculation pass to re-compute cells that use the cell with the reference to the asynchronous function.

Volatile and Non-Volatile Functions

Excel supports the concept of a volatile function, that is, one whose value cannot be assumed to be the same from one moment to the next even if none of its arguments (if it takes any) has changed. Excel reevaluates cells that contain volatile functions, together with all dependents, every time that it recalculates. For this reason, too much reliance on volatile functions can make recalculation times slow. Use them sparingly.

The following Excel functions are volatile:

• NOW

• TODAY

• RANDBETWEEN

• OFFSET

• INDIRECT

• INFO (depending on its arguments)

• CELL (depending on its arguments)

• SUMIF (depending on its arguments)

Both the VBA and C API support ways to inform Excel that a user-defined function (UDF) should be handled as volatile. By using VBA, the UDF is declared as volatile as follows.

By default, Excel assumes that VBA UDFs are not volatile. Excel only learns that a UDF is volatile when it first calls it. A volatile UDF can be changed back to non-volatile as in this example.

Using the C API, you can register an XLL function as volatile before its first call. It also enables you to switch on and off the volatile status of a worksheet function.

By default, Excel handles XLL UDFs that take range arguments and that are declared as macro-sheet equivalents as volatile. You can turn this default state off using the xlfVolatile function when the UDF is first called.

Calculation Modes, Commands, Selective Recalculation, and Data Tables

Excel has three calculation modes:

• Automatic

• Automatic Except Tables

• Manual

When calculation is set to automatic, recalculation occurs after every data input and after certain events such as the examples given in the previous section. For very large workbooks, recalculation time might be so long that users must limit when this happens, that is, only recalculating when they need to. To enable this, Excel supports the manual mode. The user can select the mode through the Excel menu system, or programmatically using VBA, COM, or the C API.

Data tables are special structures in a worksheet. First, the user sets up the calculation of a result on a worksheet. This depends on one or two key changeable inputs and other parameters. The user can then create a table of results for a set of values for one or both of the key inputs. The table is created by using the Data Table Wizard. After the table is set up, Excel plugs the inputs one-by-one into the calculation and copies the resulting value into the table. As one or two inputs can be used, data tables can be one- or two-dimensional.

Recalculation of data tables is handled slightly differently:

• Recalculation is handled asynchronously to regular workbook recalculation so that large tables might take longer to recalculate than the rest of the workbook.

• Circular references are tolerated. If the calculation that is used to get the result depends on one or more values from the data table, Excel does not return an error for the circular dependency.

• Data tables do not use multi-threaded calculation.

Given the different way that Excel handles recalculation of data tables, and the fact that large tables that depend on complex or lengthy calculations can take a long time to calculate, Excel lets you disable the automatic calculation of data tables. To do this, set the calculation mode to Automatic except Data Tables. When calculation is in this mode, the user recalculates the data tables by pressing F9 or some equivalent programmatic operation.

Excel exposes methods through which you can alter the recalculation mode and control recalculation. These methods have been improved from version to version to allow for finer control. The capabilities of the C API in this regard reflect those that were available in Excel version 5, and so do not give you the same control that you have using VBA in more recent versions.

Most frequently used when Excel is in manual calculation mode, these methods allow selective calculation of workbooks, worksheets, and ranges, complete recalculation of all open workbooks, and even complete rebuild of the dependency tree and calculation chain.

Range Calculation

Keystroke: None

VBA: Range.Calculate (introduced in Excel 2000, changed in Excel 2007) and Range.CalculateRowMajorOrder (introduced in Excel 2007)

C API: Not supported

• Manual mode

  Recalculates just the cells in the given range regardless of whether they are dirty or not. Behavior of the Range.Calculate method changed in Excel 2007; however, the old behavior is still supported by the Range.CalculateRowMajorOrder method.

• Automatic or Automatic Except Tables modes

  Recalculates the workbook but does not force recalculation of the range or any cells in the range.

Active Worksheet Calculation

Keystroke: SHIFT+F9

VBA: ActiveSheet.Calculate

C API: xlcCalculateDocument

• All modes

  Recalculates the cells marked for calculation in the active worksheet only.

Specified Worksheet Calculation

Keystroke: None

VBA: **Worksheets(**reference ).Calculate

C API: Not supported

• All modes

  Recalculates the dirty cells and their dependents within the specified worksheet only. Reference is the name of the worksheet as a string or the index number in the relevant workbook.

  Excel 2000 and later versions expose a Boolean worksheet property, the EnableCalculation property. Setting this to True from False dirties all cells in the specified worksheet. In automatic modes, this also triggers a recalculation of the whole workbook.

  In manual mode, the following code causes recalculation of the active sheet only.

Workbook Tree Rebuild and Forced Recalculation

Mac Excel Manual Calculation F9

Keystroke: CTRL+ALT+SHIFT+F9 (introduced in Excel 2002)

VBA: **Workbooks(**reference ).ForceFullCalculation (introduced in Excel 2007)

C API: Not supported

• All modes

  Causes Excel to rebuild the dependency tree and the calculation chain for a given workbook and forces a recalculation of all cells that contain formulas.

All Open Workbooks

Keystroke: F9

VBA: Application.Calculate

C API: xlcCalculateNow

• All modes

  Recalculates all cells that Excel has marked as dirty, that is, dependents of volatile or changed data, and cells programmatically marked as dirty. If the calculation mode is Automatic Except Tables, this calculates those tables that require updating and also all volatile functions and their dependents.

All Open Workbooks Tree Rebuild and Forced Calculation

Keystroke: CTRL+ALT+F9

VBA: Application.CalculateFull

C API: Not supported

• All modes

  Recalculates all cells in all open workbooks. If the calculation mode is Automatic Except Tables, it forces the tables to be recalculated.

See also

To use formulas efficiently, there are three important considerations that you need to understand:

Calculation is the process of computing formulas and then displaying the results as values in the cells that contain the formulas. To avoid unnecessary calculations that can waste your time and slow down your computer, Microsoft Excel automatically recalculates formulas only when the cells that the formula depends on have changed. This is the default behavior when you first open a workbook and when you are editing a workbook. However, you can control when and how Excel recalculates formulas.

Iteration is the repeated recalculation of a worksheet until a specific numeric condition is met. Excel cannot automatically calculate a formula that refers to the cell — either directly or indirectly — that contains the formula. This is called a circular reference. If a formula refers back to one of its own cells, you must determine how many times the formula should recalculate. Circular references can iterate indefinitely. However, you can control the maximum number of iterations and the amount of acceptable change.

Precision is a measure of the degree of accuracy for a calculation. Excel stores and calculates with 15 significant digits of precision. However, you can change the precision of calculations so that Excel uses the displayed value instead of the stored value when it recalculates formulas.

Change when a worksheet or workbook recalculates

As calculation proceeds, you can choose commands or perform actions such as entering numbers or formulas. Excel temporarily interrupts calculation to carry out the other commands or actions and then resumes calculation. The calculation process may take more time if the workbook contains a large number of formulas, or if the worksheets contain data tables or functions that automatically recalculate every time the workbook is recalculated. Also, the calculation process may take more time if the worksheets contain links to other worksheets or workbooks. You can control when calculation occurs by changing the calculation process to manual calculation.

Important: Changing any of the options affects all open workbooks.

1. Click the File tab, click Options, and then click the Formulas category.

   In Excel 2007, click the Microsoft Office Button, click Excel Options, and then click the Formulas category.

2. Do one of the following:

   • To recalculate all dependent formulas every time you make a change to a value, formula, or name, in the Calculation options section, under Workbook Calculation, click Automatic. This is the default calculation setting.

   • To recalculate all dependent formulas — except data tables — every time you make a change to a value, formula, or name, in the Calculation options section, under Workbook Calculation, click Automatic except for data tables.

   • To turn off automatic recalculation and recalculate open workbooks only when you explicitly do so (by pressing F9), in the Calculation options section, under Workbook Calculation, click Manual.

     Note: When you click Manual, Excel automatically selects the Recalculate workbook before saving check box. If saving a workbook takes a long time, clearing the Recalculate workbook before saving check box may improve the save time.

   • To manually recalculate all open worksheets, including data tables, and update all open chart sheets, on the Formulas tab, in the Calculation group, click the Calculate Now button.

   • To manually recalculate the active worksheet and any charts and chart sheets linked to this worksheet, on the Formulas tab, in the Calculation group, click the Calculate Sheet button.

Tip: Alternatively, you can change many of these options outside of the Excel Options dialog box. On the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic.

Note: If a worksheet contains a formula that is linked to a worksheet that has not been recalculated and you update that link, Excel displays a message stating that the source worksheet is not completely recalculated. To update the link with the current value stored on the source worksheet, even though the value might not be correct, click OK. To cancel updating the link and use the previous value obtained from the source worksheet, click Cancel.

Recalculate a worksheet or workbook manually by using keyboard shortcuts

Change the number of times Excel iterates a formula

1. Click the File tab, click Options, and then click the Formulas category.

   In Excel 2007, click the Microsoft Office Button, click Excel Options, and then click the Formulas category.

2. In the Calculation options section, select the Enable iterative calculation check box.

3. To set the maximum number of times Excel will recalculate, type the number of iterations in the Maximum Iterations box. The higher the number of iterations, the more time Excel will need to recalculate a worksheet.

4. To set the maximum amount of change you will accept between recalculation results, type the amount in the Maximum Change box. The smaller the number, the more accurate the result and the more time Excel needs to recalculate a worksheet.

Note: Solver and Goal Seek are part of a suite of commands sometimes called what-if analysis tools. Both commands use iteration in a controlled way to obtain desired results. You can use Solver when you need to find the optimum value for a particular cell by adjusting the values of several cells or when you want to apply specific limitations to one or more of the values in the calculation. You can use Goal Seek when you know the desired result of a single formula but not the input value the formula needs to determine the result.

Change the precision of calculations in a workbook

Before you change the precision of calculations, keep in mind the following important points:

By default, Excel calculates stored, not displayed, values

The displayed and printed value depends on how you choose to format and display the stored value. For example, a cell that displays a date as '6/22/2008' also contains a serial number that is the stored value for the date in the cell. You can change the display of the date to another format (for example, to '22-Jun-2008'), but changing the display of a value on a worksheet does not change the stored value.

Use caution when changing the precision of calculations

When a formula performs calculations, Excel usually uses the values stored in cells referenced by the formula. For example, if two cells each contain the value 10.005 and the cells are formatted to display values in currency format, the value $10.01 is displayed in each cell. If you add the two cells together, the result is $20.01 because Excel adds the stored values 10.005 and 10.005, not the displayed values.

When you change the precision of the calculations in a workbook by using the displayed (formatted) values, Excel permanently changes stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed. If you later choose to calculate with full precision, the original underlying values cannot be restored.

1. Click the File tab, click Options, and then click the Advanced category.

   In Excel 2007, click the Microsoft Office Button, click Excel Options, and then click the Advanced category

2. In the When calculating this workbook section, select the workbook you want and then select the Set precision as displayed check box.

Learn more about precision in Excel

Mac Excel Manual Calculation Pdf

Although Excel limits precision to 15 digits, that doesn't mean that 15 digits is the limit of the size of a number you can store in Excel. The limit is 9.99999999999999E+307 for positive numbers, and -9.99999999999999E+307 for negative numbers . This is approximately the same as 1 or -1 followed by 308 zeros.

Precision in Excel means that any number exceeding 15 digits is stored and shown with only 15 digits of precision. Those digits can be in any combination before or after the decimal point. Any digits to the right of the 15th digit will be zeros. For example, 1234567.890123456 has 16 digits (7 digits before and 9 digits after the decimal point). In Excel, it's stored and shown as 1234567.89012345 (this is shown in the formula bar and in the cell). If you set the cell to a number format so that all digits are shown (instead of a scientific format, such as 1.23457E+06), you'll see that the number is displayed as 1234567.890123450. The 6 at the end (the 16th digit) is dropped and replaced by a 0. The precision stops at the 15th digit, so any following digits are zeros.

Change the number of processors used to calculate formulas

A computer can have more than one processor (it contains multiple physical processors) or can be hyperthreaded (it contains multiple logical processors). On these computers, you can improve or control the time it takes to recalculate workbooks that contain many formulas by setting the number of processors to use for recalculation. In many cases, portions of a recalculation workload can be performed simultaneously. Splitting this workload across multiple processors can reduce the overall time it takes complete the recalculation.

1. Click the File tab, click Options, and then click the Advanced category.

   In Excel 2007, click the Microsoft Office Button, click Excel Options, and then click the Advanced category.

2. To enable or disable the use of multiple processors during calculation, in the Formulas section, select or clear the Enable multi-threaded calculation check box.

   Note This check box is enabled by default, and all processors are used during calculation. The number of processors on your computer is automatically detected and displayed next to the Use all processors on this computer option.

3. Optionally, if you select Enable multi-threaded calculation, you can control the number of processors to use on your computer. For example, you might want to limit the number of processors used during recalculation if you have other programs running on your computer that require dedicated processing time.

4. To control the number of processors, under Number of calculation threads, click Manual. Enter the number of processors to use (the maximum number is 1024).

Learn about calculating workbooks that were created in an earlier version of Excel

To ensure that older workbooks are calculated correctly, Excel behaves differently when you first open a workbook saved in an earlier version of Excel than when you open a workbook created in the current version.

• When you open a workbook created in the current version, Excel recalculates only the formulas that depend on cells that have changed.

• When you use open a workbook that was created in an earlier version of Excel, all the formulas in the workbook — those that depend on cells that have changed and those that do not — are recalculated. This ensures that the workbook is fully optimized for the current Excel version. The exception is when the workbook is in a different calculation mode, such as Manual.

• Because complete recalculation can take longer than partial recalculation, opening a workbook that was not previously saved in the current Excel version can take longer than usual. After you save the workbook in the current version of Excel, it will open faster.

In Excel for the web, a formula result is automatically recalculated when you change data in cells that are used in that formula. You can turn this automatic recalculation off and calculate formula results manually. Here's how to do it:

Note: Changing the calculation option in a workbook will affect the current workbook only, and not any other open workbooks in the browser.

1. In the Excel for the web spreadsheet, click the Formulas tab.

2. Next to Calculation Options, select one of the following options in the dropdown:

   • To recalculate all dependent formulas every time you make a change to a value, formula, or name, click Automatic. This is the default setting.

   • To recalculate all dependent formulas — except data tables — every time you make a change to a value, formula, or name, click Automatic Except for Data Tables.

   • To turn off automatic recalculation and recalculate open workbooks only when you explicitly do so, click Manual.

   • To manually recalculate the workbook (including data tables), click Calculate Workbook.

Note: In Excel for the web, you can’t change the number of times a formula is recalculated until a specific numeric condition is met, nor can you change the precision of calculations by using the displayed value instead of the stored value when formulas are recalculated. You can do that in the Excel desktop application though. Use the Open in Excel button to open your workbook to specify calculation options and change formula recalculation, iteration, or precision.

Basic Excel Calculations

Need more help?

Mac Excel Manual Calculation

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.