Change formula recalculation, iteration, or precision in Excel (2023)

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.

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.

      (Video) Iterative Solutions/Excel

      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.

      Change formula recalculation, iteration, or precision in Excel (1)

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

To

Press

Recalculate formulas that have changed since the last calculation, and formulas dependent on them, in all open workbooks. If a workbook is set for automatic recalculation, you do not need to press F9 for recalculation.

F9

Recalculate formulas that have changed since the last calculation, and formulas dependent on them, in the active worksheet.

Shift+F9

Recalculate all formulas in all open workbooks, regardless of whether they have changed since the last recalculation.

Ctrl+Alt+F9

Check dependent formulas, and then recalculate all formulas in all open workbooks, regardless of whether they have changed since the last recalculation.

Ctrl+Shift+Alt+F9

  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.

    (Video) Excel Calculate Option: How to Recalculate Your Excel Formulas Manually Or Automatically

  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.

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.

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.

(Video) A "Dangerous" Excel Setting - Set Precision as displayed

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.

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

(Video) 24.Excel Formulas Iterations

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:

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.

(Video) How To Modify The Calculation Options in a Microsoft Excel Workbook?

FAQs

How do I turn off recalculation in Excel? ›

If you are already in an Excel Workbook, in the Ribbon, select File > Options.
  1. Now, select Formulas and go to the Calculation options section. Here you can find the “Workbook calculation” settings.
  2. Select Manual.
  3. Then click OK.

How do you make a formula more precise in Excel? ›

Click File > Options. , and then click Excel Options. Click Advanced, and then under When calculating this workbook, select the Set precision as displayed check box, and then click OK. Click OK.

How do I enable iterative calculations in Excel permanently? ›

To turn on Excel iterative calculation, do one of the following: In Excel 2016, Excel 2013, and Excel 2010, go to File > Options > Formulas, and select the Enable iterative calculation check box under the Calculation options. In Excel 2007, click Office button> Excel options > Formulas > Iteration area.

What does iterative calculation mean in Excel? ›

Iterative calculations are repeated calculations until a specific numeric condition is met. Iterative calculations help Excel find the solution to formulas by performing the same calculation repeatedly using previous results. By analyzing the previous results, Excel can find the likelihood of possible solutions.

How is the automatic recalculation turned off when using random functions? ›

Select Tools ? Options, check the Iteration box, and press OK. In column A, next to your original formula, enter either TRUE to enable automatic recalculation or FALSE to disable it.

How do I turn off auto increment in spreadsheet? ›

Turn automatic completion of cell entries on or off
  1. Click File > Options.
  2. Click Advanced, and then under Editing options, select or clear the Enable AutoComplete for cell values check box to turn this option on or off.

How do I make data more precise? ›

How to Improve Data Accuracy?
  1. Inaccurate Data Sources. Companies should identify the right data sources, both internally and externally, to improve the quality of incoming data. ...
  2. Set Data Quality Goals. ...
  3. Avoid Overloading. ...
  4. Review the Data. ...
  5. Automate Error Reports. ...
  6. Adopt Accuracy Standards. ...
  7. Have a Good Work Environment.

How do you use iterations in Excel? ›

In the Calculation options section, select the Enable iterative calculation check box. 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.

How do you make a cell formula permanent? ›

Here are the steps to Lock Cells with Formulas:
  1. With the cells with formulas selected, press Control + 1 (hold the Control key and then press 1).
  2. In the format cells dialog box, select the Protection tab.
  3. Check the 'Locked' option.
  4. Click ok.

How do you fix automatic Formulas in Excel? ›

Check for Automatic Recalculation

On the Formulas ribbon, look to the far right and click Calculation Options. On the dropdown list, verify that Automatic is selected. When this option is set to automatic, Excel recalculates the spreadsheet's formulas whenever you change a cell value.

What is an example of a iteration? ›

Iteration is when the same procedure is repeated multiple times. Some examples were long division, the Fibonacci numbers, prime numbers, and the calculator game.

What is an iteration formula? ›

Iteration is a numerical method used to find an approximation to a root (solution) of an equation y=f(x) where f(x)=0. The method discussed here is often known as fixed point iteration.

What is an example of an iterative process? ›

In many areas of society an iterative process is regularly employed. For example, in politics, many different campaign formulas are tried out for a candidate to arrive to the most effective one; or a government might employ different social programs until the need they are aiming at is fulfilled.

How do I get Excel to stop calculating 4 threads? ›

On the Excel Options dialog box, click Formulas in the menu on the left. Scroll down to the Calculation options section and select Manual to prevent the formulas from being calculated every time you make a change to a value, formula, or name or open a worksheet containing formulas.

