Excel Conditional Formatting - The Better To See You With

When Microsoft introduced conditional formatting with Excel 2003 users were finally able to accent important data without needing macros (i.e., VBA) or complex sorting and highlighting methods.

While the most-obvious use of conditional formatting is to highlight data that based on its values (ex., highest, lowest, meeting/exceeding values, heat mapped, etc.) , another useful feature is the ability to provide visual guides for large ranges of cells.  It's probably no accident that by default, Excel Tables are created with banded rows: highlighting every other row helps the viewer follow along an individual row from column to column.

This article will show you two ways to highlight your data in this manner - neither requiring any macros, and with little-to-no changes to your worksheet itself.
 

That good ol' lineprinter...

Yes, I can remember those days, loading up a Printronix dot matrix line printer with 17-inch wide greenbar paper every morning. That stuff was heavy! And those printers were noisy. But large reports were a little easier to read because every four lines of print were colored a very light green to help follow across the page.

We've come a long way. Now you can still highlight large ranges of data in sections of alternating row color to help you follow data, but without the heavy boxes of paper and no noise!

How to Highlight alternating rows (Excel 2010/2013):

1. Menu:  Home --> Conditional Formatting --> Manage Rules
This opens the Conditional Formatting Rules Manager
2. Click "New Rule" button.
This opens the New Formatting Rule dialog box.

3. Click "Use a formula to determine which cells to format"

4. In the box labeled "Format rules where this formula is true" type: =MOD(ROW(),2)=1  (**see note)5. Click the "Format..." button.

6. Click the "Fill" tab and choose a color that you'd like to highlight rows. Click the "OK" button.
I recommend a very light color, one that is just distinguishable from the white cells, but still allows your text to be visible.

7. Click the "OK" button.
Closes the Format Cells box.

8. Click the "OK" button.
Closes the Edit Formatting Rule box.

9. Change the value in the "Applies to" box to the following: =1:1000 (or as many rows as you need) then click the "OK" button.
You should now see every other row lightly colored.  These instructions only apply down to row 1000; you can easily edit that range and adjust as necessary.  (By the way, =MOD(ROW(),2) would also work, but it's less-obvious).
If you want to see some other alternating patterns, try these formulas:
Highlight every other pair of rows (2-white, 2-highlighted):  =MOD(ROW(),4)<2
Highlight every other group of four rows, just like good ol' greenbar:  =MOD(ROW(),8)<4
Highlight one out of every three rows (1-highlighted, 2-white): =MOD(ROW(),3)=0

**Wait, what?  What's a MOD?**

Ch..ch..ch..ch..Changes!

The above options provide fixed, alternating highlighting of rows. But wouldn't it be cool if it would highlight groups of sorted data, regardless of order, even if the data changes?

You can do this - without macros - with just the addition of a "helper column." This column can be added anywhere beside or within your data and hidden, if you prefer. It merely tracks the changes in the value or combination of values that you want to trigger the highlighting. Here's how you set it up.
 
1. Determine the column - or columns - that will trigger the change in row highlighting whenever they change.  In order to be useful, all the data should be sorted on this column(s), but it's not necessary.

2. Find a convenient location to insert a helper column. Most often, this will be a column just outside your data range.

3. In the first data cell, add one of these formulas (in these examples the helper column starts in W)
   - if just following changes in one column (in this example, column A), enter this in cell W2:
    =IF(A2="","",IF(A2=A1,IF(W1=1,1,0),IF(W1=0,1,0)))
   - if tracking changes in two columns (ex. first name & last name in columns A & B), enter this in cell W2:
    =IF(A2&B2="","",IF(A2&B2=A1&B1,IF(W1=1,1,0),IF(W1=0,1,0)))

4. Copy the formula down to the end of the data section.  You should see 0's and 1's change with each change in your key column(s).

5. Menu:  Home --> Conditional Formatting --> Manage Rules
This opens the Conditional Formatting Rules Manager

6. Click "New Rule" button.
This opens the New Formatting Rule dialog box.
7. Click "Use a formula to determine which cells to format"

8. In the box labeled "Format rules where this formula is true" type: =$W2=1

9. Click the "Format..." button.

10. Click the "Fill" tab and choose a color that you'd like to highlight rows. Click the "OK" button.
As above, choose a light color.

11. Click the "OK" button.
Closes the Format Cells box.

12. Click the "OK" button.
Closes the Edit Formatting Rule box.

13. Change the value in the "Applies to" box to the following: =1:1000 (or as many rows as needed) then click the "OK" button.You should see a change in the row color for every change in your key column(s), like so:
Some examples of where you might apply this kind of conditional formatting (in all cases, the data is sorted by the categories below):

• Transactions by CUSTOMER NUMBER/NAME
• Transactions by REGION/COUNTRY/STATE/CITY
• Transactions by DATE
• Employee listings by COMPANY/DEPARTMENT/MANAGER

Included with this article is an example workbook demonstrating several of the conditional formatting types described. Feel free to examine it and experiment with modifying the existing formats or using them as templates for your own data.

-Glenn Ray
Conditional-Formatting-Article.xlsx