‘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?
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.
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.
II. What is an Indirect Circular Reference in Excel?
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.
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.
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
|#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/A||This 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 on ‘Circular 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.
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.
(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.
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.
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.
- How to Protect Excel Formula, Cells, Worksheet & Workbook - June 8, 2020
- 08 Best Examples of Excel Conditional Formatting - June 2, 2020
- 04 Best Ways: How to Transpose Data in Excel - May 23, 2020
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.