Excel 2007 vs. 2003: Light Up Your Data with Conditional Formatting

By David H. Ringstrom, CPA

Excel 2007 takes conditional formatting — a powerful yet often overlooked feature — to new levels. In this article, I’ll explore data bars, color scales, icon sets, and other ways to transform lists of raw numbers into visual analysis — along with some helpful tips and tricks. I’ll also provide a brief overview of conditional formatting in Excel 2003 for those that haven’t moved to Excel 2007 yet.

Try it now:  A free 60-day trial of Office 2007 is available for immediate download. You can instruct the installation program to leave your present version of Office intact so that you’re free to work in either version.

Transition tool: Office Online offers an interactive tool that shows you where your favorite Excel 2003 commands now reside in Excel 2007.

Introducing Conditional Formatting

Everyone has applied static formatting to a cell: changing a font, applying bold or underline, or setting a new background color. Such formatting remains in place until you change it. Conditional formatting is different — the formatting changes based on the cell’s contents. Let’s create a sample worksheet to see how this works:

1.      Enter the word Week in cell A1 and the word Sales in cell B1 of a blank worksheet.

2.      Enter the number 1 in cell A2, and then choose Fill and then Series from the Editing section of the Home ribbon.

3.      Choose Columns, set the stop value to 16, and then click OK.

Clever: The Fill Series feature makes it easy to create a list of numbers without using your mouse. Many users go about this the hard way: they drag the fill handle (the notch in the lower right-hand corner of a cell) down and often overshoot their intended destination.

4.      Enter this formula in cell B2:

=1000*RANDBETWEEN(-30,70)

New function: The new RANDBETWEEN function generates random numbers within a range that you specify, in this case between -30 and 70. In spreadsheet jargon this function is “volatile” — its results change every time the worksheet recalculates.

5.      Double-click the fill handle in cell B2. Doing so copies the formula in cell B2 down to cell B17 automatically.

Timesaver: You can double-click the fill handle in any Excel version instead of dragging formulas down a column manually. Excel copies the active cell’s contents down until it encounters a blank cell in the preceding column.

6.      Cells B2:B17 should already be selected, so choose Conditional Formatting, Highlight Cells Rules, and then Greater Than from the Styles section of the Home ribbon.

7.      Set the Greater Than rule to 0, and choose Green Fill with Dark Green Text from the drop down list, as shown in Figure 1. Click OK to apply the conditional formatting. All cells with numbers greater than zero should be green.

Figure 1: You can preview conditional formatting on the worksheet as you define the rule.

8.      Choose Conditional Formatting, Highlight Cells Rules, and then Less Than.

9.      Set the Less Than Rule to 0 and click OK to accept the default choice of Light Red Fill with Dark Red Text. All cells with numbers less than 0 should be red. Any cells with 0 values should remain white.

10.  Press the F9 key several times to see conditional formatting at work. This keystroke recalculates the worksheet, which causes RANDBETWEEN to generate new random numbers.

Identify Duplicate Values

Now let’s see how conditional formatting can identify duplicate items in a list:

1.      Select cells A2:A20 (cells A18:A20 should be blank); choose Conditional Formatting, Highlight Cells Rules, and then Duplicate Values.

2.      Select Duplicate Values with Yellow Fill with Dark Yellow Text, as shown in Figure 2.

Figure 2: The Duplicate Values rule identifies multiple instances of list items.

3.      Enter these values in the corresponding cells:

A18: 2

A19: 9

A20: 16

Notice that cells A3, A10, and A17:A20 turn yellow as you enter the duplicate values — this provides a visual cue to the user that an entry already exists.

Delete duplicates: Use the Remove Duplicates command on the Data ribbon to eliminate duplicate entries from a list with just two clicks.

4. Select rows 18 through 20, choose Delete, and then Delete Sheet Rows from the Cells section of the Home ribbon.

Highlight Every Other Row

In previous versions of Excel, I often used conditional formatting to highlight every other row. Doing so makes a worksheet much easier to read. I’ll first demonstrate how to do so with conditional formatting, but I’ll then show you an easier way:

1.      Select cells A1:B17 in the spreadsheet from above; choose Conditional Formatting, Clear Rules, and then Clear Rules from Entire Sheet.

