If you’ve used Microsoft Excel for any length of time then most likely you’ve inadvertently created a formula that results in a circular reference. This means that the formula you created includes the cell where the formula resides. In most every instance in Excel when we create formulas we refer to cells other than the one where our formula resides. However, there are situations where you may want to purposely create a circular reference.
You’ve most likely created an accidental circular reference by creating a SUM that includes the total row itself, as shown in Figure 1. The results that you see will vary based on your version of Excel:
- Excel 2013—A simple prompt will appear informing you that you created a circular reference, giving you the option to choose OK or Cancel. If you click OK, the formula appears in the cell but might display zero, as shown in Figure 1.
- Excel 2010 and earlier—You might have the impression that your computer is about to crash. Not only does Excel give you a warning prompt, but it also draws one or more arrows on the screen and displays a help screen.
About David Ringstrom, CPA
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.