How to Fix Circular Formulas in Excel

How to Fix Circular Formulas in Excel--Directly Referenced Cell
1.
To demonstrate this type of circular formula and the resolution of the problem a simple bank reconciliation spreadsheet is used. Set up an Excel spreadsheet as follows: in row 1 label Column A: Balance, Column B: Debit, Column C: Credit, Column D: To, Column E: Reference, Column F: Date and Column G: Cleared Bank.
2.
In row 2 enter: a $500 credit in the 'Credit' column, 'Deposit to open account' in the 'To' column, and a date in the 'Date' column. In the 'Balance' column on row 3 enter the following formula: =A3 C2-B2, with the intent that this formula would take the prior balance, add any deposits and subtract any debits. When you enter this formula you will get an error message stating that a circular reference has been created. You can either select 'OK' or 'Help' to fix the reference yourself or 'Cancel' to ignore the circular reference. If you select 'OK' or 'Help' a pop-up window will open that will give you more information about circular references.
3.
In addition to the pop-up window that provides more information about circular references a toolbar opens when you select 'OK' or 'Help.' This is the Circular Reference toolbar. If you select the drop-down arrow on the toolbar it will show all circular references in the current workbook. You can go directly to the circular reference by selecting it from the drop-down list.
4.
Fixing the circular reference can be done by first selecting the cell and then placing your cursor in the formula bar. This color codes the formula to the actual cells on the worksheet that are referenced in the formula. In this example, you can see that the cell A3 references itself in the formula.
5.
The algebraic equivalent to this would be the formula: x = x y - z, which would only hold true in one instance, where y and z = zero. To correct this formula, change the formula in A3 to reference cell A2 instead of cell A3. This can be done by typing directly into the formula bar, dragging the colored border that is around cell A3 so that it is around cell A2 or retyping the formula.
How to Fix Circular Formulas in Excel--Indirectly Referenced Cell
6. To demonstrate indirectly referenced circular formulas, we have simply added a column to the practice worksheet and labeled it 'Interest Paid.' In row 2 of that column enter the following formula: =A3*0.03, with .03 representing interest paid on the balance. In row 3 in the Balance column enter the following formula: =A2 C2-B2 D2. The algebraic equivalent to this would be the formula: x = w y z (x *.03).
7.
When you enter this formula you will get an error message stating that a circular reference has been created. You can either select 'OK' or 'Help' to fix the reference yourself or 'Cancel' to ignore the circular reference. If you select 'OK' or 'Help' a pop-up window will open that will give you more information about circular references.
8.
In addition to the pop-up window that provides more information about circular references a toolbar opens when you select 'OK' or 'Help.' This is the Circular Reference toolbar. If you select the drop-down arrow on the toolbar it will show all circular references in the current workbook. You can go directly to the circular reference by selecting it from the drop-down list. Since this is an indirectly referenced circular formula an arrow will point from the cell that has the circular reference back to the cell that refers to that cell. In this example, the arrow points from A3 back to D2, indicating that A3 refers to D2 for its calculation and that D2 refers to A3 for its calculation.
9. To correct this formula, change the formula in D2 to reference cell A2 instead of cell A3. This can be done by typing directly into the formula bar, dragging the colored border that is around cell A3 so that it is around cell A2 or retyping the formula.