How to fix circular reference in excel
Excel circular reference occurs when you use the output of one formula as the input to another formula and this causes the formula to return an #REF! error. The data must be connected in such a way that the output from one formula becomes the input of another formula and so on, hence creating a loop or circle in the flow of data through different formulas that in turn forms a circular reference error.
This is one of the most common errors faced by users and most often appears when using relative or absolute references in formulas. However, there are several methods to fix circular reference errors in excel and avoid this kind of situation as well.
What is Circular Reference
A Circular Reference is when one cell refers to another cell, and that other cell refers back to it. This can happen in several ways but it occurs most often when you are referencing data within a table that contains multiple sheets.
In its simplest form, if you have a sheet of data called Sales, you might have a series of cells on that sheet referring back to other sheets which contain data relating to each sale (salesperson, date sold, etc).
There are many times when we do not want our data or calculations to be reliant on outside information (such as sales reports from others departments) because there may be delays before those figures become available.
The easiest way to work around a Circular Reference is, when creating your spreadsheet, to avoid using any values from other worksheets. For example, instead of referring back to data on another sheet (Sales), you could create another sheet called ‘calculations’ and then apply those formulas there.
This way all your worksheet calculations are contained within a single sheet. The only thing you will need is some kind of value that allows you to populate or set that first row of ‘calculations’.
Common Causes of Circular References
What are the common causes of this error? let’s get a look at some;
- The first and most common cause of circular references is using a formula that refers back to one of its own cells. For example, try entering formula =A2+B2 into cell C1. That will give you an error because A2 refers back to C1, which makes it a circular reference.
- Another very common way that people accidentally create cycles is by copying formulas down a column (or across a row). This can also be fixed simply by breaking the cycle somewhere else in your worksheet.
- Sometimes there’s no way around having a looping pattern like that and you need to break out of it.
- A fourth way is by using a relative or absolute reference and not completing that cell. For example, try entering =SUM(A2:A4) into cell D1 without finishing off your range. That will also create a cycle.
- A fifth way is by copying a cell, then deleting it, and leaving its formula behind. For example, try copying cell C3 without finishing off your range (by hitting Enter or clicking out of it). Now delete that copied cell and you’ll still have its formula hanging around. Change it back to whatever makes sense for your situation.
How to Identify a Circular Reference Error
From there, check for relative references between two cells on different worksheets that reference each other. A yes answer would mean that you have a circular reference error.
Next, determine if you have a single circular reference or multiple circular references. A single circle will appear when you have one looped cell and multiple loops come from that same cell; multiple circles happen when there is more than one looped cell. Look for common denominators among these circles, such as an entire row of your spreadsheet.
Fixing the issue manually
We can manually look at each cell and figure out what references it. The first step is to determine whether it’s a circular or an indirect reference. A regular indirect (or non-circular) referencing occurs when your formula refers to a cell that contains another formula that refers back to one of your original cells.
In these cases, you need to identify how far back you need to go in order for all formulas to not be dependent on one another. Once that’s been determined, you can use a couple of methods: Breaking a cycle: As an example: A1 has a formula =A1+$B$6 If we change it to =A1+$B6 then we remove B6 from our dependency loop since that cell is no longer needed in order for your formula to compute. So…now there’s no circularity and the formula will return expected results.
You can do something similar if there are several cells that are being referenced by many other cells, e.g., $C$3. Just replace some of those numbers with hard-coded values (like 0) and now everything below relies only on itself, allowing your spreadsheet to work correctly.
Another option would be to create arrays which are excel constructs designed specifically for mathematically understanding relationships between cells more easily. For example, if cell G7 was A2 * 2, you could change G7 into G3:G11 which makes array operations much easier than trying to count up 7 rows every time an operation occurs.
Can Excel correct a circular reference for you?
If you’re on an older version of Excel and creating a circular reference manually, you won’t get an error message if you attempt it. Instead, Microsoft will simply refuse to save your file—and that means it won’t show up if someone attempts to open it.
You can get around that problem by opening your workbook in a later version of Excel or by creating your own code. Microsoft doesn’t provide a solution built into Excel because most files should not have a circular reference.
If you need one, consider taking it up with an Excel developer. For now, though, if you want to manually create a circular reference then you need to take these four steps:
- First, set up your data table so it looks like Figure A and assigns values from 1 through 5 in each row and column.
- enter your formula in cell A2 (in our example, =A1). Next, highlight all values except for A2 and drag them down until they wrap around.
- Finally, change your formula cell from A2 to B2 (in our example, =B1) and drag down all values that are highlighted except for B2. If you’ve done everything correctly, you’ll see 1 through 5 for each row and column and it will display: Alert! Circular reference detected. Click here to solve circular references in Excel before saving or printing these cells.
This can be helpful if you’re working on a large table of data. It warns users that they may encounter errors and helps them correct them before they attempt to save or print their workbooks.
However, it doesn’t allow you to correct a circular reference automatically, so Microsoft recommends editing manually. If you do run into an error when trying to create a circular reference manually, try these tips
How do you unlock cells in Excel?
There are two primary ways you can do it. The first is relatively easy, but also extremely time-consuming: Change each cell that references other cells so that it’s referencing itself.
To do that, click on an empty area of your worksheet and press CTRL+A (to select all), and then press CTRL+SHIFT+ENTER (to enter each cell).
Alternatively, you can use Find & Select; just make sure to uncheck Match formula results on a case-by-case basis while hunting down all those pesky circular references. Both options are tedious as hell, but—unfortunately—they’re also pretty effective.
PEOPLE ALSO READ: Beats Studio Buds Review