Exploring Office 2007: Conditional Formatting in Excel 2007 Excel 2007 Conditional Formatting: Color the Numbers Helen Bradley
Sometimes using just one conditional formatting option isn't enough and you'll want to use multiple different formats for a single cell to indicate multiple properties. Prior to Excel 2007 it wasn't easy to apply multiple conditional formats to a single cell but now, with Excel 2007, not only do you have lots more conditional formatting options, you can also use them to apply multiple formats to a cell. In this article we'll introduce you to some of these new features of Conditional Formatting in Excel 2007.
»
Color the Numbers
One new feature of conditional formatting is the ability to easily highlight numbers using colors to illustrate their value. With a range selected, choose the Conditional Formatting icon on the Home tab of the Ribbon. Choose New Rule and from the Select a Rule Type list choose Format all cells based on their values. Next, choose the 3-Color Scale. Leave the values at their default and click OK.
You will see a pattern of green and red colors on the screen — the cells colored darkest green are the largest in the range, those colored yellow are in the middle of the range, and those colored red are towards the lower end of the scale. This format assesses the values in the range and makes the calculations for applying the colors.
If the color range isn't to your liking, select the data again, choose Conditional Formatting > Manage Rules and click Edit Rule. Change the colors by selecting different colors from the dropdown lists.
For example, you can choose a theme color and use the lightest color for the lowest value, a middle intensity of the color for the midpoint, and the darkest color for the highest value. Click OK twice to see the change.
If you're the type of person that wants more than one type of format, select the same area again and this time, from the Conditional Formatting options choose New Rule and select the "Format only values that are above or below average" option. From the Format Values... box, select 1 std dev above, click the Format button, and select a Font Color for the numbers and click OK twice.
Now in addition to cells having a colored background, the values in those cells will be colored differently for values that are above one standard deviation for all of the cells in the range. So you can, for example, use the cell background color to indicate that a cell matches one condition, the font color to indicate a match for a second condition, and even a format such as bold and italics to indicate a match for a third criteria.
»
Identifying Duplicates
One new feature of the Conditional Formatting tool in Microsoft Excel 2007 is the ability to highlight duplicate values in a range. To do this, select the range and choose Conditional Formatting > Highlight Cells Rules and choose Duplicate Values. You can highlight cells that have duplicate values by selecting Duplicate or choose Unique to highlight cells which contain unique values.
Select the format to use and click OK. Now, all cells containing duplicate values will be highlighted using the selected format. If data changes and duplicates are created later on, the conditional format will ensure these cells are highlighted.
Another handy option is the ability to highlight cells at either end of the selected range. For this, choose Conditional Formatting > Top/Bottom Rules and select, for example, the top number of items by selecting Top 10 Items. This is a bit misleading as it actually lets you select the top n items — so you can set it to the value 3 to highlight just the top 3 values.
