Circular Reference Greyed out

**JesperAudi** · 02-27-2013, 09:30 AM

Using Excel 2010, I have a large workbook, with lots of sheets, many pivots, and thousands of formulae including external references to other workbooks .
Because of its size I have been turning autocalc on and off.
I now have the message circular references in the status bar but have no idea where.
If I go into formula auditing the circular reference bit is greyed out.
Any ideas anyone?

Thanks, Jesper

I should add that I also get a message saying that Excel cannot calculate a formula. There are circ refs in an open workbook that cannot be listed. (nb only one open) Try editing the last formula or using Undo button. (I suspect it happened a while ago and there are no undo's and I have no idea where to look)

**ChemistB** · 02-27-2013, 09:57 AM

Try doing an error check on each sheet. That should at least tell you which formula can't be calculated. Typically "Circular Reference" is gray if there are no circular references on the sheets. Maybe it's an issue with defined names. Hmmmm

**JesperAudi** · 02-27-2013, 10:16 AM

Thanks ChemistB for reply.

I'm not using names so that isn't the issue.
The problem with error checking each sheet is that there are hundreds of errors eg dividing by zero etc
I have started the idea of deleting each sheet without saving so I can see if it occurs on more than one sheet; I'll let you know how I get on.

Thanks, Jesper
I

**JesperAudi** · 02-27-2013, 10:44 AM

Well I guess that solved it; My last 4 sheets had quite ba lot of references to 'itself'.

By deleting each sheet in turn I was able to narrow it down.
In the end the circ ref message went off only when I had deleted the 4 last sheets ~ luckily thet were together

Having found which sheets, I then had to reopen (without saving), go to those sheets and adopt the same approach to columns

The problem affected several thousand rows of 4 columns, so eventually I was able to fix it.

I will mark as solved in case anyone searches in the future, but I still don't understand why the formula auditing box was greyed out, and even if it was, why couldn't clicking in status bar at least take me to one example, rather than just saying too many to list. One would have helped.
#

Anyway chemistB, thanks for your he;lp.

Regards, Jesper

**ChemistB** · 02-27-2013, 10:52 AM

Glad you figured it out. You may be right, it might be grayed out because there are too many to list.

**DashAnth** · 12-15-2022, 12:13 PM

I apologise for the grave dig however I recently had this and found my issue was not due to the columns.

I followed the same steps of deleting rows and columns one by one. It turned out to be a dodgy row of items. So if you're in this situation it could be worth checking that.