How do I force a spreadsheet to recalculate formulas? ›

In the 'Settings for this spreadsheet' box, click on the 'Calculation' tab. Click on the Recalculation drop-down. Select 'On change and every minute' Click on 'Save settings'

How do you autofill without increasing numbers in Excel? ›

The answer is quite simple. Just hold down the Control (Ctrl) key as you drag down the auto fill handle. The last or any of the numbers do not increment. Save this answer.

How do you copy a formula in Excel without incrementing? ›

Press F2 (or double-click the cell) to enter the editing mode. Select the formula in the cell using the mouse, and press Ctrl + C to copy it. Select the destination cell, and press Ctl+V. This will paste the formula exactly, without changing the cell references, because the formula was copied as text.

What is an example of precision and accuracy? ›

One example of accuracy is the distance an arrow gets from the bullseye center. Precision refers to how repeatable a measurement can be. A good example of precision is the distance between the second and first arrows, regardless of whether they are near the mark.

What affects accuracy and precision? ›

Because the potential error is greater, the measure is less precise. Thus, as the length of the unit increases, the measure becomes less precise. The number of decimal places in a measurement also affects precision.

What factors affect precision and accuracy? ›

Factors that affect the precision and accuracy of measurement:
  • Apparatus (equipment) that is used in measurement: Calibration of apparatus. Quality of apparatus. ...
  • Skills of using equipment: Human errors in measurement by using equipment. ...
  • Capability to calibrate the instrument. Difference in calibrated value and true value.
21 Apr 2020

What is the most precise method of measuring data? ›

As the highest of the four levels of measurement (i.e., nominal, ordinal, interval, and ratio), ratio level of measurement is the most precise and therefore, desirable to use in social scientific research.

How do you know if your data is precise? ›

Measurements are precise when you measure the same item multiple times and the values are close to each other. However, precision tells you nothing about whether the measured values are near to the correct value. Measurements can be close to each other but far from the proper value.

How do you calculate precision? ›

We can calculate the precision as follows: Precision = TruePositives / (TruePositives + FalsePositives) Precision = 95 / (95 + 55) Precision = 0.633.

What does it mean by iterations? ›

Definition of iteration

1 : version, incarnation the latest iteration of the operating system. 2 : the action or a process of iterating or repeating: such as. a : a procedure in which repetition of a sequence of operations yields results successively closer to a desired result.

What are the three types of iterations? ›

We will study three forms of iteration: tail-recursion, while loops, and for loops. We will use the task of reversing a list as an example to illustrate how different forms of iteration are related to each other and to recursion. A recursive implementation of reverse is given below.

What are the 2 types of iteration? ›

There are two ways in which programs can iterate or 'loop': count-controlled loops. condition-controlled loops.

How do you keep formulas fixed? ›

If you want to maintain the original cell reference when you copy it, you "lock" it by putting a dollar sign ($) before the cell and column references. For example, when you copy the formula =$A$2+$B$2 from C2 to D2, the formula stays exactly the same. This is an absolute reference.

Can you lock formulas in Excel? ›

Now, go to lock the selected cells with formulas. To do this, press Ctrl + 1 to open the Format Cells dialog again, switch to the Protection tab, and check the Locked checkbox. The Locked option prevents the user from overwriting, deleting or changing the contents of the cells.

How do you retain the original value when a referenced cell changes in Excel? ›

If you want to KEEP the text/value that is shown in a cell for "all future":
  1. Select the cell.
  2. Select COPY from the menus/ribbon or hold CTRL and hit C.
  3. Immediately select "Paste special" from the menus/ribbon,
  4. select "[v] Values" in the dialog that opened, then click OK.
10 Oct 2015

How do you fix all formulas in a cell? ›

In the Formula Bar, put the cursor in the cell which you want to make it constant, then press the F4 key. In this case, I don't want the cell reference A1 to be adjusted with the formula moving, so I put the cursor on A1 in the formula, and then press F4.

How do I fix formulas in multiple cells? ›

Just select all the cells at the same time, then enter the formula normally as you would for the first cell. Then, when you're done, instead of pressing Enter, press Control + Enter. Excel will add the same formula to all cells in the selection, adjusting references as needed.

How do you make a formula dynamic in Excel? ›

Dynamic Formulas begin with &== and are followed by an Excel formula. Repeating Dynamic Formulas begin with &=&= and are followed by an Excel formula. You may use most of Excel's functions in a Dynamic Formula.

How do I manually update a formula? ›

In Manual mode, you can refresh formulas by pressing F9.

How do I automatically adjust formulas in Excel when adding rows? ›

