Using AutoFormat

Although it may sound a bit trivial, formatting can be an important aspect of creating a pivot table report. Pivot tables make it very easy to produce reports that summarize data by three, four, five, or more fields. When you start to show this many fields on a two-dimensional piece of paper, the totals can get very busy. Besides giving your pivot table reports a professional look and feel, formatting can have an illuminating effect on reports that are convoluted by large amounts of data. The right formatting can make your pivot table reports easier to read and improve the clarity of your data.

One of the built-in tools Excel has to assist in formatting your reports is the AutoFormat function. Even if you never use AutoFormat in Excel, you should consider it with pivot tables. AutoFormat takes on a new importance with all the field names and subtotals in a pivot table.

When you create a pivot table report, the PivotTable Wizard automatically applies an AutoFormat to your report. The default format the wizard uses is PivotTable Classic. Other AutoFormat options are available to you via the AutoFormat dialog box.

The second icon on the pivot table toolbar is a lightning bolt over a table (see Figure 4.1). Use this button to open a special PivotTable version of the AutoFormat dialog box. Alternatively, you can choose any cell in your pivot table and then select AutoFormat from the Format menu.

Figure 4.1. The lightning bolt and table icon is the key to making this table look better.

You'll notice there are 22 different formats to choose from: 10 report formats, 10 table formats, the PivotTable Classic default format, and None.

This dialog box is as straightforward as can be. As shown in Figure 4.2, simply select the format you would like to see your pivot table report in and click OK.

Figure 4.2. Select a predefined format and click OK. It is easy to select another format if the chosen one doesn't suit your tastes.

When you select one of the report formats (Report 1 through Report 10), your pivot table's structure will actually change. Compare the pivot table report in Figure 4.1 with the one in Figure 4.3. The only difference is that you selected Report 5 from the AutoFormat dialog box. Excel moved the Line of Business field from the Column Field area to the outermost field in the Row Field area. This allows for indenting and gives your pivot table report a layered look and feel.

Figure 4.3. Any of the 10 report-style AutoFormats will rearrange your data to eliminate column fields.

When you select one of the 10 table formats (Table 1 through Table 10), your pivot table report's structure is maintained while other attributes are changed. Figure 4.4 shows the original pivot table changes to use Table 7 from the AutoFormat dialog box.

Figure 4.4. Any of the 10 table-style AutoFormats will keep the table in the original format while applying styles to the various levels of subtotals.

Selecting the PivotTable Classic option returns your report to its default formatting, as shown in Figure 4.5.

Figure 4.5. The PivotTable Classic format.

Selecting the None option removes all color and border formatting.