**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: Its work in excel 2006, excel 2007, excel 2010, 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.

CLICK HERE: If you are looking for self-pacedAdvanced Excel Trainingwith 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 or search term will appear giving you various tips to correct your formula.

**How to find a circular reference in your sheet:**

To __find circular reference formula in a file, __follow these some simple steps to find excel function 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 doenrollfor Free Online Excel Course