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
- Open your Sheets file.
- Click the File tab.
- Select Spreadsheet settings.
- Choose the Calculation tab.
- 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 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.
Step 3: Select the Spreadsheet Settings option near the bottom of the screen.
Step 4: Choose the Calculation tab near the center of the window.
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.
Now that you know how ot 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.
Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.
His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.
