Last week I covered the definition of the circular reference error, and what it looks like in various spreadsheet programs. This week I’ll cover how to track down the source of the problem, and discuss some common causes/solutions.
So, now that you’ve got a “circular reference error”, and you know what it means, how do you fix it? The first step is to actually find the error. Sure, you might know that cell A1 is involved, but the error might well be larger than that. If A1=A2+1, and A2+1=A3+1, and so on until a100=a1+1 that’s a pretty big loop. It’s a lot like looking for the actual knot in a mass of yarn. Most of it’s probably fine, but you need to check all of it carefully to find the actual problem. And if you just go yanking randomly, you’re going to end up with a worse mess than when you started.
Microsoft Excel 2007 has a moderately useful tool that can be good — it’ll generate a list of all the cells in the loop. You don’t know, of course, which one has the mistake that’s causing the loop, but at least you don’t have to check all the other cells that are referenced by the cells in the loop. Excel 97 has a somewhat less useful tool that’ll create blue arrows pointing to all the other referenced cells — great in theory, but it doesn’t narrow down the problem and I generally find the arrows more trouble than they’re worth.
Most other programs don’t even go that far to help you. So if you need or want to track down the problem on your own, then the steps below will allow you to find it.
- (Optional) Scream/yell at the computer. It won’t actually help anything, but it might let you vent your frustration before you get on with the rest of the tedious steps.
- Look at the formula in the cell.
- Check to see if the cell references itself directly. Remember when checking this that it might be a normal cell reference, or a name. One common cause of a circular reference error is when the entire column is named, say, “Expenses”, and the top cell in the column tries to calculate the sum of all expenses with =sum(Expenses).
- Write a list of all the references in the problematic cell.
- Write down the definitions of all of the named references.
- Visit each of the referenced cells, repeating steps 3-5 until the error is found.
Once you’ve found the actual loop, then what? Fixing it usually involves understanding how it was created in the first place. Here are some common causes of circular references errors.
- Including yourself: If the top cell in a column is trying to reference all the cells below it, it’s pretty easy to accidentally include the top row as well. To fix this, just change the formula to exclude the cell it’s in. For example, if the formula’s in cell A1 change =sum(a1:a100) to =sum(a2:a100)
- Mistyping: Typing A1 instead of A11, for example. Even if you’re not in a1, you’ll still get this error if A1 references whatever cell the formula’s in. Here you just figure out what it’s supposed to be, and correct it.
- Circular logic: Sometimes you just forget that you’ve defined one thing by another.. Often this happens when you’re editing something you wrote considerably before, and you might find yourself saying “Hey, I could update this automatically if I just pulled the information from this other cell…” To fix this problem you probably want to revert to the earlier setup so the spreadsheet will stop complaining at you, and then sit down to decide which way you want it to be in the future.