Auto fill formula when inserting blank rows with creating a table
  1. Select the data range that you want to auto fill formula, and then click Insert > Table, see screenshot:
  2. In the Create Table dialog, check the My table has headers if there are headers in your data, see screenshot:

What iteration is used for? ›

Iteration allows algorithms to be simplified by stating that certain steps will repeat until told otherwise. This makes designing algorithms quicker and simpler because they don't need to include lots of unnecessary steps.

What is iteration number? ›

The number of iterations for the inner for loop where rounds down a real number toward the nearest lower integer. The number of iterations for the nested for loops is N=N1×N2.

What's another word for iteration? ›

What is another word for iteration?
replicationduplication
repetitionreiteration
replayredo
renewalreprise
reduplicationrepeat
33 more rows

What is simple iteration method? ›

In computational mathematics, an iterative method is a mathematical procedure that uses an initial value to generate a sequence of improving approximate solutions for a class of problems, in which the n-th approximation is derived from the previous ones.

What are the 4 steps in an iterative process? ›

The iterative process involves a continuous cycle of planning, analysis, implementation, and evaluation. Each cycle produces a segment of development that forms the basis for the next cycle of iterative improvement. You'll start with initial planning and defining overall requirements.

How do I turn off automatic percentage in Excel? ›

It is very simple ,first of all put “a” in excel cell, then press “ctrl+1” then select “font” then select “superscript” then “ok” then put numbers (like-1,2,3…..), then press enter.

What function key recalculate Excel? ›

To refresh or recalculate in Excel (when using the F9 for The Financial Edge), use the following keys: To refresh the current cell - press F2 + Enter. To refresh the current tab - press Shift + F9. To refresh the entire workbook - press F9.

How do I stop Excel from updating data tables? ›

To prevent Excel from automatically doing all the Data Table calculations each time, follow these steps: On the Excel Ribbon, click the Formulas tab. At the far right, click the Calculation Options command. In the drop down list of options, click Automatic Except for Data Tables.

Why is Excel turning my numbers into percentages? ›

Formatting empty cells If you apply the Percentage format to cells, and then type numbers into those cells, the behavior is different. Numbers equal to and larger than 1 are converted to percentages by default; and numbers smaller than 1 are multiplied by 100 to convert them to percentages.

Can you remove percentage? ›

Click the Number tab on the Format Cells window. Click the "General" listing in the Category pane. This action will remove the percentage signs and display the number without any formatting.

How do I turn percentage off? ›

How to calculate percent off?
  1. Divide the number by 100 (move the decimal place two places to the left).
  2. Multiply this new number by the percentage you want to take off.
  3. Subtract the number from step 2 from the original number. This is your percent off number.
10 Mar 2022

What is F1 f2 F3 f4 f5 f6 f7 f8 F9 f10 F11 F12? ›

The function keys or F-keys on a computer keyboard, labeled F1 through F12, are keys that have a special function defined by the operating system, or by a currently running program. They may be combined with the Alt or Ctrl keys.

What does shift F9 do in Excel? ›

Shift+F9 calculates the active worksheet. Ctrl+Alt+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.

How do I prevent Excel from automatically replicating formulas in tables? ›

File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type tab > uncheck Fill formulas in tables to create calculated columns.

Do Excel tables speed up calculations? ›

If you have a large set data than you should avoid using excel data tables. They use a lot of computer resources and can slow down the performance of Excel file. So, if it is not necessary, avoid using Data table for faster calculation of formulas. This will increase formula calculation speed.

Videos

1. Enable Iterative calculation. Set maximum number of iterations to 88 and maximum change to 0.005
(hack IT in 30 seconds by TEST4U)
2. Using Excel Circular References To Do Calculations
(Codible)
3. Set Calculation Options in Excel for Mac
(UrBizEdge)
4. Coding Challenge #140: Pi Approximation with Leibniz Series
(The Coding Train)
5. Circular references in Excel
(Rodolfo Vanzini)
6. Excel calculate wrong value
(Ravy Chap)
Top Articles
Latest Posts
Article information

Author: Merrill Bechtelar CPA

Last Updated: 03/10/2023

Views: 6128

Rating: 5 / 5 (50 voted)

Reviews: 89% of readers found this page helpful

Author information

Name: Merrill Bechtelar CPA

Birthday: 1996-05-19

Address: Apt. 114 873 White Lodge, Libbyfurt, CA 93006

Phone: +5983010455207

Job: Legacy Representative

Hobby: Blacksmithing, Urban exploration, Sudoku, Slacklining, Creative writing, Community, Letterboxing

Introduction: My name is Merrill Bechtelar CPA, I am a clean, agreeable, glorious, magnificent, witty, enchanting, comfortable person who loves writing and wants to share my knowledge and understanding with you.