A blue arrow connecting the two cells offers a graphical representation of the problem. There isn’t an answer for Excel to output, so it displays an error. This is a circular reference, as the “formula” can’t be completed. Both contain references to the other cell, with A2 containing =B2, and B2 containing =A2. Let’s assume you have two cells: A2 and B2. Calculating the result of a formula, when the cell containing that formula is part of the calculation, results in an endless loop that Excel can’t complete. What are circular references in Excel and how do they work?įor most users, a circular reference in Excel is problematic.

How to create a circular reference formula in ExcelĪre you struggling with circular references in Excel? Then keep reading! 1.

How to enable iterative calculations in Excel.

How to quickly find and remove unwanted circular references in Excel.

What are circular references in Excel and how do they work?.

Whether you’re looking to use circular references or avoid them entirely, this guide will explain: If you want to insert an iterative calculation (one that repeats itself a limited number of times until certain conditions are met), you might want to use a circular reference formula, but you’ll need to tweak Excel’s settings to allow it. However, there is one way you can use circular references in Excel to your advantage. For instance, if you’re analyzing data with a SUMIF formula, and one of the cells in your data set is referencing the output from the same cell containing that formula, Excel can’t calculate the result, causing this error to appear. You’ll see circular reference warnings for any formula you try to insert into Excel that loops back to itself. We’ll explain everything you need to know about circular references (and how to avoid or potentially use them) in this step-by-step guide. As the formula can break its own result, this creates a circular reference. If you see a circular reference warning in Excel, it usually points to a formula in a cell that, in some way, refers back to itself.

Turn on data analysis excel