Ah, Microsoft Excel. No matter how good you think you are at Excel, there is always some new feature to learn. New tricks and tips to get around to make you more productive. I have one right here. Here is how to find circular references in Excel. At its core, a circular reference is a formula that refers to its cell or another cell.
Finding it is quite easy. All you need to do is to click on the Error Checking from the Formula tab at the top of the ribbon. This will let you know all the cells that have circular references. But fixing them is not just a button click.
While circular references are generally a bad idea, in some cases, you might want to use them as well. These are reserved for advanced Excel users though.
Circular References: A Looping Menace
The annoyance of circular references in an Excel worksheet can drive any sane person crazy. But what is it?
Well, a circular reference is never intended. It is when a formula will refer to its own cell or another over and over again.
As you might imagine, this will give you incorrect numbers for that formula, effectively making it pretty useless. That is why if you are working with excel, you need to know how to pinpoint circular references.
This will allow you to fix errors in excel sheets in a jiffy. Also, imagine a co-worker sent you a worksheet. And something is just not right. You might want to double-check. In some cases, the numbers might not be adding up right because there is a sneaky circular reference there.
Circular references can create an endless loop. And as I already mentioned, it will give you the wrong results. The problem is greater than this though. A circular reference will also slow down your spreadsheet since it is just looping over and over again.
That is something you do not want to deal with when you have a large (very important) Excel worksheet.
The biggest problem is that circular references can be pretty difficult to find. But once you know how to locate annoying circular references in Excel, the job becomes pretty easy. You just need to know what to look for.
How to Find Circular References in Excel?
So, how do you find circular references in excel? The process is pretty straightforward. You just need to know what to look for. Microsoft is a pretty smart organization. And Excel is a pretty powerful tool.
Luckily, there are tools built right into Excel to help you find annoying circular references that are messing up with your worksheet.
Here is how to find circular references in your Excel worksheets:
Step 1: Navigate your cursor to the Formula tab on Excel. It should be on the upper ribbon. You will see three options – Show Formula, Error Checking, and Evaluate Formula.
Step 2: Click on Error Checking. You can also just click on the arrow on the right side of the Error Checking sub-menu. This will give you another drop-down menu.
Step 3: Click on Circular References. This will reveal the cells that have circular references. Then you just go in and fix them. Voila! It is that easy.
Finding circular references is somewhat easy. But the problem is removing them. Excel does not give you a simple button to remove all these errors. You actually need to go into the cells and fix it manually.
It can be time-consuming in some cases. But it is better for the long run. You have more control over what fixes you implement this way. You can either replace the problematic cells with a different and simpler formula or just get rid of them altogether.
Pro Tip: To see which cells are problematic. You can also check the status bar of your Excel worksheet. It is much easier to find the errors this way. You do not need to sift through the entire file. This is pretty useful if you are working with a large file that has many cells with formulas.
Types of Circular References: Are All Circular References Bad?
Many might not be aware that there are two types of circular references – a direct circular reference and an indirect one. A direct circular reference will directly refer to its own cell.
An indirect circular reference formula will indirectly refer to its own cell. You can check this article to know more about it. However, some circular references are intentional. I need to give you a heads-up here though.
Intended circular references are used by mostly expert Excel users. And they are used to make iterative calculations. The iterations will repeat till a number condition is met. By default, Excel will stop after 100 iterative calculations. Or if, all the values in a circular reference change by less than 0.001 between iterations.
Whichever of these scenarios comes first, Excel will stop the calculation. You have control over when Excel stops the count though. You can tell the software the maximum amount of change or iterations.
Some Handy Tips
Before wrapping up, I wanted to leave you with some handy tips that will help you even more. These are pretty short tips.
- If your circular reference is not on the active sheet and another sheet, then Excel will just display ‘Circular References‘ without any kind of cell number.
- The cell address of one circular reference can be seen on the lower-left corner status bar.
- You can even more between circular references. When you double-click the tracer arrow, the arrow will show you which cell’s value is affected. This guide from Microsoft can also help.
Wrapping Up
There you have it. Now, you know how to find circular references in Excel. They can be quite an annoyance but sometimes they can be used by advanced Excel uses for iterative calculations. So, use them carefully. Although, it is generally better to just avoid them.
