Change formula recalculation, iteration, or precision in Excel

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.

1. Click the File tab, click 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.

1. Click the File tab, click 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.

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.

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

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.

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.

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).

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.