Unit 21: Conditional Formatting

Formatting allows you to change the way that the data in cells in a worksheet appear on the screen. For example, numbers can be made to appear as currency values or percentages by formatting them accordingly. Microsoft Excel 2010 provides a variation on formatting known as conditional formatting. With conditional formatting, cells can be formatted in different colors schemes. Rather than this formatting being applied to all cells in a range, it is applied selectively and based on specific rules. This type of formatting allows you to see, for example, values that are over a certain amount, or to instantly spot high and low values based on assigned coloring.

As the name suggests, conditional formatting is a type of formatting that is applied to cells or ranges when certain conditions are met. These conditions are set, but can quite often be customized and edited, in rules that have been programmed into Excel. There are two types of conditional formatting – values-based formatting, and trend-based formatting. What Happens with Conditional Formatting With conditional formatting, cells in a specified range are colored or shaded according to certain conditions which are outlined in rules. Values-Based Conditional Formatting With values-based conditional formatting, cells in the range are examined and their shading and coloring is based on whether they meet the conditions of the rule. This type of formatting allows to see whether values in a range are greater than a certain value, less than a certain value, equal a certain value, or fall between a range. You can also display top ten, bottom ten, top 10%, bottom 10%, and above and below averages with this type of formatting. In all cases a dialog box will appear which will allow you to tweak the rule to what is needed. Basically, the dialog box will allow you to specify a rules value and to determine the color of the shading.

Conditional Formatting Top Bottom Rules

 

Excel Conditional Formatting Video

A common analysis requirement is to see which values in a worksheet are greater than a specific amount. For example, if you want to see which salespeople have bettered their targets. This can be done using the Greater Than option which appears as one of the options in the Highlight Cells Rules set of the Conditional Formatting command. Excel will compound conditional formats. For example, you can apply a Greater Than format, then come back and apply a Less Than format.
The original format will remain, depending upon what is required in the second format. Unless you want compounding formats, it is much safer to clear any previous formats from the worksheet before applying a new one.

Each of the Sparklines above charts the figures for the Jan, Feb, and Mar columns to their left. For example, the Sparkline in cell G4 charts the figures in the range C4:E4. There are three different types of Sparklines available in Excel 2010, Line, Column, and Win/Loss. Each of these are shown above. The Line Sparkline displays as a line. The dots in the example above appear because the Sparklines have been asked to display the highest and lowest values. A Column Sparkline displays as vertical bars. The Win/Loss Sparkline displays positive values in one color above an imaginary line and negative values in another color below that imaginary line. While Sparklines are never going to be as versatile as charts there are still a lot of formatting options that you have at your disposal when working with them. When a cell containing a Sparkline is selected a Design tab for

UNIT 21 – Conditional Formatting


There aren't any posts currently published in this category.