How to find circular references in Excel (with examples)

Updated 17 March 2023

If you've tried entering a formula in Excel and it's not working, you may have a circular reference. Circular references can slow down your workbook activities by causing miscalculations and errors. Learning how to find circular references ensures you eradicate them and increases the accuracy of your Excel spreadsheet. In this article, we discuss what a circular reference is and provide an example, before outlining how to find circular references in Excel, remove them and enable or disable them.

What is a circular reference in Excel?

A circular reference occurs in Excel when a formula refers back to its cell. This reference can be direct or indirect. Circular references can repeat endlessly on a continuous loop, thereby slowing down workbook calculations. This is because it visits its cell more than once in its calculation chain, creating an infinite bounce-back effect.

When a cell contains a circular reference, the following message usually appears as a warning: 'Careful, we found one or more circular references in your workbook which might cause your formula to calculate incorrectly.' Upon closing this window, the cell containing the circular reference usually displays a zero or the last calculated value in the cell. Most circular references are accidental and the result of an indirect calculation.

Circular reference example

Cell A6 contains the sum of phone sales by using the formula 'A1:A5'. This is because cells A1 to A5 hold the sum of individual phone sales. By using this formula, cell A6 displays the total amount of the cells and therefore the total sum of phone sales. When inputting the formula, the person accidentally puts in 'A1:A6'. This prompts the function in the cell to perform a calculation that depends on the result of the exact calculation its performing since A6 is included in the calculation. This prompts Excel to issue a circular reference warning.

How to find circular references in Excel

Learning how to find circular references in Excel is important for preventing them from skewing your calculations. To find circular references, follow these steps:

Open the Excel document containing circular references.
Click the 'Formulas' tab in the ribbon menu.
Click the arrow next to 'Error Checking'.
Select 'Circular References' to find out where the last entered circular reference is in the document.
Click on the cell listed under 'Circular References' to navigate directly to that cell.

Upon doing this, the status bar displays a message notifying you that you've found all circular references present in your workbook and displays the location of one of the cells in your worksheet.

If circular references are present in other worksheets on your Excel document, the status bar reads 'Circular References' but doesn't provide you with a cell address. Upon finding a circular reference, you can either choose to remove it or replace it with a workable function. Note that you're unable to use this location feature if you've manually enabled the 'Iterative Calculation' option.

Removing circular references in Excel

There is no mechanism in Microsoft Excel that allows you to remove all circular references with a simple button click. The software requires you to locate each circular reference individually using the above steps. Yet it's possible to trace the relationships between cells and formulas using Trace Precedent and Trace Dependent features, thereby speeding up the removal process.

The former draws a line showing you which cells affect the selected cell. This allows you to trace cells that provide data to a formula of the cell. Trace Dependents show you which cells contain formulas that reference the selected cell. This helps you locate cells that are dependent on the active cell. These features draw lines between the cells to demonstrate how they affect one another. Steps on how to display the race arrows include:

Open the Excel spreadsheet containing circular references.
Go to the 'Formulas' tab on the top ribbon menu.
Locate the 'Formula Auditing' group.
Click either the 'Trace Dependents' or 'Trace Precedents' option.
When you've finished, click the 'Remove Arrows' button underneath 'Trace Dependents'. Alternatively, you can use the following shortcuts to display trace arrows:

Trace Precedents: Alt+T U T

Trace Dependents: Alt+T U D

Direct vs. indirect circular reference

A direct circular reference occurs when a formula directly references its cell. For instance, a formula in cell A1 that reads 'A1:B2' is a direct circular reference. Alternatively, an indirect circular reference occurs when a formula refers to its cell unknowingly. An example of an indirect circular reference is:

Cell A1 contains the number 10. Cell A2 contains the formula '=A1*5' which produces the number 50 in the cell because 10 – the value of cell A – multiplied by five is 50. Cell A3 contains the formula '=A2*2' which displays the number 100 since 50 multiplied by two equals this. In attempting to replace the number 10 in cell A with the formula '=A2*2' from cell A3, the circular reference error appears. Upon pressing 'OK' the cell returns a zero. This happens because, throughout the chain of calculations, the value in cell A1 relies on itself.

Why avoid using circular references in Excel?

Circular references in Excel can cause performance issues that aren't always immediately apparent. This undermines the integrity of your calculations and could lead to data misrepresentation. If you select a cell containing a circular reference and switch the formula to the editing mode by double-clicking the cell or pressing F2, the formula returns to 0 when you press enter. This means that you're required to start again. It's important to note that there are some instances where using Excel circular references is necessary or justified.

When to use a circular reference in Microsoft Excel

Sometimes using a circular reference provides a shorter solution for calculations, or is simply the only possible one. Consider this example:

You have a list of inventory items in column A. Column B shows the delivery status of each of these inventory items with a 'Yes' or 'No' as to whether they've arrived. You wish to update the spreadsheet so that the same row in Column C automatically displays the current time and date when you type 'Yes' in a B column – thereby allowing you to track the arrival of deliveries. This requires you to use the IF function with a circular reference. For instance, the formula in C1 reads: = IF(B1="yes", IF(C1="" ,NOW(), C1), "") This function prompts the cell to read cell B1 and check for a 'Yes'. If the text is present, it runs a second IF to prevent the function from returning an empty string. This second IF formula notes the current date and time and displays it in cell C1 provided the cell doesn't already have a value in it.

For iterative functions to work, it's essential that you allow iterative calculations in your Excel worksheet. This prevents Microsoft Excel from issuing warning messages when opening the workbook and the function returning a zero.

Enable or disable circular references in Excel

Microsoft Excel automatically turns off iterative calculations. When turning on iterative calculations, be sure to specify these two options:

Maximum change: This specifies the maximum amount of change between calculation results. Smaller numbers allow you to acquire more accurate results but within an increased timeframe and the default setting Is usually 0.001.
Maximum Iterations: This box requires you to specify how many times you want the formula to recalculate. The default setting is usually 100 and the more iterations you allow, the more time calculations take.

The steps to enable iterative calculations may vary depending on the version of Microsoft Excel you're using. To disable this feature, you simply follow the same instructions but click disable rather than enable at the last step. Steps on how to enable iterative calculations for different versions include:

Excel 2010, 2013, 2016 and 2019

To enable iterative calculations on Excel 2010, 2013, 2016 and 2019, follow these steps:

Create a new Excel worksheet or open an existing one.
Press 'File' in the top menu.
Click 'Options'.
Navigate to 'Formulas'.
Locate the 'Calculation' options section.
Select the 'Enable iterative calculation' check box in this section.

Excel 2007

To enable iterative calculations on Excel 2007, follow these steps:

Create a new Excel worksheet or open an existing one.
Click the 'Office' button.
Select 'Excel options'.
Click 'Formulas'.
Select 'Iteration area'.
Allow iterative calculations.

Excel 2003 and earlier versions

To enable iterative calculations on Excel 2003 and earlier versions, follow these steps:

Create a new Excel worksheet or open an existing one.
Select 'Menu'.
Go to 'Tools'.
Locate and click the 'Calculation' tab.
Enable iterative calculations.

Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.