How to Enable Iterative Calculation in Google Sheets

Many people that use Google Sheets won’t encounter problems with the settings that it has in the default configuration.

But if your spreadsheet contains a formula with a circular reference that you want to calculate multiple times, then that can be an issue.

Our tutorial below will show you how to find the iterative calculation setting in Google Sheets so that you can turn it on and run your formulas.

How to Activate Google Sheets Iterative Calculation

  1. Open your Sheets file.
  2. Click the File tab.
  3. Select Spreadsheet settings.
  4. Choose the Calculation tab.
  5. Turn on Iterative calculation, adjust its settings, then click Save settings.

Our guide continues below with additional information on how to enable iterative calculation in Google Sheets, including pictures of these steps.

If you have a formula in Google Sheets that contains a circular reference, then you might have found that executing that formula can be problematic.

This is due to a Google Sheets setting where the iterative calculation is disabled by default.

Many formulas that contain circular references often do so accidentally, and they can cause potential issues if it’s not intentional.

But if you are intentionally using circular references, then you will need to turn on the iterative calculation option.

Our guide below will show you where to find this setting, as well as how you can specify the maximum number of iterations and a threshold for when it should stop occurring.

If you also use Microsoft Excel and would like to compare values in different cells, then read our how to subtract in Excel tutorial for more information.

How to Turn on Iterative Calculation in Google Sheets (Guide with Pictures)

The steps in this article were performed in Google Chrome. These steps should also work in other Web browsers, but may vary if you are working on a mobile device or in an app.

Step 1: Open your Google Drive at https://drive.google.com/drive/my-drive and open a Google Sheets file.

Step 2: Click the File tab at the top of the window.

click the file tab

Step 3: Select the Spreadsheet Settings option near the bottom of the screen.

open the spreadsheet settings menu

Step 4: Choose the Calculation tab near the center of the window.

enable google sheets iterative calculations

Step 5: Click the Iterative calculation dropdown and turn it on, then specify the Max number of iterations and the Threshold. You can then click the Save settings button to save the settings.

how to turn on iterative calculations in google sheets

Now that you know how to turn on iterative calculation in Google Sheets, you will be able to control what the application does when it encounters a circular reference.

Do you often work as part of a team in Google Sheets, and you like to use the Notes feature?

Find out how to clear all of the notes from a spreadsheet so that they aren’t present when you need to submit a final version of the sheet or share it with someone whom you don’t want to see all of your internal notes.

Join Our Free Newsletter

Featured guides and deals

You may opt out at any time. Read our Privacy Policy