Microsoft Office Tutorials and References
In Depth Information
Highlighting Cells with Conditional Formats
6. Verify that light green appears in the Format cells that contain the text box,
select Green Fill with Dark Green Text in the with box, and then click the OK
button. Cell D52 is filled with the same light green fill color used for the top
10 percent values.
7. In cell E51, enter Bottom 10% in terms of sales increase and then, in cell E52,
enter Top 10% in terms of sales increase .
8. Select the range E51:E52 . You’ll format these cells with a cell style to distinguish
them from the rest of the text in the worksheet.
9. In the Styles group, click the Cell Styles button, and then, in the Data and Model
section, click Explanatory (the third cell style in the first row).
10. Click cell A35 to deselect the range. The legend is complete. See Figure 2-36.
Conditional formatting legend
custom fill colors
match th e cell
used in the table
text for matte d with
the Explanatory style
The conditional formatting in the Monthly Sales worksheet helps Tom understand how
sales of the X310 heart rate monitor changed over the past two years and helps him focus
on particular sales regions for additional analysis.
Using Conditional Formatting Effectively
Conditional formatting is an excellent way to highlight important trends and data values
to clients and colleagues. However, it should be used judiciously. An overuse of
conditional formatting can sometimes obscure the very data values you want to emphasize.
You’ll need to make decisions about what to highlight and how it should be highlighted.
Keep in mind the following tips as you consider the best ways to effectively communicate
your findings to others:
• Document the conditional formats you use. If a bold, green font means that a sales
number is in the top 10 percent of all sales, include that information in a legend in the
• Don’t clutter data with too much highlighting. Limit highlighting rules to one or
two per data set. Highlights are designed to draw attention to points of interest. If you
use too many, you’ll end up highlighting everything—and, therefore, nothing.
• Use color sparingly in worksheets with highlights. It’s difficult to tell a highlight
color from a regular fill color, especially when fill colors are used in every cell.
• Consider alternatives to conditional formats. If you want to highlight the top 10
sales regions, it might be more effective to simply sort the data with the best-selling
regions at the top of the list.
Remember that the goal of highlighting is to provide a strong visual clue of important
data or results. Careful use of conditional formatting helps readers to focus on the
important points you want to make rather than be distracted by secondary issues and facts.