A personal repository of technical notes. - CSC

Excel Alternating Rows Conditional Formatting Example

Need to format groups of matching rows with alternating colors.

Use a formula with conditional formatting.

1) Decide which column will be the "key column".
2) Sort sheet by key column.
3) Define a new Conditional Formatting column
  • Put "1" in column header row
  • Put formula in second row. Example: "=IF(A2=A1,D1,D1*-1)" where "A" is key column and "D" is Conditional Formatting column.
  • Copy formula into each row of column.

4) Define Conditional Formatting for alternating rows
  • Select all active cells in sheet
  • Click Conditional Formatting/New Rule...
  • Select Rule Type "Use a formula to determine which cells to format".
  • In "Format values where this formula is true:", define the Conditional Formatting column where = 1: Example: "=$D1=1" where "D" is Conditional Formatting column.

  • Click Format...
  • Select Fill tab, pick color, click OK, click OK.