In Excel, a 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?
Excel Vlookup formula – Guidebook
Bored of downloading text heavy / copy-pasted eBooks?
If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.
It simply means that your formula includes a cell that is covered by the formula. A simple example is as below: Circular reference work in excel 2006, excel 2007, excel 2010, excel 2013, excel 2016 & excel 2019.
Circular Reference Excel is a formula in Excel Cell that directly or indirectly refer to its own cells. So, In this Excel tutorial, we see how to find circular reference excel.
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.
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.
How to find a circular reference on your Excel sheet:
Steps to find a circular reference in Excel as follows
- Click On the Menu bar, Click on Formulas tab, to find circular reference in excel.
- Select Error Checking and point to Circular References and a drop-down list will unfold.
- Click on Circular References from the drop-down list and Excel 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.
Excel Vlookup formula – Guidebook
Bored of downloading text heavy / copy-pasted eBooks?
If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.
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 the Calculation Options menu.
- You can also specify the maximum number of times 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 a 2% commission on total building cost to your builder. Simple….right? Not if 2% includes the cost of a 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.
15 Excel Data Cleaning Tricks Guidebook
Bored of downloading text heavy / copy-pasted eBooks? If Yes, you will enjoy this guidebook (43 pages)
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.
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!
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!
How to Find a Circular Reference in Excel?
To find circular references in excel, perform below steps;
- Click on the Formulas tab to find circular reference in excel.
- Click on the Error Checking button arrow, point to Circular References, and then click the first cell listed in the submenu.
- Review the formula cell & Excel will give out the cell which has circular references
- This way you can find circular references in excel.
In this way, you can easily find the circular reference in Excel. I hope this tutorial helps you.
15 Pivot Tables Tricks for Pros
15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.
Most Popular Tricks are #3, #7 & #12