How to Find circular reference in Excel

In Excel, Circular reference warning is received with raised eyebrows and terrified looks most of the time. But from our experience, we know it to be all smoke and no fire, which we will prove to you right here. So what really is a circular reference? It simply means that your formula includes a cell which is covered by the formula. A simple example is as below: Circular reference work in excel 2006, excel 2007, excel 2010, excel 2013

Find circular reference excel 2013

In the above cells, you can see that the formula calculates a sum in cell D15 but the formula includes D15 in its range.

What is Find Circular Reference in Excel?

Circular Reference Warning:

Once you create a circular reference, a warning box appears where you can either seek help for correcting formula or click ok to accept the reference. If you do press Ok, the cell will display “-“ or “0” as below.

how to find circular reference in excel 2007

Say, you decide to select help from the dialogue box. Then the following help box or search term will appear giving you various tips to correct your formula. excel circular reference warning wrong

How to find a circular reference on your Excel sheet:

Steps to find a circular reference in Excel

  • Click On Menu bar, Click on Formulas
  • Select Error Checking and a drop-down list will unfold.
  • Click on Circular References from the drop-down list and it will give out the cell which has circular references


This will find excel function and remove circular references.

This way you can review all the formulas shown in cells in the sub-menu and correct them one by one.

Fix Circular Reference warning by enabling iterative calculation:

To fix circular reference warning in your excel worksheet, you need to enable Iterative calculation. It is not enabled by default because the iterative calculation affects and slows down your machine’s performance.

  • Go to File and select options from the drop-down list.
  • A dialogue box will appear; select Formulas.
  • Check the Enable Iterative Calculation option under Calculation Options menu.
  • You can also specify the maximum number of time the system needs to go on calculating the iteration. By default, it is on 100.


Using Circular Reference in Excel for your benefit:

So you must be thinking that circular references are useless and your goal is to find the circular reference and fix it. You’re wrong!. There are formulas and equations that might need you to create a circular reference. Let me take you through one example. Also, You can check our Online Free Excel, PowerPoint & VBA Tips. You are constructing a building and you have decided to give 2% commission on total building cost to your builder. Simple….right? Not if the 2% includes the cost of builder’s fee! If you want to do it using a formula, you can do it. But imagine having hundreds of such factors and iterations. Just follow simple steps as below: Put in the formula for the total cost which includes cell C3.


Next, against the commission, enter the formula as given below which will create a circular reference and the tracer arrow will show it. As far as the circular reference warning box is concerned just click on ok.

Read More: How to add APA Page Numbers in Microsoft Word

Tracer-arrow-Circular-Reference Follow it up by enabling iterative calculations as shown in point 4 above. Once done, you will get the right calculation as below, Problem solved! Enabling-Iterative-Calculation So, as we had set out to prove, Circular referencing is not such a villain as it is usually regarded as. Besides simple tools in excel make it possible for you to identify them and correct them quite easily. Is there another way you find circular reference useful? Share your views here!

Some Related Searches on Circular Reference:

how to find circular reference in excel
find circular reference
excel find circular reference
how to find circular reference
circular reference in excel
find circular reference excel
find circular reference in excel
how to find a circular reference
how to find circular references in excel
how to find a circular reference in excel

Pre-Registration Open

Power BI Dax

Related Tutorials

Text To Column in Excel | Split Cells
January 4, 2019
Pivot Tables (Complete Guidelines)
December 27, 2018
Power Bi Dax Deduplication Based On Column
December 17, 2018
Special Character Symbol List with Shortcodes in Excel
December 15, 2018
December 10, 2018
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018