How to Find Circular References in Excel to Avoid Faulty Data

Did you see the circular reference error on an Excel sheet and wondering if the worksheet is broken? Trust me! You’re not the first one to face this Excel error. Hundreds and thousands of Excel users experience circular reference errors every day. Not to worry! Here’s how to find circular references in Excel and resolve those.

Being an avid Excel user, you rarely type the same data in many cells. Instead, you import a data set from the source and use cell referencing to create formulas. Thus, you create a dynamic spreadsheet.

When someone changes the cell values of the cell references, the worksheet updates itself automatically. This is called data analysis automation and data analysts love to play with cell references.

However, the problem arrives when you intentionally or accidentally type the cell address where you’re currently performing a calculation. Or, when you’re creating many formulas throughout the worksheet, a cell containing a formula appears in another formula indirectly.

In such and many other similar situations you get the circular reference error in Excel. Read on to learn what circular reference is, its reasons, why to avoid it, and most importantly how to find circular references in Excel to get rid of these anomalies for good.

What Is a Circular Reference in Excel?

Excel encounters a circular reference error when it sees that one or many functions of the worksheet reference back to the cell address where their functions were created.

It might sound confusing to you, so think about how severe it could be for the Excel app. In short, you never want to put the cell address in a formula where you’re creating it. Look at the following example for a visual clarification:

You’re trying to multiply 10 from cell B1 and put the result in cell B1 itself.

Now, in the above scenario, if you just press OK, Excel will close the error and show zero as the calculated result. This is just a straightforward example.

Now, imagine there are hundreds of formulas in a bookkeeping worksheet, finance planning worksheet, employee payroll, vendor payment worksheet, and so on.

You’ll trust the faulty data calculated by Excel due to the circular reference error and that’ll in turn impact your business or professional decision-making.

Furthermore, circular referencing can severely reduce the speed of calculations within the Excel app. In severe cases, the very error can cause extremely slow performance in the PC too if not dealt with immediately.

However, if you’re working on advanced Excel formulas where your data model demands intermediary inputs from the circular reference to continue with a long function, you may need to use it. In fact, Excel lets you use this calculation method when necessary.

For example, in energy production units, like windmills, you need to calculate recycled output energy from a fraction of input energy. Alternatively, in a chemical plant, engineers use the output stem as input for the same or another boiler or process vessel. Such high-level engineering calculations indeed require circular references for iterative calculations.

When Do You See the Circular Reference Error in Excel?

Clearly speaking, the circular reference pop-up isn’t an error message. It’s a way for Excel to inform you that it encountered circular reference in one or many formulas and that may influence its calculation algorithm and performance.

To avoid any damages caused by circular references in Excel formulas, you must know when they appear and when they don’t. Find below the instances:

• In any open worksheet or workbook, you create a formula and enter the circular reference for the first time.
• You or someone removed all the circular references from the Excel file and saved it. Now, again you or another user enter a circular reference in the same file.
• You closed all open workbooks. Now, opened a different workbook and then type a formula containing a circular referencing.

• You’ve just opened a workbook that previously contained one or many circular references. You’ll see the warning.
• Once Excel has shown you the circular reference warning message once and you clicked OK, it won’t show the same pop-up anymore in the same session even if you keep creating more circular referenced formulas.

Thus, you must carefully look for the above instances to catch circular reference warnings and resolve those if you’re not looking for iterative calculations.

Types of Circular References in Excel

If you want to learn how to find circular references in Excel, you must understand their types. Because finding and eliminating circular references in Excel isn’t an automated task. Find below the common types of this error:

• Intended circular references occur when you use them intentionally for iterative calculation purposes. For example, you need to input a time stamp on another cell when someone enters a value in a referenced cell using volatile and logical functions like IF, OR, SUMIF, etc.

• Unintended circular referencing when you try to work on an Excel formula faster and refer to the formula-containing cell mistakenly. For instance, you need to apply the SUM function from cell A1 to A9 and fetch the result in cell A10. But mistakenly, you type the formula like this on cell A10:

=SUM(A2:A10)

• Hidden circular references are those that don’t show any warning messages when you create a formula or open the worksheet for the first time. It happens because someone allowed iterative calculation on the Excel file.

• Indirect circular references are also popular and they occur when you create a new formula in any cell, for instance, cell A1, and this new formula would use another function in another cell, like cell B1, which already is using cell A1 as a reference.

Now that you understood the basics of circular references in Excel, let’s find out how to find circular references in Excel below:

How to Find Circular References in Excel: From Status Bar

Provided that the iterative calculation feature is deactivated, you can see the following notifications about circular references on the Excel Status Bar:

• Circular References: C3 (or any other cell reference) when you’re on a worksheet containing circular formula in the workbook.

• You’ll only see Circular References if you’re on a worksheet that doesn’t contain any but the workbook has a few of these circular formulas.

How to Find Circular References in Excel: Using Error Checking

It’s the best way to check for circular references in a workbook or worksheet. Here are the steps you must know:

• Open a worksheet that you suspect of containing circular formulas.
• Click the Formulas menu on the Excel ribbon.

• In the Formula Auditing command group, click the Error Checking drop-down list.
• Hover the cursor over the Circular References option to see all the available instances in all worksheets.
• Select each instance and Excel will pinpoint those cells automatically.

How to Find Circular References in Excel: From Formula Tracing

If you don’t find the Error Checking method efficient enough to detect large numbers of formulas with circular references, you can try these steps to use the formula tracing method:

• Select any referenced cell or the formula-containing cell.
• Go to the Formulas tab on the Excel ribbon menu and then click Trace Precedents.
• Excel will highlight the cells that affect the formula result on the current cell with arrows.

• You can also click Trace Dependents to discover cells impacted by the value of the selected cell reference.
• If Excel doesn’t show any errors after you click the above commands, there are no circular references in your worksheet.
• Repeat the steps on all worksheets to check the entire workbook.

This method is particularly important when the iterative calculation features are active on the Excel worksheet.

How to Find Circular References in Excel: Using Excel Options

• Click the File tab on the Excel ribbon and then open Excel Options from the sidebar that shows up.
• On the Excel Options dialog box, select the Formulas menu from the sidebar and then uncheck the checkbox for Enable iterative calculation.

• Click OK to save the changes.
• Now, the workbook or worksheet will automatically show you the circular reference warning.
• If it doesn’t show instantly, save the workbook, close it, and reopen it to see the warning.
• Once you see the warning check the Excel Status Bar to know which cell is in the circular reference.

How to Resolve Circular References in Excel

The sureshot way to correct one or many circular reference errors on Excel is by manually editing each formula that contains it. In this way, you can resolve both accidental and indirect circular references in Excel.

Another easy way is to remove the formula from the current cell and place it somewhere else within the same worksheet.

How to Find Circular References in Google Sheets (Bonus Method)

Like Excel, Google Sheets doesn’t allow any circular references in one or more formulas if you haven’t activated the iterative calculation feature. Hence, Google Sheets will immediately show the #REF! error on the cell that contains a circular formula.

Therefore, you can simply select all the cells that contain the #REF! warning to find and resolve circular references on Google Sheets.

Excel Circular References: Final Words

So, now you know all the secrets for how to find circular references in Excel. Use the method you like and ditch all the circular references to ensure you don’t get confused by unreliable data from an Excel report or mathematical dashboard.

Next up, the best Excel calendar templates for any year and the best Excel budget templates.