2.      Choose Conditional Formatting, Highlight Cells Rules, and then More Rules.

3.      As shown in Figure 3, choose Use a Formula to Determine Which Cells to Format, and then enter this formula:

=MOD(ROW(),2)=0

Figure 3: A custom rule can highlight every other row in our list.

4.      Click the Format button, and then choose Fill color, such as light green.

5.      Click OK twice to apply the conditional formatting. Every even-numbered row in the list you selected should now be light green.

MOD function: The MOD function has two arguments — number and divisor — and returns the remainder after number is divided by divisor. In this case the ROW function provides number, and divisor is 2. The remainder is any number to the right of the decimal left over after division, which is zero in the case of even-numbered rows.

6.      Choose Conditional Formatting, Clear Rules, and then Clear Rules from Entire Sheet to erase the conditional formatting.

7.      Select any cell within A1:A17, and then choose Format as Table from the Styles section of the Home ribbon. Click once on Table Style Light 2 (the second icon on the top row), and then click OK. Your spreadsheet should look similar to Figure 4; additional rows will adopt this new format automatically.

Figure 4: The Format as Table command transforms data into an easy-to-read format.

8.      Activate the Table Tools Design tab of the ribbon to remove the table style. Choose Convert to Range in the Tools section, and then click None in the Table Styles section to remove the table formatting.

Data Bars, Color Scales, and Icon Sets

You’re no longer limited to formatting cells just with color — Excel 2007 can convert drab lists into visual eye-candy:

1.      Select cells B2:B17 in your spreadsheet, and then choose Conditional Formatting, Data Bars, and then select a color.

2.      Choose Conditional Formatting, Color Scales, and then choose a color scale.

3.      Choose Conditional Formatting, Icon Sets, and then choose an icon set.

4.      At this point your spreadsheet should look somewhat like Figure 5, depending upon the choices that you made. Its overkill to apply all three formats to the same data, but this exercise is designed to illustrate the new conditional formatting capabilities in Excel 2007.

Figure 5: Excel 2007 enlivens static data.

Sort and Filter: The Sort and AutoFilter commands in Excel 2007 allow you to sort or filter lists based on color or cell icon.

Go beyond: The Top/Bottom Rules choice on the Conditional Formatting menu makes it easy to identify broad categories in your lists. You can also choose More Rules at the bottom of any conditional formatting category to fine-tune the formats. Also, the Manage Rules choice enables you to prioritize multiple rules so that you can decide which format you want to apply and in what order.

Learn more: See Data Takes Shape with Conditional Formatting for even more details and a video demonstration.

Conditional Formatting in Excel 2003

Excel 2007 makes previous versions of conditional formatting feel almost primitive: you can apply only three rules — as opposed to 64 in Excel 2007 — and no graphics are available. You also can’t sort or filter based on conditional formatting results. However, it’s still a helpful feature for data analysis:

1.      Enter the word Week in cell A1 and the word Sales in cell B1 of a blank worksheet.

2.      Enter the number 1 in cell A2, and then choose Edit, Fill and then Series.

3.      Choose Columns, set the stop value to 16, and then click OK.

4.      Enter this formula in cell B2:

=1000*RAND()

5.      Double-click the fill handle in cell B2.

6.      Choose Format and then Conditional Formatting.

7.      For Condition 1, choose Cell Value Is, and then make a choice from the drop-down list.

8.      Complete the amount field or fields that appear based upon your selection, and then click OK.

Alternatively, you can use formulas in Excel 2003 or earlier:

1.      Select your list, and then choose Format and then Conditional Formatting.

2.      For Condition 1, choose Formula Is and then enter one of these two formulas:

·         Identify duplicate rows: =COUNTIF($A$1:$A$20,A1)>1

COUNTIF: The COUNTIF function has two arguments — list and value — and returns the number of times that value appears in the list. In this case, our list is $A$1:$A$20 — the $ characters (this indicates an absolute reference) are required so that all cells use the same list, but don’t use an absolute reference for value.

·         Highlight every other row: =MOD(ROW(),2)=0

3.      Click Format, choose a pattern color, and then click OK twice.

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.