Ask Sawal

Discussion Forum
Notification Icon1
Write Answer Icon
Add Question Icon

Excel where to find circular reference?

5 Answer(s) Available
Answer # 1 #

You’ll see circular reference warnings for any formula you try to insert into Excel that loops back to itself. For instance, if you’re analyzing data with a SUMIF formula, and one of the cells in your data set is referencing the output from the same cell containing that formula, Excel can’t calculate the result, causing this error to appear.

However, there is one way you can use circular references in Excel to your advantage. If you want to insert an iterative calculation (one that repeats itself a limited number of times until certain conditions are met), you might want to use a circular reference formula, but you’ll need to tweak Excel’s settings to allow it.

Whether you’re looking to use circular references or avoid them entirely, this guide will explain:

Are you struggling with circular references in Excel? Then keep reading!

For most users, a circular reference in Excel is problematic. Calculating the result of a formula, when the cell containing that formula is part of the calculation, results in an endless loop that Excel can’t complete.

Let’s use a very basic example. Let’s assume you have two cells: A2 and B2. Both contain references to the other cell, with A2 containing =B2, and B2 containing =A2. This is a circular reference, as the “formula” can’t be completed.

There isn’t an answer for Excel to output, so it displays an error. A blue arrow connecting the two cells offers a graphical representation of the problem.

This also happens if, for example, you reference data in a cell that relies on the result of your first cell. For instance, A2 contains =B2, while B2 contains =C2. However, C2 contains a simple calculation, =A2-1. Because the overall result relies on the output of the initial cell (A2), this still results in an error.

Loops like these aren’t ideal for users working in an Excel workbook, largely because the excess calculations result in excessive memory usage (although the example above, by definition, wouldn’t result in any useful output anyway).

Excel won’t try to give you a result—the warning is sufficient to stop the error. Few users will want to disable it, but there is one advantage to doing so. If you decide you want to create a static result using functions that would otherwise change regularly, such as a timestamp, you could disable circular referencing to do so.

This is done by enabling iterative calculations, which are calculations that are repeated until certain conditions are met. Combined with certain logical functions, such as an IF function, you can turn otherwise volatile formulas into static ones that don’t update. This requires a bit of tweaking to Excel’s default settings, however.

Excel will throw up occasional errors with this kind of setting enabled, such as a zero output after quickly switching between selected cells. Where possible, it’s recommended to avoid this kind of scenario, or (at the very least) to limit the number of formulas where iterative calculations could cause data validity issues.

Most users will want to avoid circular references appearing in their Excel workbooks. This is usually because it breaks the formula, preventing it from outputting a result (although a circular reference is, in many cases, a barrier to a result in the first place).

To get around this problem, you’ll need to locate and remove unwanted circular references in Excel. There are a couple of ways you can do this, from spotting the error symbol on certain cells to using Excel’s error checking system to locate them for you.

When you insert a formula that contains a circular reference in Excel, it will usually warn you. A pop-up will appear, along with a warning symbol alongside the cell itself. However, this pop-up and warning symbol won’t always appear if you’ve inserted multiple circular reference formulas into your workbook.

This can make finding each incorrect formula difficult, especially for larger data sets. To get around the problem, press the Formulas tab on the ribbon bar, then press the downwards arrow icon next to the Error Checking option. From the menu, hover over the Circular References option.

In the pop-up menu, you’ll see a list of absolute cell references to cells that contain formulas that cause circular references. Clicking on any of these cells causes Excel to find and select it in your workbook. You’ll also see the cell reference listed in the status bar at the bottom of the window.

Once you’ve located the cell, you’ll need to alter it to remove the circular reference and repeat this for each example of the problem in your workbook. Removing circular references from formulas will allow Excel to correctly return a result.

Circular references are usually a sign that Excel is working as intended. If Excel tried to return a result where a circular reference existed, it would cause excessive memory usage and, depending on the formula, return incorrect results.

As we’ve mentioned, however, there is a limited exception to this rule where circular references are desirable. If you want to create static results from so-called volatile functions (such as time or date functions), you can use iterative calculations to do so.

