Table of Contents

• Simple Conditional Formatting

Simple Conditional Formatting

Conditional formatting has improved significantly, and it’s now a useful tool for visualizing numeric data. Conditional formatting enables you to apply cell formatting selectively and automatically, based on the contents of the cells.

Specifying Conditional Formatting

To apply a conditional formatting rule to a cell or range, select the cells and then use one of the commands on the Home Tab-> Style Group-> Conditional Formatting drop-down to a specific rule. The Choices are:-

• Highlight Cell Rules: Examples of rules include highlighting cells that are greater than a particular value, between two values, contain a specific text string, or are duplicated.

• Top bottom Rules: Examples include highlighting the top 10 items, the items in the bottom 20 percent, and items that are above average.

• Data Bars: Applies graphic bar directly in the cell, proportional to the cell’s value.

• Color Scales: Applies background color, proportional to the cell’s value.

• Icon Sets: Displays icons directly in the cells. The icons depend on the cell’s value.

The figures below show a worksheet with nine ranges, each with different types of conditional formatting rules applied. Here’s a brief explanation of each.

Removing Conditional Formats

If you no longer want to conditionally format your data, you can remove any rules applied to it and return it to its original format. To remove conditional formats from the selected cells, sheet, or table, click Conditional Formatting in the Styles group of the Home Tab. Point to the Clear Rules option and select the appropriate option from the fly-out menu.

Advanced Conditional Formatting

Excel’s conditional formatting feature is versatile, but sometimes it’s just not quite versatile enough. Fortunately, you can extend its versatility by writing conditional formatting formulas.

Making your own rules

Excel provides the New Formatting Rule dialog box, shown in Figure above. Access this dialog box by choosing Home Tab -> Styles group -> Conditional Formatting -> New Rules. The New Formatting Rule dialog box lets you recreate all the conditional format rules as well as new rules available via the Ribbon.

New Rule: -> Enables you to specify other conditional formatting rules, including rules based on a logical formula.

* Clear Rules: -> Deletes all the conditional formatting rules from the selected cells.

Manage Rules: -> Displays the Conditional Formatting Rules Manager dialog box, in which you can create new conditional formatting rules, or delete rules.

First, select a general rule type from the list at the top of the dialog box. The bottom part of the dialog box varies, depending on your selection at the top. After you specify the rule, click the Format button to specify the type of formatting to apply if the condition is met. An exception is the first rule type, which doesn’t have a Format button (it uses graphics rather than cell formatting).

Following is a summary of the rule types:

• Format all cells based on their values: Use this rule type to create rules that display data bars, color scales, or icon sets.
• Format only cells that contain: Use this rule type to create rules that format cells based on mathematical comparisons (greater than, less than, greater than or equal to, less than or equal to, equal to, not equal to, between, not between). You can also create rules based on text, dates, blanks, nonblank, and errors. This rule type is very similar to how conditional formatting was set up in previous versions of Excel.
• Format only top or bottom-ranked values: Use this rule type to create rules that involve identifying cells in the top n, top n percent, bottom n, and bottom n present
• Format only values that are above or below average: Use this rule type to create rules that identify cells that are above average, below average, or within a specified standard deviation from the average.
• Format only unique or duplicate values: Use this rule type to create rules that format unique or duplicate values in a range.
• Use a formula to determine which cells to format: Use this rule type to create rules based on a logical formula.

Creating Formula-Based Rules

Follow these steps:

1. Select the range A1:B10 and ensure that cell A1 is the active cell.
2. Choose Home Tab -> Style group -> Conditional Formatting -> New Rule to display the New Formatting Rule dialog box.
3. Click the rule type labeled Use A Formula To Determine Which Cells To Format.
4. Enter the following formula in the Formula box =WEEKDAY (A2)=1
5. Click the Format button to display the Format Cells dialog box. Apply whatever formats you want to.
6. Click OK to return to the New Formatting Rule dialog box (refer to Figure)
7. In the New Formatting Rule dialog box, click the Preview button to make sure that the formula is working correctly and to see a preview of your selected formatting.
8. If the preview looks correct, Click OK to close the New Formatting Rule dialog box.