Page 197 - Excel 2013 All-in-One For Dummies

Conditional Formatting 179
2. Click the Conditional Formatting button on the Home tab and then
choose Top/Bottom Rules➪Below Average from the drop-down menu.
Excel opens the Below Average dialog box that contains a single drop- down list box where you define the formatting to be used when a value is below the calculated average for the cell range.
3. Select Custom Format at the bottom of the Format Cells That Are BELOW AVERAGE drop-down list box.
Excel opens the Format Cells dialog box where you define all the attri- butes to be part of the custom conditional formatting.
4. Click the Font tab in the Format Cells dialog box and then click Bold Italic in the Font Style list box.
5. Click the Fill tab in the Format Cells dialog box and then click the bright yellow swatch in the Background Color section before you click OK.
Excel closes the Format Cells dialog box, returning you to the Below Average dialog box, which now displays Custom Format in the Format Cells That Are BELOW AVERAGE drop-down list box.
6. Click OK to close the Below Average dialog box.
Excel then applies the custom formatting of bold italic text with bright yellow fill color to all values in the cell selection that are below the calcu- lated average (displayed after the Average heading on the status bar at the bottom of the Excel program window).
Highlighting duplicate values in a cell range
The Duplicate Values option on the Highlight Cell Rules continuation menu enables you to highlight duplicate values within a selected cell range.
To highlight duplicate values in a cell range, follow these steps:
1. Select the range of cells in the worksheet where you want duplicates formatted in a special way.
2. Click the Conditional Formatting button in the Styles group of the Home tab of the Ribbon; then choose Highlight Cell Rules➪Duplicate Values from the drop-down menu.
Excel opens the Duplicate Values Columns dialog box containing two drop-down lists: the first where you indicate whether Excel is to format identical values (Duplicate, the default) in the range or the standalone values (Unique) in the range, and the second where you indicate the type of formatting applied to either the duplicates or one-of-a-kind values.
Book II Chapter 2
Formatting Worksheets