How to Change Workbook Appearance in Microsoft Excel 2010
- 8/25/2010
- Formatting Cells
- Defining Styles
- Applying Workbook Themes and Excel Table Styles
- Making Numbers Easier to Read
- Changing the Appearance of Data Based on Its Value
- Adding Images to Worksheets
- Key Points
Changing the Appearance of Data Based on Its Value
Recording package volumes, vehicle miles, and other business data in a worksheet enables you to make important decisions about your operations. And as you saw earlier in this chapter, you can change the appearance of data labels and the worksheet itself to make interpreting your data easier.
Another way you can make your data easier to interpret is to have Excel change the appearance of your data based on its value. These formats are called conditional formats because the data must meet certain conditions, defined in conditional formatting rules, to have a format applied to it. For example, if chief operating officer Lori Penor wanted to highlight any Thursdays with higher-than-average weekday package volumes, she could define a conditional format that tests the value in the cell recording total sales and changes the format of the cell’s contents when the condition is met.
To create a conditional format, you select the cells to which you want to apply the format, display the Home tab, and then in the Styles group, click Conditional Formatting to display a menu of possible conditional formats. In Excel, you can define conditional formats that change how the program displays data in cells that contain values above or below the average values of the related cells, that contain values near the top or bottom of the value range, or that contain values duplicated elsewhere in the selected range.
When you select which kind of condition to create, Excel displays a dialog box that contains fields and controls you can use to define your rule. To display all of the rules for the selected cells, display the Home tab, and then in the Styles group, click Conditional Formatting. On the menu, click Manage Rules to display the Conditional Formatting Rules Manager.
)
The Conditional Formatting Rules Manager.
The Conditional Formatting Rules Manager enables you to control your conditional formats in the following ways:
Create a new rule by clicking the New Rule button.
Change a rule by clicking the rule and then clicking the Edit Rule button.
Remove a rule by clicking the rule and then clicking the Delete Rule button.
Move a rule up or down in the order by clicking the rule and then clicking the Move Up button or Move Down button.
Control whether Excel continues evaluating conditional formats after it finds a rule to apply by selecting or clearing a rule’s Stop If True check box.
Save any new rules and close the Conditional Formatting Rules Manager by clicking OK.
Save any new rules without closing the Conditional Formatting Rules Manager by clicking Apply.
Discard any unsaved changes by clicking Cancel.
After you create a rule, you can change the format applied if the rule is true by clicking the rule and then clicking the Edit Rule button to display the Edit Formatting Rule dialog box. In that dialog box, click the Format button to display the Format Cells dialog box. After you define your format, click OK to display the rule.
)
A basic conditional formatting rule. Rules can include multiple criteria.
Excel also enables you to create three other types of conditional formats: data bars, color scales, and icon sets.
Data bars summarize the relative magnitude of values in a cell range by extending a band of color across the cell.
When data bars were introduced in Excel 2007, they filled cells with a color band that decreased in intensity as it moved across the cell. This gradient fill pattern made it a bit difficult to determine the relative length of two data bars because the end points weren’t as distinct as they would have been if the bars were a solid color. Excel 2010 enables you to choose between a solid fill pattern, which makes the right edge of the bars easier to discern, and a gradient fill, which you can use if you share your workbook with colleagues who use Excel 2007.
Excel also draws data bars differently than was done in Excel 2007. Excel 2007 drew a very short data bar for the lowest value in a range and a very long data bar for the highest value. The problem was that similar values could be represented by data bars of very different lengths if there wasn’t much variance among the values in the conditionally formatted range. In Excel 2010, data bars compare values based on their distance from zero, so similar values are summarized using data bars of similar lengths.
Color scales compare the relative magnitude of values in a cell range by applying colors from a two-color or three-color set to your cells.
The intensity of a cell’s color reflects the value’s tendency toward the top or bottom of the values in the range.
Icon sets are collections of images that Excel displays when certain rules are met.
An icon set can consist of three, four, or five images.
When icon sets were introduced in Excel 2007, you could apply an icon set as a whole, but you couldn’t create custom icon sets or choose to have Excel 2007 display no icon if the value in a cell met a criterion. In Excel 2010, you can display any icon from any set for any criterion or display no icon.
When you click a color scale or icon set in the Conditional Formatting Rules Manager and then click the Edit Rule button, you can control when Excel applies a color or icon to your data.
In this exercise, you’ll create a series of conditional formats to change the appearance of data in worksheet cells displaying the package volume and delivery exception rates of a regional distribution center.
Select cells C4:C12.
On the Home tab, in
the Styles group, click Conditional Formatting. On the menu, point to Color Scales, and then in the
top row of the palette, click the second pattern from the left.Excel formats the selected range.
Color Scales conditional formatting applied to the first data range.
Select cells F4:F12.
On the Home tab, in the Styles group, click Conditional Formatting. On the menu, point to Data Bars, and then, in the Solid Fill group, click the orange data bar format.
Excel formats the selected range.
Select cells I4:I12.
On the Home tab, in the Styles group, click Conditional Formatting. On the menu, point to Icon Sets, and then in the left column of the list of formats, click the three traffic lights with black borders.
Excel formats the selected cells.
Three types of conditional formatting applied to the same data range.
With the range I4:I12 still selected, on the Home tab, in the Styles group, click Conditional Formatting, and then click Manage Rules.
The Conditional Formatting Rules Manager opens.
Click the Icon Set rule, and then click Edit Rule.
The Edit Formatting Rule dialog box opens.
In the Edit Formatting Rule dialog box, you can customize conditional formatting.
Click the Reverse Icon Order button.
Excel reconfigures the rules so the red light icon is at the top and the green light icon is at the bottom.
In the red light icon’s row, in the Type list, click Number.
In the red light icon’s Value field, type 0.7.
In the yellow light icon’s row, in the Type list, click Number.
In the yellow light icon Value field, type 0.5.
Click OK twice to close the Edit Formatting Rule dialog box and the Conditional Formatting Rules Manager.
Excel formats the selected cell range.
Click cell C15.
On the Home tab, in the Styles group, click Conditional Formatting. On the menu, point to Highlight Cells Rules, and then click Less Than.
The Less Than dialog box opens.
In the left field, type 96%.
In the With list, click Red text.
Click OK.
The Less Than dialog box closes, and Excel displays the text in cell C15 in red.
)
)
)
)
Custom conditional formatting includes cell and text colors.