Checking for error by seeing which cells update upon re-calculating

**AELLC** · 02-05-2012, 02:41 PM

What I have is a very large Excel 2010 worksheet with hundreds of cells containing formulas.

I am trying to correct an error (i.e. debug) that I made in calculating a certain quantity (not an Excel error, just my error in writing a formula or logic statement in the worksheet). Therefore the Error Checking button in the Formula tab is not applicable.

The only way I can find the cell(s) that need to be corrected is for Excel to show which cells changed in value upon the sheet re-calculation resulting from the input of a certain number. Then I can go to each cell, one at a time, to debug.

Is there any way I can command Excel to highlight or list all cells which change in value upon re-calculation?

**xenixman** · 02-05-2012, 03:07 PM

If there is some type of keyword that is in the formula
you could use the regular Find button.

If your looking for a formula with the keyword ISBLANK or VLOOKUP
just click the Find & Select button on the ribbon, the select Find button and enter your keyword.

**AELLC** · 02-05-2012, 03:37 PM

xenix, neither is the case. It is a worksheet that I have been modifying for years to increase its functionality, and somehow an error crept in, of which I just noticed today.

I have no idea what is causing the error. The only way I can debug it is to see which cells update, otherwise it may take a very long time.

**xenixman** · 02-05-2012, 03:55 PM

If you know that your getting an error amount in a specific cell,
then how about the Trace Precedent?

**xenixman** · 02-05-2012, 04:00 PM

The only other way I can think of is to create a duplicate sheet
and use Conditional Formatting to see which cells differ between the two sheets
when you make changes to the first sheet.

**AELLC** · 02-05-2012, 05:49 PM

Right now I am comparing 2 worksheets that are only named differently, in order to de-bug this.

I am doing the trace precedent but it isn't easy because the precedents are so many in number and they branch all over.

I am wondering if there is an Excel add-in that helps compare the 2 open worksheets.

**AELLC** · 02-05-2012, 06:21 PM

OK, the bug may be in a data-validated cell where there is a drop-down list consisting of one blank and a series of numbers 4,6,8, etc.

Does Excel consider the "blank" to be text rather than a number?

**AELLC** · 02-05-2012, 06:50 PM

I think this is solved, so no need to kick it around further. I did it by switching calculation to manual and comparing the 2 open sheets.

Just to be sure, the data-validated cell is now followed by a calculation that multiplies it by 1, so the result is either zero or a number>0, never a blank cell.