How to Find a Circular Reference in Excel: Locating, Removing, & Allowing

• A circular reference is when a formula tries to calculate itself.
• Go to Formulas > Error Checking > Circular References to see a list of circular references.
• Enable “iterative calculations” in the options menu to allow circular references to run.

What is a circular reference?

A circular reference is when a formula refers to the cell it’s being written in. This means the Excel formula you made is trying to incorporate itself into the formula, creating a loop. Cells with a circular reference will show a zero or the last calculated value. There are two common ways a circular reference occurs: Direct reference: The formula refers to the cell it’s being written in. Indirect reference: The formula refers to another cell that refers to the cell the formula is being written in.

Check out this example of a direct circular reference. Let’s say you’re trying to add the values of cells A1, A2, and A3. You write the summation formula “=A1+A2+A3+A4” in cell A4. This causes a circular reference, since the summation formula includes the cell it’s being written in, A4.

Check out this example of an indirect circular reference. You add the values in D2 and E2 with a formula in D3: “=D2+E2”. Then you create another formula in E2: “=D2+D3”. Now, the formula in D3 references the formula in E2, which includes D3, creating an indirect circular reference.

Find and Fix Circular References

Click Formulas. This is a tab at the top of Excel. Fixing a formula in Excel can be tricky, but luckily the software has built-in error checking!

Click the Error Checking down arrow. This will open a drop down menu with error checking options.

Hover your cursor over Circular References. This will show you the next circular reference in the current worksheet.

Click the cell in the Circular References list. This will take you to the cell with the circular reference. These can sometimes occur when you copy and paste formulas, since the cell references can shift.

Review the cell’s formula. Check whether the cell includes a reference to itself. If the formula is complicated or your worksheet has a complex workflow of formulas, use Trace Precedents and Trace Dependents to see what cells the formula refers to and affects: Select the cell with the circular reference error. Click the Formulas tab. Click Trace Precedents to see what cells the formula uses. Click Trace Dependents to see what cells have formulas referencing the cell you selected.

Enable Iterative Calculations

Open the calculation options. This method turns on Iterative Calculations in the options menu. This allows formulas with circular references to recalculate a specified number of times. This can slow down the Excel file, especially for complex formulas. To open the calculation options: Windows: Click the File tab. Click Options in the left sidebar. Click Formulas in the Options window’s sidebar. Mac: Click the Excel menu bar item. Click Preferences in the drop down menu. Select Calculation.

Click the box next to “Enable iterative calculation” (Windows). This is called “Use iterative calculation” on Mac.

Change the Maximum Iterations and Maximum Change values (optional). Excel will recalculate the circular reference formula either when it has iterated the maximum amount of times, or when the calculated values in the formula differ by less than the maximum change. Increasing the Maximum Iterations or decreasing the Maximum Change will increase the calculation time.