Tweet

# Excel For Noobs Tutorial: Circular Reference and Enabling Iterations to Fix Circular Reference Formulas

A circular reference error arises when a formula either refers to the cell it is inserted in or refers to another cell that refers to its own cell. For example, if we were to enter =A1+B1+C1 into cell C1 we would get a circular reference warning. This is because we entered a formula into cell C1 that refers to cell C1 as an argument. The problem with formulas that contain circular references is that every time the formula is calculated, it must be calculated again. This could go on infinitely.

When you enter a formula with a circular reference a warning box will appear as shown below.

When this box appears you can click OK and Excel will just display a 0 as the result of your formula, or you can click Help and Excel will display a help screen providing more information about circular references.

There are times when circular references are necessary. For example, suppose you owned a company and your employees’ salaries were a percentage of net profit. Since net profit is calculated as Revenues – Expenses and salaries are an expense, we would run into a circular reference problem. We must know what net profits are before we can calculate salaries and we must know salaries before we can calculate net profits. In this case we can use circular references to our advantage. Examine the data and formulas in the Excel screenshot provided below. I am using the show formulas feature so you can see what exactly is entered into the cells. As you can see we have a circular reference problem because Net Profits in cell B4 is =B1-B2-B3 but cell B3 contains a formula that refers to cell B4.

The formulas entered into this worksheet are resolvable formulas and if the formula is repeated enough times a desired result can be reached for both cell B3 and cell B4. To solve this circular reference problem we simply need to enable iterative calculations. To enable iterative calculations follow the steps below:

Step 1: Select File and then click on Options. The Excel options dialog box will appear.

Step 2: In the Excel options dialog box click the Formulas tab.

Step 3: In the Calculation Options group check the box where it says Enable iterative calculation. Now Excel will run the maximum allowed iterations until it reaches a result where Salaries are exactly 10% of Net Profit. Look at the screenshot below to see the final result.

Find the next tutorial in the links below.