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