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.)

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:

to this:

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 they clutter the picture instead of enhancing it, you can banish them easily: Select a PivotTable cell, click the Options tab under PivotTable Tools, and then click the +/− Buttons in the Show/Hide group.

Note 

With outline controls suppressed, you can still expand and collapse field headings. Select a heading in the field you’re interested in, click the Options tab under PivotTable Tools on the Ribbon, and then click Expand Entire Field or Collapse Entire Field in the Active Field 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/Hide 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 502). The Field Headers command is a toggle. Click it again to restore the headings-and the filter controls.

Note 

You can change the name of a PivotTable field or an item within a field by selecting any occurrence of it and typing the name you want. When you change one occurrence, all occurrences in the table change.