When moving from cell to cell on Microsoft Excel, perhaps highlighting and copying content into rows and columns, it’s easy to make a mistake and paste an unwanted formula into a cell. This problem is compounded if you accidentally create what’s known as a “circular reference”.
What is a circular reference?
On Excel, formulas can be entered into a cell that use the data in one or more other cells. As a straightforward example, the formula =A1+B1 works fine if entered in cell C1. You’re simply asking it to add together the numbers in cells A1 and B1 and display the answer in cell C1.
However, it becomes a circular reference if you force a cell to include itself in a calculation. If you enter =A1+C1 into cell C1, Excel can’t make sense of this because you’re asking the cell to add data to itself. This creates an infinite calculation (hence a “circular” reference as it goes on and on forever) and Excel will tell you it’s not happy about it with this error message.
This is easily understood if you only have a few cells filled in like in our example above, but if you have a huge spreadsheet with thousands of rows and columns, an error message telling you that there’s a circular reference somewhere within all that data is not terribly helpful. It smacks of Excel saying “you’ve done something wrong, but I’m going to let you figure it out for yourself”, which is just the way Microsoft rolls sometimes.
Luckily, it’s quite easy to find the needle in the haystack once you know what you’re doing. Here’s how to find the irksome circular reference and get rid of it:
How to find the circular reference
1. Go to ‘Formulas’ on the ribbon at the top of the screen
2. Find the ‘Error Checking’ button that appears underneath the ribbon. You might have to maximise Excel to full screen to see it. It has a picture of a yellow warning triangle with a tick next to it
3. Click the drop-down arrow next to ‘Error Checking’. One of the options you should be able to see is ‘Circular References’
4. Hover over ‘Circular References’ and it will show you the offending cell(s). You can click these to be taken straight to the cell
5. Remove the circular reference from the cell
Knowing what you’re doing on Microsoft Excel is a real virtue, but equally important is knowing how to rectify mistakes like this, since even the most accomplished spreadsheet whizzes make them.
- How to find a circular reference on Excel - May 23, 2024
- Five life skills learned from internet marketing - January 3, 2024
- How artificial intelligence can (and can’t) help you write content - September 29, 2023
