Circular References in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2393384%22%20slang%3D%22en-US%22%3ECircular%20References%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2393384%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20I%20open%20a%20couple%20of%20my%20spreadsheets%2C%20I%20get%20the%20following%20warning%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%22%3CSTRONG%3EThere%20are%20one%20or%20more%20circular%20references%20where%20a%20formula%20refers%20to%20its%20own%20cell%20either%20directly%20or%20indirectly.%20This%20might%20cause%20them%20to%20calculate%20incorrectly.%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHow%20can%20I%20find%20the%20circular%20references%20to%20correct%20them%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EThanks.%20%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2393384%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2393626%22%20slang%3D%22en-US%22%3ERe%3A%20Circular%20References%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2393626%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1065145%22%20target%3D%22_blank%22%3E%40EverettMoran%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EActivate%20the%20Formulas%20tab%20of%20the%20ribbon.%3C%2FP%3E%0A%3CP%3EIn%20the%20Formula%20Auditing%20group%2C%20click%20the%20dropdown%20arrow%20on%20the%20right%20hand%20side%20of%20the%20Error%20Checking%20button%2C%20and%20select%20Circular%20References.%20You%20should%20see%20a%20submenu%20listing%20the%20cells%20that%20have%20formulas%20with%20circular%20references.%3C%2FP%3E%0A%3CP%3ESelecting%20a%20cell%20address%20in%20the%20submenu%20will%20activate%20that%20cell%2C%20so%20that%20you%20can%20inspect%20its%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2497633%22%20slang%3D%22en-US%22%3ERe%3A%20Circular%20References%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2497633%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20Hans.%20I%20just%20now%20have%20found%20your%20reply.%20What%20is%20the%20%22Formula%20Auditing%20Group%22%20and%20how%20do%20I%20find%20it%3F%20Also%2C%20these%20are%20workbooks%20that%20have%20multiple%20worksheets.%20Do%20I%20need%20to%20perform%20the%20%22formula%20audit%22%20on%20each%20sheet%20individually%20to%20locate%20the%20circular%20reference(s)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again.%20Everett%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

When I open a couple of my spreadsheets, I get the following warning:

"There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly."

 

How can I find the circular references to correct them?

 

Thanks.

4 Replies

@EverettMoran 

Activate the Formulas tab of the ribbon.

In the Formula Auditing group, click the dropdown arrow on the right hand side of the Error Checking button, and select Circular References. You should see a submenu listing the cells that have formulas with circular references.

Selecting a cell address in the submenu will activate that cell, so that you can inspect its formula.

@Hans Vogelaar 

 

Thank you, Hans. I just now have found your reply. What is the "Formula Auditing Group" and how do I find it? Also, these are workbooks that have multiple worksheets. Do I need to perform the "formula audit" on each sheet individually to locate the circular reference(s)?

 

Thanks again. Everett

@EverettMoran 

On the Formulas tab of the ribbon, click Error Checking > Circular References. You should see a flyout menu with the cells on the active sheet that have circular references.

S0553.png

You'll have to do this for each sheet individually, I'm afraid.

Thank you for the visual. Yes, finding them now! Thanks again for the help.