How to Fix a Circular Reference Error in Microsoft Excel



RECOMMENDED: Click here to fix Windows errors and optimize system performance

In a Microsoft Excel spreadsheet, you may have formulas in one cell that require the information in another cell for calculation. These relationships are called references. If a cell refers to itself or to another cell that refers to the original cell, the formulas cannot be completed because they require a result that depends on the original cell. This event is called a circular reference, and if you have a large spreadsheet with thousands of formulas, a circular reference can cause the entire sheet to be calculated incorrectly.

You have entered a formula, but it doesn’t work. Instead, you get this message about a “circular reference”. Millions of people have the same problem, and it’s because your formula is trying to calculate itself, and you have disabled a function called iterative calculation. This is what it looks like:

The formula =D1+D2+D3 breaks down because it lives in cell D3 and it’s trying to calculate itself. To solve the problem, you can move the formula to another cell. Press Ctrl+X to cut the formula, select another cell and press Ctrl+V to paste it.

Another common error is to use a function that contains a reference to itself; for example, cell F3 =SUM(A3:F3).

How to Find Circular References in Excel

To check if your Excel workbook contains circular references, follow these steps:

  • Go to the Formulas tab, click the arrow next to Error Check and point to Circular References This displays the last circular reference you entered.
  • Click the cell listed under Circular References, and Excel takes you to that exact cell.
  • When you do so, the status bar informs you that ring references have been found in your workbook and displays the address of one of these cells.


January 2021 Update:

We now recommend using this tool for your error. Additionally, this tool fixes common computer errors, protects you against file loss, malware, hardware failures and optimizes your PC for maximum performance. You can fix your PC problems quickly and prevent others from happening with this software:

  • Step 1 : Download PC Repair & Optimizer Tool (Windows 10, 8, 7, XP, Vista – Microsoft Gold Certified).
  • Step 2 : Click “Start Scan” to find Windows registry issues that could be causing PC problems.
  • Step 3 : Click “Repair All” to fix all issues.

download



If circular references are found in other sheets, the status bar displays only “Circular references” without the address of the cell.

Note. This function is disabled if the “Iterative calculation” option is activated. You must switch it off before consulting the workbook for circular references.

How to Remove Circular References in Excel

If a formula directly refers to its own cell or finally refers to its own cell via a string of references to other cells, Excel will give you a circular reference warning. The reason for this is that the formula cannot be calculated in the usual way because it can affect its own value when it is updated.

Often this is a sign of a typing error or a logical error, and you will want to find out what creates the circular reference and eliminate it.

  • Open the spreadsheet
  • Use the Formulas menu
  • Search for a circular reference
  • Tracking precedents with Excel
  • Follow the reference chain
  • Find other circles according to your needs
  • Iterative calculation on Excel

Activating the Iterative Calculation Function

  • Open Microsoft Excel
  • Select File and go to Options
  • Select formulas
  • Check the box next to “Enable iterative calculation

Moving the Formula Manually to Another Cell

If you have a simple calculation, e.g. A1 + A2 + A3 = A4, but want to set the sum formula to A1, A2 or A3 instead of A4, this will trigger the reference error.

In this case, simply select a different cell for your formula without overlapping with cells that already contain numerical values.

Conclusion

If you follow the steps below, you should be able to correct any circular reference errors and continue to use Microsoft Excel accordingly.

Which method worked best for you? Let us know by leaving us a message in the “Comments” section below.

https://support.microsoft.com/en-us/office/remove-or-allow-a-circular-reference-8540bd0f-6e97-4483-bcf7-1b49cd50d123



RECOMMENDED: Click here to troubleshoot Windows errors and optimize system performance