Microsoft Office Tutorials and References
In Depth Information
Perhaps one of the most useful options on the Highlight Cells Rules
continuation menu is the Duplicate Values option that enables you to flag duplicate
entries in a cell range by assigning them a special formatting. Doing this not
only makes it easy to visually identify duplicate entries in a data list or table
but also to find them electronically by searching for their particular formatting
characteristics. (See Chapter 6 for details on searching your worksheets.)
The options on the Top/Bottom Rules continuation menu enable you to
specially format and, therefore, easily identify values in data tables and lists that
are either above or below the norm. These options not only include those
for automatically formatting all values in a range that are among the top 10
highest or lowest (either in value or percentage) but also above or below the
average (as calculated by dividing the total by the number of values).
In addition to using the ready-made rules for conditional formatting located on
the Highlight Cells Rules and Top/Bottom Rules continuation menus, you can
also create your own custom rules. When you create a custom rule, you not
only specify the rule type that identifies which values or text entries to format,
but also you format the colors and other aspects included in the formatting.
(For details on creating custom conditional formats, consult my Excel 2013
All-In-One For Dummies .)
Formatting via the Quick Analysis tool
One of the quickest and easiest ways to apply Data Bars, Color Scales, Icon Set,
Greater Than, or Top 10% conditional formatting to a data table is with the new
Quick Analysis tool. The coolest thing about applying conditional formatting in
this manner is that Live Preview lets you visualize how your data looks with a
particular type of conditional formatting before you actually apply it.
To assign conditional formatting with the Quick Analysis tool, select the data in
your table that you wanted formatted and then select the Quick Analysis tool.
By default, the Formatting option is selected when Excel displays the tool’s
palette so that all you have to do is highlight each of the formatting options with
your mouse or Touch Pointer to see how they will look on your data.
Figure 3-20 shows you the Live Preview of the financial data in the Mother
Goose 2013 Sales table with the Data Bars conditional formatting (as the Data
Bars button is highlighted in the Formatting options). To assign this
conditional format to the financial data in the selected table, you simply click the
Data Bars button on the Quick Analysis palette. To preview how the data
would look formatted with another conditional format, you simply highlight
its button with the mouse or Touch Pointer.