When a formula refers to its own cell, either directly or indirectly, it creates a circular reference. In this tip we explain how you can locate and remove a circular reference. The screenshot below will be used for this example.
If you only want to copy the month down, e.g. January 2022, hold down Ctrl and drag the Fill Handle to the required cells. To find the total sales commission the formula would be: =SUM(E2:P2
If by mistake you included column Q in your formula as per below example. This will create a circular reference because the formula lives in cell Q5 and it is trying to calculate itself.
An error message will pop up indicating that you have a circular reference.
You will notice that you are now not getting an answer to your formula.
There are various ways you can find the error.
Click on cell Q5 and click on the function button F2. This will bring up your formula. You will then be able to trace the formula and see that you have erroneous included column Q.
2. Click on cell Q5 and select Formulas, then on the arrow next to Error Checking
You will see that a selection appears, select Circular References. You see that the cell that contains the error appears.
In this instance correct the formula eg : =SUM(E5:P5)
3. You can also look for the area via Formulas and Trace Precedents. Remember that your cursor needs to be on the relevant cell.
Once selected, the cell with the identified error will be highlighted
As soon as the error has been corrected, the blue highlight will disappear.
