How To Fix Excel Circular Reference Error?

Summary:

This tutorial mainly focuses to provide you all with the best fixes to resolve Excel circular reference error. Apart from this, you will also get to know every basic detail regarding Excel circular reference error like what are it’s causes and ways to prevent it off.

What’s This Excel Circular Reference Error Is?

Sometimes when you enter any formula it doesn’t work. Instead of that, you will start receiving circular reference error in Excel.

Well, such an issue arises because your assigned Excel formula is trying to calculate itself.

Example:

For more clear idea, let’s understand this with a simple example:

In cell B3 you are using the formula =D1+D2+D3. So in this case you are using a function that includes the reference to itself.

To fix this problem, you need to move your formula to some other cell. For this, just press the Ctrl+X from your keyboard to cut the formula, and after selecting another cell press the Ctrl+V button to paste it.

Error Screenshot:

What Causes Circular Reference Error In Excel?

After reading about Excel Circular Reference Error you must have got enough idea about the actual problem.

Overall circular reference error occurs when the formula either directly or indirectly refers to the cell in which it is already contained. Due to this, the formula recalculates its already obtained result which generates such type of error.

If your Excel workbook is having any circular reference then automatic calculation won’t be performed.

Instead of that, you need to make use of the Excel error checking feature to locate the location of the circular references and after that remove them.

If in case you leave them as it is then Excel will calculate each cell involved in this circular reference by making use of the results obtained from the previous iteration.

Note: here iteration means the process of repeated recalculation until it will match specific numeric criteria.

How To Fix Circular Reference Error In Excel?

Method 1# Locate a Circular Reference

• Hit the Formulas.
• Tap the Error Checking button and hit the arrow sign present next to it.

• From its drop-down choose Circular References and again hit the side arrow sign.
• From the submenu hit the first cell.
• Now make a review of the formula cell.

• If you are in doubt that the cell is responsible for circular reference error. Then hit the next cell present in the Circular References submenu, if it is available.
• Continue to the review option and make corrections in the circular reference.
• Very soon your status bar will notify you that “Circular Reference” is present in your workbook along with the address of that cell.

• If these circular references are present in some other sheets then the status bar will only show you the “Circular References” word without any cell address.

Here are some more tricks that you can try to fix Excel circular reference error:

• After assigning the formula in a cell, make a check whether you have referenced that formula cell in the cell itself.

Suppose, in cell B3 you have added a formula = (A1+A2)/A3. Then this will cause such circular reference errors.

• The second option is to check whether there are some indirect references.

This will happen when you enter your formula in cell A1 and this uses another formula present in cell B1 which in turn refers back to the A1 cell.

If this whole process confuses you then just think over it how much your Excel application gets confused.

Method 2# Enable / Disable Circular References In Excel

As it is already mentioned, that iterative calculation by default gets turned off in Excel. In order to make your circular formulas working you need to enable the iterative calculations within your Excel file.

• If you are an Excel 2003 user then hit the Iterative Calculation option present within the Menu > Tools > Options > Calculation tab.

• If you are an Excel 2007 user then hit the Office button > Excel options > Formulas > Iteration area.

• In Excel 2019/ 2016/ 2013/ 2010

Hit the File>Options. Now tap on the formulas tab and choose to Enable iterative calculation check box present within the “Calculation options”

After turning on the iterative calculations you need to specify these two options:

• Maximum Iterations box –

This option is for specifying the number of times the formula will be recalculated. If you set the higher number of iterations then the calculation will take more time.

• Maximum Change box –

Using this option you can set the maximum change between the calculation results. It’s better to set this number small as this will give a more exact result. If you set a higher number then Excel will take a longer time to do calculations.

Note: Usually Excel stops doing calculations after 100 iterations. Or when all values present within the circular reference are been modified by less than 0.001 between iterations.

How To Prevent Excel Circular References Error?

• When you need to switch between the cells in the circular reference worksheet then do this by double-clicking the tracer arrow. This arrow indicates those cell which affects the value of your currently selected cell.
• Show the tracer arrow by tapping to the Formulas after that either choose Trace Dependents or Trace Precedents.
• Close all your opened workbooks and then create a new workbook. After that enter the formula which contains a circular reference.

Why You Should Avoid Using Circular References In Excel?

The idea of using the circular reference in Excel is not a well-recommended approach. I am telling this because apart from displaying a warning message or performance issue it leads to several other issues that won’t appear immediately.

Suppose that you have selected a cell that contains the circular reference and then mistakenly, you have turned off the formula editing mode. So when you press the enter button without doing any modification in the formula then it will give you the result zero.

That’s the reason why many Excel experts also suggest avoiding the usage of circular references in your Excel workbook if it’s possible.