Page 116 - Excel Timesaving Techniques for Dummies

22_574272 ch19.qxd 10/1/04 10:41 PM Page 101
SUM formula in cell C12 with 0. As a result, the total
don’t protect your formulas against this type of unin- Making Your Outstanding Errors Stand Out 101
tentional editing (as I suggest in Technique 38), you for cell range B12:D12 no longer is equal to that of
can at least set up this type of conditional formatting the cell range E3:E11, and the conditional formatting
to notify you if such an error occurs. is immediately applied to cell E12.

Figure 19-7 illustrates a situation where you could
apply this type of conditional formatting. Here, you
have a typical table of data that calculates the subto-
tals and grand total of the quarterly sales for a
bunch of different stores. E12 contains the formula
that returns the grand total (by summing the row
subtotals in the cell range E3:E11).

To alert me if this sum of the row subtotals ever
becomes unequal to the sum of the column totals (in
the cell range B12:D12), I set up conditional format-
ting for this grand-total cell E12. In the Conditional
Formatting dialog box, I select Formula Is as the
type and then enter the following formula in the
accompanying text box:
• Figure 19-7: Table where the sum of the column subtotals
=SUM($B$12:$D$12)<>SUM($E$3:$E$11)
should always equal the sum of the row
subtotals.
As for the formatting to be applied when the inequal-
ity set up by this formula is true, I go with bold and
red as the font attributes and bright yellow shading
for the cell background. That way, if cell E12 ever
lights up like a Christmas tree with this garish for-
matting, I know right away that some knucklehead
(probably me) has gone and messed up one of the
SUM formulas that calculates the column and row
subtotals.

Figure 19-8 shows you how this conditional format-
ting appears in cell E12 when just such a thing
occurs. For this figure, I intentionally replaced the • Figure 19-8: Conditional formatting applied to the grand
total cell when the inequality condition is
true.