<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Excel Conditional Formatting - The Better To See You With

Published on
15,622 Points
5,222 Views
4 Endorsements
Last Modified:
Awarded
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...

greenbarYes, 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
CFRM2. 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)CF-formula5. 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.
CF-Applies ToYou 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).
Alternating rowsIf 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?**

The MOD function returns the modulus of a number. If you recall long division, that is whole number remainder after you divide one number by another. For example, the modulus of 10 divided by 3 is 1. In Excel, that is written

=MOD(10,3)

The formula use in this conditional formatting formula is dividing the current row that the cell is on by 2 and returning the remainder. The result will always be 0 for even numbers or 1 for odd numbers.  So, if a row is an odd-numbered row, the format is applied.
 

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:
value changeSome 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
4
Comment
Author:Glenn Ray
  • 3
5 Comments
 

Administrative Comment

by:Eric AKA Netminder
Glenn,

Your article has been published, and because I've always wanted to know how to do this, it has been awarded EE-Approved status.

Congratulations!

ericpete
Page Editor
0
LVL 28

Author Comment

by:Glenn Ray
ericpete,
Thank you for the editorial changes and for approving my article.

-Glenn
0
LVL 28

Author Comment

by:Glenn Ray
I could see the source HTML as in your screenshot, but clicking the OK button had no effect (would not close the window, so no changes saved).

However, I just went back to look at the editor again (to make notes for a posting to the forum you listed) and some of the controls behave properly (bolding, underlining turns on and off).  Undo works, but still need "body text" control.

-Glenn
0
LVL 28

Author Comment

by:Glenn Ray
Thanks.  Looks better. :-)
0
LVL 16

Expert Comment

by:Jerry Paladino
Nice Article Glenn!

Thanks,
Jerry
0

Featured Post

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Join & Write a Comment

This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month