Removing circular references without the use of macros?

[mattflow]

Is there a way to get rid of a circular reference without the use of macros?

[MrShorty]

Short answer: Yes!

How to do it? Depends on your individual spreadsheet. In the status bar, do you have something to the effect of "circular:J6"? That's Excel's way of telling where it recognizes the circular reference. Then you can use the formula auditing tools (Tools>Formula Auditing>Trace Precedents/evaluate formula/show toolbar/etc.) to try to locate the circular reference. There's also a circular reference toolbar (View>Toolbars>Customize>Toolbars>check circular reference toolbar) that has some of the same tools.

If Excel can't identify one of the cells in the circular reference, but there is one, then you'll have to work a little harder at locating the circular reference, but it involves the same tools, you just may have a harder time knowing where to start.

[mattflow]

Sorry, I should have given a little more background. These circular references are necessary (there is a budget which has entries that are % of the total budget). So is there a way to have a separate cell that keeps track of this without causing a circular reference? And the hard part is this has to be done without the use of macros.

[MrShorty]

I don't understand what you need a circular reference for, but will it work if you enable iteration (Tools>Options>Calculation>Iteration checkbox)?