Rather than creating a function that endlessly loops (potentially without success) to create a result, iterative calculations loop for a finite period of time. If the result is met, this creates a static result that doesn’t update. Excel isn’t really designed for this scenario, but it can work—just expect the odd error here and there.

If you want to enable iterative calculations in Excel 2010 and later, start by opening the Excel workbook containing your data. On Windows PCs, press File > Options, then select Formulas from the left-hand menu. Mac users should press Excel > Preferences > Calculation instead.

In the Formulas or Calculation menu (depending on your operating system), click to enable the Enable iterative calculation option. You’ll have two options you can change: maximum iterations and maximum change.

The maximum iterations value determines how often the formula should evaluate the result and potentially recalculate. Limiting or increasing this can increase or decrease the available time for a result to become viable. If a formula’s criteria isn’t met after the maximum value is passed, Excel will return zero as the result.

The maximum change value determines the accuracy of the output result, letting you decide how much a formula’s output can change before it stops recalculating. For instance, if the original value is 10 and the maximum change value is 0.1, and the value then changes to 10.1, Excel will stop recalculating, even if the maximum iterations value isn’t reached.

By default, Excel sets the maximum iterations value to 100 and the maximum change value to 0.001. You can increase or decrease these values to suit your own requirements.

To save the setting, press OK (on Windows) or close the settings menu (on Mac). The setting will apply to your open workbook specifically, and you’ll need to repeat these steps for any other workbook you wish to use.

If you’ve enabled iterative calculations in Excel, you can insert a formula that uses circular references into your Excel workbook. There’s no set formula to use, but we’ll demonstrate how it could prove effective by turning the output from volatile functions that update regularly (such as dates, times, or random numbers) into static results.

As we’ve mentioned, however, this kind of scenario has a very limited use case. Most users will probably want to avoid circular referencing like this to ensure the validity of your data. If you want to use it, however, you can follow these steps:

Before you begin to insert a new formula, you’ll need to create a workbook and insert your data. For instance, you could create a table with a list of student names, where an iterative calculation could be used to show when a submission is received by creating a static timestamp.

This is only one potential use case—you’ll need to determine the right data to use for your own workbook. Once you’ve inserted your data, you can move on to the next step.

With your initial data inserted, you can begin to insert a new formula. To create a formula that creates static results from volatile results in a specific way, we’ll be creating a nested formula. This is a formula that combines more than one function.

To start, select an empty cell using your mouse. With the cell selected, press the formula bar at the bottom of the ribbon bar until you see the blinking cursor appear.

Once the blinking cursor is visible, you can begin to insert your formula.

Logical functions (such as IF, SUMIF, OR, etc.) allow you to create formulas that only become visible when an argument is TRUE or FALSE. Combining this with a volatile function in a workbook with iterative calculations enabled allows you to create a static result from them.

Using our student lists example, we can create a static timestamp when an exam submission is received for each student. We’ll be using a nested IF formula to do this, but you could use any number of similar functions.

Logical tests like IF usually have three components: the test, the value if it’s true, and the value if it’s false. In this instance, the test is determining whether the rows in column B (for exam completion) have a YES or NO value. This is a TRUE/FALSE logical test. If YES is true, the static timestamp will appear, otherwise it won’t.

To create a function like this, type =IF(B2=”YES”,IF(C2=”“,NOW(),C2),””).

The IF formula in cell C2 checks whether the cell in B2 is equal to the text string YES. If it is, another nested IF formula checks to see if C2 is empty.

If it is, it’ll display a current timestamp using the NOW function, otherwise it’ll return an empty string (essentially a clever way to show the cell is empty). With iterative calculations active, this means that, when you insert YES into cell B2, the NOW() function will stop updating, creating a static timestamp.

You can copy and paste your own formula into each cell to fill your column or press the green square icon in the bottom right corner of the selected cell to fill the column automatically.

The purpose of this example formula is to demonstrate how iterative calculations can be used to create static results from volatile functions. NOW() is one example to show the current date and time, but you could also use RAND() for a random number, TODAY() for today’s date, etc.

