How to Fix Circular Formulas in Excel
Excel has many built-in formulas that can reduce calculating errors, ease calculations and standardize worksheet calculations. Sometimes a circular reference error can occur when you are using a standard formula or writing a custom formula. A circular formula, referred to as a circular reference in Excel, is when the formula in a cell refers back to itself in its calculations. This means that the cell needs to know its own value to make the calculation; and since the value of the cell is based on the calculation, it is impossible to know the value prior to the calculation being made. When the cell is directly referenced it is easier to identify the circular reference than when the cell is indirectly referenced, such as when the value in the cell is used in a calculation for another cell.
- Difficulty:
- Moderate
Instructions
-
How to Fix Circular Formulas in Excel--Directly Referenced Cell
-
1
Spreadsheet setup
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
Circular reference error message
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
Circular reference toolbar
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
Direct circular reference
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
Corrected direct circular reference
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
-
1
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).
-
2
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
Indirect circular reference error with arrows
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.
-
4
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.
-
1
Tips & Warnings
In spreadsheets that are heavy in formulas it can be difficult to identify where the circular reference occurs. Make use of the drop-down menu that lists any circular references on the circular reference toolbar to quickly locate circular references.
Whenever you are changing formulas make sure that you are referencing the correct cells in your new formula. An easy way to visually see what cells are referenced in the formula is to select the cell containing the formula and then put your cursor in the formula bar. This will place a colored border around each cell that is part of the formula. The colored border will correspond to the color of the cell reference in the formula.
