Tom’s Tutorials for Excel: Dynamic Conditional Formatting By Group

Tom’s Tutorials for Excel: Dynamic Conditional Formatting By Group

To improve the readability of your lists, you can conditionally format alternating groups of primary items. The challenge is to make this happen when the count of each client name varies in the list, which can change at any time.



In the above picture, Widgets, Inc maintains its list of clients by their name and purchasing activity. To quickly identify each client group, Conditional Formatting has been applied with an alternating scheme of color/no color at each change of client name in column A.

Here’s how it’s done, using the next picture as a guide. Notice the two pictures are identical except that column G has now been unhidden.



Step 1
In cell G3 (same row as the list header) a zero is entered.

Step 2
In cell G4 enter the helper formula
=IF(A4=A3,G3,G3+1)
and copy down to cell G22, (same row where the list ends).

Step 3
Select the list’s data range, in this example A4:C22.

Step 4
Show the Conditional Formatting dialog by pressing Alt+O+D.

Step 5
In the Conditional Formatting dialog, enter a new rule formula — that is, use a formula to determine which cells to format — with this formula:
=AND(LEN($A4)>0,MOD($G4,2)=0)
Note the absolute $ references in the formula.

Step 6
Click the Format button.

Step 7
Click the Patterns tab (in versions 2003 or before) or the Fill tab (in versions 2007 or after) and select a color from the palette.

Step 8
Click OK to accept the selected color, and click OK to exit the Conditional Formatting dialog.

Step 9
Optional and recommended, re-hide the column (which is column G in this example) where you have the helper formula.