You’ll need to first satisfy the conditions for your nested logical formula, however. To do this, select one of the cells in your test column, then insert the correct value for your formula test to become TRUE or FALSE, depending on your own formula.

In this instance, the cells in column B must contain YES. When you insert YES, the logical test becomes TRUE, creating a timestamp that, thanks to iterative calculations, won’t update.

This is only one potential example. You can modify the steps above using your own data, different logical test formulas, and different test criteria to create static results from volatile functions that suit your own data.

If you’re seeing circular reference warnings in Excel, it’s probably best to go through and fix them. However, if you’re keen to use circular references, you can combine volatile functions with logical tests to create static results using iterative calculations. Just remember that this isn’t how Excel is meant to operate, and can result in errors.

Watching out for circular references in your data is just one of several essential skills that new data analysts should watch out for in Excel. If you’ve already mastered the essential Excel formulas, you can take on a five-day short course to identify the other skills and experience you’ll need for a successful career in data analysis.

Farhad Nebbou
Answer # 2 #

You've tried to enter some formula in your Excel sheet, but for some reason it's not working. Instead, it tells you something about a circular reference. Is this how you ended up on this page? :)

Thousands of users are facing the same problem on a daily basis simply because of forcing an Excel formula to calculate its own cell. When you try to do this, Excel throws up the following error message:

"Careful, we found one or more circular references in your workbook which might cause your formula to calculate incorrectly."

To put it simply, what Excel is trying to say is this: "Hey, I may get stuck at the round-about. Are you sure you want me to proceed anyway?"

As you can understand, circular references in Excel are troublesome, and the common sense says to avoid them whenever possible. However, there may be some rare cases when an Excel circular reference is the only possible solution for the task you are faced with.

Venush Sharma
Answer # 3 #

A circular reference in Excel occurs when a formula directly or indirectly refers to its own cell. This is not possible.

Let's start simple. A direct circular reference in Excel occurs when a formula directly refers to its own cell.

1. For example, the formula in cell A3 below directly refers to its own cell (A3). This is not possible.

2. Press Enter. The circular reference error message appears.

3. Click OK. Excel returns a 0.

An indirect circular reference in Excel occurs when a formula indirectly refers to its own cell. Let's look at a simple example.

1. For example, cell D2 below contains the value 25.

2. Cell F4 refers to cell D2.

3. Cell C5 refers to cell F4.

4. So far, everything's OK. Now replace the value 25 in cell D2 with the formula shown below.

5. Press Enter. The circular reference error message appears.

6. Click OK. Excel returns a 0.

7. On the Formulas tab, in the Formula Auditing group, click Show Formulas.

8. Cell D2 refers to cell C5. Cell C5 refers to cell F4. Cell F4 refers back to cell D2.

Conclusion: the formula in cell D2 indirectly refers to its own cell (D2). This is not possible. The formulas in cell C5 and cell F4 also refer back to their own cell (it's a circle).

To find circular references in Excel, in the Formula Auditing group, click the down arrow next to Error Checking and click Cell References.

1. Our sheet contains 2 circular references. 1 direct circular reference (A3) and 1 indirect circular reference (D2, C5 and F4).

2. On the Formulas tab, in the Formula Auditing group, click the down arrow next to Error Checking.

3. Click Circular References. Excel shows the cell address of one circular reference.

4. You can also look at the status bar. This is much easier.

5. Fix this circular reference.

To find more circular references, repeat these steps.

6. On the Formulas tab, in the Formula Auditing group, click the down arrow next to Error Checking.

7. Click Circular References. Excel shows the cell addresses of the indirect circular reference.

8. The status bar only displays a single cell address.

Note: fix this circular reference by replacing one of the formulas with a value. As a result, the warning in the status bar will disappear.

Uzma yopuhuk
Answer # 4 #

The status bar in the lower-left corner displays Circular References and the cell address of one circular reference. If you have circular references in other worksheets, but not in the active worksheet, the status bar displays only “Circular References” with no cell addresses.

Asjha Bologna
Majorette Dancer
Answer # 5 #
  • Go to File > Options > Formulas.
  • Put a checkmark in the Enable Iterative Calculation box under “Calculation Options”.
Daily udyggkks