Home / Formulas / Circular Reference Warning

Circular Reference Warning

The ExcelHelpDesk Support team received the following request for help

Problem – Circular Reference Warning

Problem with circular formula.
In column H I have various sums-e.g. H9-H13. At H14 I have the total of sums in H9-13. This is repeated down the H column. So far so good. In column J I want a column of totals. So at J14 I want to reproduce what is at H14. I therefore inserted “=H14” at J14. J14 keeps evaluating this as 0. Indeed, whatever formula I put in column J comes out as 0.
I have used this exact formula in a different spreadsheet with no problems. Why therefore is it not working here?

The following message box was presented to Julian when opening the workbook with the circular reference

Solution – Find the circular reference and resolve it

On opening the workbook in Excel 2007 the cell H17 was identified with the Circular Reference. The formula in that cell was “=SUM(H13:H17)”. The cause of the Circular Reference in this cell was due to the cell H17 being included to the SUM formula when it was also the result cell.

Simply by changing the formula only to sum those cells in the range outside of cell H17 the Circular Reference was removed. The formula was changed to “=SUM(H13:H16)”

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment

About Excel Help Desk

Check Also

INDIRECT to extract a value from another worksheet

You can see that we have the Worksheet name in Column A and the value that is needed to be extracted in Column D. The supplied worksheet included a manually entered formula to pull through the value from the other worksheet. We will now demonstrate how to create a formula using the INDIRECT function that will automatically pull through values from other worksheets without manually entering each worksheet name and cell reference.

One comment

  1. outsource excel

    Definitely follow this guide. Take it from someone who knows – circular references are awful, not only because they affect data integrity, but because having several of them can significantly slow down your workbook.