When your students need to peruse row after row of numbers in a data-heavy worksheet, they can easily lose their place, overlook an important trend, or misread some crucial bit of information. To help them manage this data overload, you can show them how to display specific types of data in different colors.
For instance, they might display better-than-expected sales totals in green and below-expected totals in red. This use of color makes the information jump off the page, allowing your students (and their clients, colleagues, and bosses) to evaluate sales performance at a glance.
And here's some good news: There's no need to haul out the highlighters or manually flag the various worksheet entries. Excel's conditional formatting feature automates the color-coding process. We'll use our over/under sales goal scenario to demonstrate this handy technique.
Let’s assume that you have four columns of sales data with a SUM function at the bottom of each one to total the column entries, as shown in Figure A. We’ll tell Excel to display totals that are equal to or above $100,000 in bold green type and to display totals that are below $100,000 in bold red type.
|We'll add conditional formatting to our row of totals.|
- Select the four cells containing the SUM function totals.
- Choose Conditional Formatting from the Format menu.
- Under Condition 1, leave the first box set to Cell Value Is, but choose Greater Than Or Equal To from the drop-down list and enter 100000 in the text box.
- To set the format for entries that reach or exceed expectations, click the Format button. Then, choose Bold from the Font Style list and green from the Color drop-down list, as shown in Figure B.
|These settings will apply the desired conditional formatting to entries that are greater than or equal to 100,000.|
- When you finish, click OK to return to the Conditional Formatting dialog box.
After you’ve created the first condition—for values of 100,000 or more—you can set up a second condition for worse-than-expected sales. (You can actually define up to three conditions if you want, but we’re going to settle for two.)
- Click the Add button. Excel will expand the Conditional Formatting dialog box to include options for Condition 2.
- Leave the first box set to Cell Value Is, select Less Than from the list box, and enter 100000 in the text box. To specify the formatting for this condition, click Format, choose Bold and red, and click OK. Figure C shows the Conditional Formatting dialog box at this point.
|Excel will apply these formats when the second condition is met.|
- Click OK to return to your worksheet. When you do, you’ll discover that Excel is displaying the totals according to your specifications, as shown in Figure D.
|Once you apply the conditional formatting, Excel will display the totals in the specified colors.|
To demonstrate the dynamic nature of this feature, you can implement a few changes in the data that will trigger a color change. For example, in our sample worksheet, if you change the entry in cell A2 to 21,000, the total in the North column will dip below $100,000. When this happens, Excel will change the entry to red text. And if you change the entry in cell C5 to 28,000, pushing the East column's total to 103,720, Excel will turn the entry green.
If you'd like to suggest a technique or topic for us to cover, just drop us a note . And don't forget you can share your opinions about this article by posting a comment below.
Jody Gilbert has been writing and editing technical articles for the past 25 years. She was part of the team that launched TechRepublic and is now Feature Editor for Tech Pro Research.