Q. What is a circular cell reference and how can I prevent them?
A. A circular cell reference results when a formula refers back to the cell it is being typed into. For example, if you are typing the formula A1+B1+C1 into C1, you will get a circular cell reference. Unfortunately, it’s not always that straightforward. Sometimes, your formula is just A1+B1, but B1 contains a formula that refers to another formula that refers to C1.
Don’t despair; there are some tools to help you. On the Formulas tab in the Formula Auditing group, you’ll find the Evaluate Formula button. You can evaluate each part of the formula. When it arrives at the part that is a circular reference, it will tell you this in the dialog box and italicize the value in the Evaluation box. Now, you can use Trace Precedents to find where the offending cell is located.