Have you ever been working in a spreadsheet and seen #REF! in a cell that should display the result of a formula calculation? It’s called a circular reference, and it happens to the best of us.
What Is a Circular Reference?
A circular reference error indicates that the cell where the output should appear has been included in the formula. In other words, the formula refers to its own value and is therefore impossible to calculate. Here is an example where I am adding numbers in a column, but I have included the cell with the total number in the calculation:
Looking at the formula on top, it includes cell A6, which is the output cell. Because it doesn’t have a value beyond the formula, Excel/Sheets can’t compute and throw up a circular reference area.
How to Fix It
The good news is, these errors are usually easy to fix. There is a tiny flag in the formula cell that contains a note to name the error and offer to help. In my example above, I only need to edit the formula to exclude cell A6. When I hover over the cell, this error message pops up:
It’s not very helpful. I followed the instructions and turned on iterative calculation here:
But it’s clear that iterative calculation did not solve the circular reference. This total is very wrong:
If I simply include cells A2:A5 instead of A2:A6, the new formula is error-free and shows a properly calculated total in the last cell:
Additional Context
The screenshots above are from Google Sheets. Microsoft Excel has a more robust error correction workflow than Sheets. The folks at Tech-Talk have created an article and video that provides more detail about correcting circular references in Microsoft Excel.
If you’d like to learn more about Google Suite and Microsoft Office, check out more of my posts or see more from Tech-Talk about Excel/Sheets, Word/Docs, PowerPoint/Slides, and Office/Office 365..
