How to Allow Circular References in Excel & Google Sheets

This article will demonstrate how to allow circular references in Excel and Google Sheets.

 

 

By default, Excel returns an error message when a circular reference is detected, and the formula causing the circular reference will not calculate. On some occasions, you may need need to allow the use of circular references in Excel.

Enable Iterative Calculation

In the Ribbon, select File > Options > Formulas.

 

 

Make sure Enable iterative calculation is checked, and set the Maximum Iterations and Maximum Change as required.

Note: Maximum Iterations is the number of times that Excel will repeat the calculation until it stops (i.e., 100). Maximum Change is the maximum difference allowed between the iterations of the calculation. If the maximum change is reached, Excel will stop repeating the calculation EVEN IF the number of times it has recalculate has not reached maximum iterations.

 

How to Allow Circular References in Google Sheets

Google Sheets also shows an error when a circular reference is detected.

 

 

To allow circular references, in the Menu, select File > Spreadsheet settings.

Select the Calculation tab, and then in the Iterative calculation drop down, select On.

 

 

Adjust the Max number of iterations and Threshold if required, and then click Save settings.