PivotTables are the ideal tools for summarizing and examining large data tables, even those containing in excess of 10 or even 100,000 rows. Even though PivotTables often end up as compact summaries, you should do everything you can to make your data more comprehensible. One way to improve your data's readability is to apply a number format to the PivotTable Values field. To apply a number format to a field, right-click any cell in the field and then click Number Format to display the Format Cells dialog box. Select or define the format you want to apply and then click OK to enact the change.
For more information on selecting and defining cell formats by using the Format Cells dialog box, see "Formatting Cells" in Chapter 5.
Analysts often use PivotTables to summarize and examine organizational data with an eye to making important decisions about the company. For example, chief operating officer Jenny Lysaker might examine monthly package volumes handled by Consolidated Messenger and notice that there's a surge in package volume during the winter months in the United States.
Excel 2007 extends the capabilities of your PivotTables by enabling you to apply a conditional format to the PivotTable cells. What's more, you can select whether to apply the conditional format to every cell in the Values area, to every cell at the same level as the selected cell (that is, a regular data cell, a subtotal cell, or a grand total cell) or to every cell that contains or draws its values from the selected cell's field (such as the Volume field in the previous example).
To apply a conditional format to a PivotTable field, click a cell in the Values area. On the Home tab, in the Styles group, click Conditional Formatting and then create the desired conditional format. After you do, Excel 2007 displays a Formatting Options smart tag, which offers three options on how to apply the conditional format:
For more information on creating conditional formats, see "Changing the Appearance of Data Based on Its Value" in Chapter 5.
In Excel 2003 and earlier versions of the program, you were limited to a small number of formatting styles, called autoformats, which you could apply to a PivotTable. In Excel 2007, you can take full advantage of the Microsoft Office system enhanced formatting capabilities to apply existing formats to your PivotTables. Just as you can create data table formats, you can also create your own PivotTable formats to match your organization's desired color scheme.
To apply a PivotTable style, click any cell in the PivotTable and then, on the Design contextual tab, in the PivotTable Styles group, click the gallery item representing the style you want to apply. If you want to create your own PivotTable style, click the More button in the PivotTable Styles gallery (in the lower-right corner of the gallery) and then click New PivotTable Style to display the New PivotTable QuickStyle dialog box.
Type a name for the style in the Name field, click the first table element you want to customize, and then click Format. Use the controls in the Format Cells dialog box to change the element's appearance. After you click OK to close the Format Cells dialog box, the New PivotTable Quick Style dialog box Preview pane displays the style's appearance. If you want Excel 2007 to use the style by default, select the Set as default PivotTable quick style for this document check box. After you finish creating your formats, click OK to close the New PivotTable Quick Style dialog box and save your style.
The Design contextual tab contains many other tools you can use to format your PivotTable, but one of the most useful is the Banded Columns check box, which you can find on the Design contextual tab, in the PivotTable Style Options group. If you select a PivotTable Style that offers banded rows as an option, selecting the Banded Rows check box turns banding on. If you prefer not to have Excel 2007 band the rows in your PivotTable, clearing the check box turns banding off.
In this exercise, you'll apply a number format to a PivotTable values field, apply a PivotTable style, create your own PivotTable style, give your PivotTable banded rows, and apply a conditional format to a PivotTable.
USE the Formatting workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\PivotTables folder.
OPEN the Formatting workbook.
CLOSE the Formatting workbook.