Conditional formatting is typically applied to a range of cells and responds differently depending on the contents of each cell. You can use conditional formatting to flag discrepancies, indicate relative values, highlight specific or duplicate values, and more. The Conditional Formatting menu contains five categories of prepackaged rules you can choose from, which are described in the following list.
Highlight Cells Rules This category of rules uses comparison operators to determine which cells to highlight, including greater than, less than, equal to, and between. You can also use Highlight Cells Rules to highlight duplicate values or dates within a specified range.

77-427 & 77-428-f29

Top/Bottom Rules You can use this category of rules to highlight figures on the top or bottom of the value scale, using numbers, percentages, or averages. The default for both “top” commands is 10, but you can use any number (for example, top 500) or any percentage.

77-427 & 77-428-f30

# Bars These create a “bar chart” style display on the worksheet, where the relative value in each cell in the range is displayed as a bar of color; the greater the value, the longer the bar. The bars are sized relative to the sum of all values specified in the conditionally formatted range.

77-427 & 77-428-f31

Color Scales These are sets of colors that denote relative values, and can help visualize the distribution of values across a worksheet.

77-427 & 77-428-f32

Icon Sets You can use these to indicate relative values by using sets of 3, 4, or 5 icons, each representing a range of values. Icons are inserted inside the cell, which usually requires widening the column to accommodate them.

77-427 & 77-428-f33

In the dialog boxes for both Highlight Cells and Top/Bottom rules, you can modify the default formats by selecting a different option in the With list. One of the options in that list is Custom Format, which displays the Format Cells dialog box, where you can tailor the number, font, border, and fill formatting. But you can gain far more control over conditional formatting by creating your own rules, as described in the next topic.

 

Creating custom conditional formats

You can build your own custom formats by clicking the New Rule command on the Conditional Formatting menu (or the ...
Read More

Using functions to format cells

The last rule type in the Edit Formatting Rule dialog box is Use A Formula To Determine Which Cells To ...
Read More

Creating advanced filters

You can apply more than one conditional format to the same cells. For example, you can use a Top 10 ...
Read More

Managing conditional formatting rules

------------------------------------------------------ Experimenting with conditional formatting tends to cause the accumulation of rules, but you might not notice, because when you ...
Read More

This lesson is a part of Apply custom formats and layouts in Excel 2013 from 77-427 Excel 2013 Expert Part One Prep course. More lessons in this chapter are

Apply advanced conditional formatting and filtering

Conditional formatting is typically applied to a range of cells and responds differently depending on the contents of each cell ...
Read More

Prepare workbooks for internationalization and accessibility

In order to prepare workbooks for internationalization and accessibility, whenever you share your workbooks with others, you need to make ...
Read More

The Practice tests included in this course are:

No posts found.