What is all that noise about Circular Reference?
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:
In the above cells, you can see that the formula calculates a sum in cell D15 but the formula includes D15 in its range.
CLICK HERE: If you are looking for self-paced Advanced Excel Training with Lifetime access and support
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 will appear giving you various tips to correct your formula.
How to find circular reference in your sheet:
To find circular reference formula in a file, follow these some simple steps to find and remove circular references
- 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 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 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.
Say, 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 after 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.
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!
Are you beginner in Excel then do enroll for Free Online Excel Course