Analyzing Microsoft Excel 2010 Data with PivotTable Reports

Formatting a PivotTable

The Design tab that appears on the ribbon under PivotTable Tools when you select any part of a PivotTable includes a large selection of professionally designed PivotTable styles. These work just like—and, in fact, are similar to—the styles available with ordinary tables. By choosing from the PivotTable Styles gallery, you can ensure that your PivotTable looks good and uses colors consistent with the rest of your workbook. You can customize the built-in style choices by selecting or clearing the check boxes in the PivotTable Style Options group, and you can add your own designs by clicking New PivotTable Style at the bottom of the PivotTable Styles gallery. To display the PivotTable Styles gallery, click the More button at the bottom of the scroll bar. (This button is a small arrow with a line above it.) For more information about using and customizing built-in styles, see “Formatting Tables” on page 770.

Customizing the Display of Empty or Error Cells

Empty cells in a PivotTable are usually displayed as empty cells. If you prefer, you can have your PivotTable display something else—a text value such as NA, perhaps—in cells that would otherwise be empty. To do this, right-click any cell in the PivotTable, and click PivotTable Options. On the Layout & Format tab in the PivotTable Options dialog box, select the For Empty Cells Show check box, and in the text box type the text or value that you want to see.

If a worksheet formula references a cell containing an error value, that formula returns the same error value. This is usually true in PivotTables as well. Error values in your source data propagate themselves into the PivotTable. If you prefer, you can have error values generate blank cells or text values. To customize this aspect of PivotTable behavior, right-click any cell in the PivotTable, and click PivotTable Options. On the Layout & Format tab in the PivotTable Options dialog box, select the For Error Values Show check box. Then, in the text box, type what you want to see.

Merging and Centering Field Labels

When you have two or more fields stacked either on the column axis or on the row axis of a PivotTable, centering the outer labels over the inner ones can sometimes improve the table’s readability. Just right-click a PivotTable cell, click PivotTable Options, and then select the Merge And Center Cells With Labels check box on the Layout & Format tab in the PivotTable Options dialog box. With this option, you can change this kind of presentation:

Click to view larger image

to this:

Click to view larger image

Hiding Outline Controls

You’ll probably find outline controls useful in some contexts and not in others. They’re great when you have large or complex PivotTables and you want to be able to switch quickly from a details view to an overview. But if you find that they clutter the picture instead of enhance it, you can banish them easily: Select a PivotTable cell, click the Options tab under PivotTable Tools, and then click +/– Buttons in the Show group.

Hiding Row Labels and Column Labels

The headings Row Labels and Column Labels that Excel displays near the upper-left corner of your PivotTable may prove distracting at times. You can suppress them by selecting a PivotTable cell, clicking the Options tab under PivotTable Tools, and then clicking Field Headers in the Show group. Note, however, that removing these labels also removes their associated filter controls—and you might want those controls from time to time. (See “Filtering PivotTable Fields” on page 793.) The Field Headers command is a toggle. Click it again to restore the headings—and the filter controls.