Formatting the values in the pivot table

One thing that stands out like a sore thumb in pivot tables is the lack of number formatting for the values it summarizes. When Excel creates a new pivot table, it doesn't pick any formatting from the original data source. You have to manually apply whatever number formats and other kinds of table formatting that you want. Fortunately, Excel makes it easy to format both the individual fields of the pivot table as well as the overall table itself.

To format a particular pivot table field, double-click one of the field labels in the table or select the label and then click the Field Settings button on the PivotTable toolbar to open the PivotTable Field dialog box for that field. To format the values summarized in the body of the pivot table, double-click the label that says "Sum of" followed by the name of the field whose values are being summarized in the body of the table to open the PivotTable Field dialog box.

If you're formatting a numeric field whose data is presented in the body of the table (as a Data Item) and you want to assign a number format to this data, click the Number button. A simplified version of the Format Cells opens that contains only a Number tab from which you can select the type of number format that you want to use.

If you're formatting a text field used as a Column or Row Field in the pivot table and want to sort the field labels in ascending or descending order across the top row or down the first column, click the Advanced button in its PivotTable Field dialog box. Click the Ascending or Descending option button in the AutoSort section of the PivotTable Field Advanced Options dialog box.

To select an AutoFormat for the pivot table, position the cell pointer in any of the table's cells and then click the Format Report button. (If the PivotTable toolbar is not displayed, choose Format → AutoFormat.) The program selects all the cells in the pivot table and opens up an AutoFormat dialog box. This AutoFormat dialog box contains a list with ten different sample Report formats followed by ten different sample Table formats, along with a sample PivotTable Classic format and a None format. (Use the final option to remove any other Report or Table formatting.)

To assign a particular Report or Table format to your pivot table, select its sample and click OK. Excel assigns all the formatting in the selected Report or Table format to your pivot table. If you find that you're not happy with the format you selected, press Ctrl+Z to remove all the new formatting from the table and select another one from the AutoFormat dialog box.