How to find Circular Reference in Excel & Fix it

How to find Circular Reference in Excel & Fix it

By

How to find circular reference in Excel and fix it‘ – a very common problem in Excel, because most circular references are much more complex and are often indirectly linked through a series of intermediate formulas.     

A circular reference in Excel is a situation that occurs when a formula contains a direct or an indirect reference to the cell containing the formula. 

Occasionally we get a Circular Reference Warning message while entering a formula which indicating that the formula we just entered will result in a circular reference.

 

I. What is a Direct Circular Reference in Excel?

Direct Circular Reference-1

When a formula in a cell directly refers to its own cell is called a direct circular reference.

For example, we want to know the total Project Cost of a project. Therefore, we use the SUM formula into cell C16 and the SUM_range is C3:C15. But unfortunately, we extend the SUM_range to C3:C16, it creates a circular reference, because the formula in cell C16 refers to cell C16. Every time the formula in C16 is calculated, it must be calculated again because C16 has changed. The calculation could go on forever.

Direct Circular Reference-2

When we get the circular reference message after entering a formula, Excel gives two options:

• Either, click on ‘OK’, Excel returns a 0 (zero) value.

• Or, click on ‘Help’, Excel displays a Help screen that tells more about circular references.

Direct Circular Reference-3

II. What is an Indirect Circular Reference in Excel?

Indirect Circular Reference-1

When a formula in a cell indirectly refers to its own cell is called an indirect circular reference.

We want to apply the revised Project ID in our Main database. Therefore, we generally use the VLOOKUP function to retrieve the exact match value. The VLOOKUP function is applied here besides the main database, i.e., in column D.

As a result, New Project ID is mapped against the Old Project ID. If we want to plot the New Project ID over the Old Project ID (i.e., from the cell D7 to the cell B7) through cell reference (using equality ‘=’ sign).

Thus, it creates a circular reference and Excel returns a 0 (zero) value.  In other words, the formula in cell B7 indirectly refers to its own cell. Excel cannot allow this.    

Indirect Circular Reference-2

Note: (i) In many cases, when entering more than one formula with a circular reference, Excel doesn’t display the warning message repeatedly.

(ii) When we open the file again after saving then it also shows the warning message that one or more formula contains a circular reference. Always take care of the warning and check that circular reference is enabled.

Indirect Circular Reference-3

III. Excel Error Values 

When Excel cannot evaluate a formula correctly, it returns an error value. All Excel errors begin with a pound sign (#). There are a number of different error values displayed depending on the type of error: 

Table: Excel error values 

Error ValueExplanation
#DIV/0!(i) This is a divide-by-zero error. It indicates that the formula is trying to divide by zero.

(ii) This also occurs when the formula attempts to divide by a cell that's blank, because Excel treats a blank cell to zero for the purpose of simple calculations with the arithmetic operators.
#NAME?The name error occurs if Excel cannot evaluate or recognize a defined name used in the formula. This can happen if we delete a name that's used in the formula or maybe misspelled or applying unmatched quotes when using text.
#N/AThis error signifies that the value isn't available. This error can happen if we try to perform certain types of lookup or statistical functions that work with cell ranges. For example, if we use a VLOOKUP function to search a range and it can't find the value that we need, we may get this error.

(ii) Sometimes users enter a #N/A value manually in order to tell Excel to ignore a particular cell when creating charts and graphs. The easiest way to do this is to use the NA() function (rather than entering the text #N/A).
#VALUE!(i) The value error occurs when we trying to calculate text instead of numbers, or

(ii) entering incorrect argument in a function. For example, the function expects a single value and we submitted a whole range.
#NUM!(i) This error means there is a problem with a number - either the number cannot be interpreted because it is too small or too big, or because it does not exist.

(ii) If we used an incorrect argument type in a function; for example, we specified a negative number where a positive number is expected.
#REF!This error occurs when the formula refers to a cell that isn't valid, as result cell reference is invalid. This can happen if the cell, row or column has been deleted from the worksheet.
#NULL!The Null error occurs when the formula uses an intersection of two ranges that don't intersect.
########This code isn't actually an error condition - in all likelihood, Excel has successfully calculated the formula. However, the formula can't be displayed in the cell using the current number format. To solve this problem, we can widen the column, or possibly change the number format.

IV. How to Find Circular Reference in Excel?

 

To find the circular reference in Excel, go to the ‘Formulas‘ tab ➪ Click on the ‘Error Checking drop-down menu under the Formula Auditing group ➪ click onCircular References.

This command displays a list of all cells that are involved in the circular reference in Excel. Start by selecting the first cell listed and then sorted out the problem.

How to Find the Circular Reference

V. How to Fix both Direct and Indirect Circular Reference in Excel

 

If Excel displays an error message warning with a circular reference in Excel, we should fix it immediately. Unresolved circular references will cause unpredictable and inaccurate results in the working worksheet.

Start by selecting the first cell listed and then work your way down the list until you figure out the problem.

(i) In the first case (Direct Circular Reference), change the range inside the SUM formula from C3:C16 to C3:C15. The circular reference has been fixed and the SUM formula returns the correct result.

Fix the Direct Circular Reference-1

(ii) In the second case (Indirect Circular Reference), while we get the Circular Reference warning message at the beginning, we should try to escape (press Esc key) and undo the process (with Ctrl+Z).

First, we make a copy of the formula range D3:D15 by Ctrl+C. Then convert the range of formulas to ‘values‘ by Alt+E+S+V or Alt+Ctrl+V+V. Or, convert the range of formulas to the ‘Values and number formats‘ by Alt+E+S+U or Alt+Ctrl+V+U

Fix the Indirect Circular Reference-1

After that, we use a cell reference to get value from cell D7 to B7 (by using equality ‘=’ sign). Then copy and paste the formula in B8, B10, B13, and B14 respectively.

Fix the Indirect Circular Reference-2

At last, copy the range B3:B15 and convert the range of formulas to ‘values‘ by Alt+E+S+V or Alt+Ctrl+V+V. Or, convert the range of formulas to the ‘Values and number formats‘  by Alt+E+S+U or Alt+Ctrl+V+U

The above process has been fixed the indirect circular reference and the Old Project IDs are successfully replaced by the new Project IDs.

Follow AEF
Sudipta Mondal is a writer, author as well as founder of "Advance Excel Forum (AEF)". It is the best learning platform on Advance Excel.

According to Sudipta, "Advanced Excel is a very necessary modern tool in the advanced world that can only be achieved through a committed focus and self-analyzing skills".
Sudipta Mondal
Follow AEF
How to find Circular Reference in Excel & Fix it

'How to find circular reference in Excel and fix it' - a very common problem in Excel, because most circular references are much more complex and are often indirectly linked through a series of intermediate formulas.     

Editor's Rating:
5

Leave a Comment

You may also like