How to Format a Pivot Table in Excel 2016 - dummies

How to Format a Pivot Table in Excel 2016

By Greg Harvey

Excel 2016 makes formatting a new pivot table you’ve added to a worksheet as quick and easy as formatting any other table of data or list of data. All you need to do is click a cell of the pivot table to add the PivotTable Tools contextual tab to the Ribbon and then click its Design tab to display its command buttons.

The Design tab is divided into three groups:

  • Layout group that enables you to add subtotals and grand totals to the pivot table and modify its basic layout

  • PivotTable Style Options group that enables you to refine the pivot table style you select for the table using the PivotTable Styles gallery to the immediate right

  • PivotTable Styles group that contains the gallery of styles you can apply to the active pivot table by clicking the desired style thumbnail

Refining the Pivot Table style

When selecting a new formatting style for your new pivot table from the PivotTable Styles drop-down gallery, you can then use Excel’s Live Preview feature to see how the pivot table would look in any style that you highlight in the gallery with your mouse or touch pointer.

After selecting a style from the gallery in the PivotTable Styles group on the Design tab, you can then refine the style using the check box command buttons in the PivotTable Style Options group. For example, you can add banding to the columns or rows of a style that doesn’t already use alternate shading to add more contrast to the table data by putting a check mark in the Banded Rows or Banded Columns check box, or you can remove this banding by clearing these check boxes.

Formatting values in the pivot table

To format the summed values entered as the data items of the pivot table with an Excel number format, you follow these steps:

  1. Click the field in the table that contains the words “Sum of” and the name of the field whose values are summarized there, click the Active Field command button on the Analyze tab under the PivotTable Tools contextual tab, and then click the Fields Settings option on its pop-up menu.

    Excel opens the Value Field Settings dialog box.

  2. Click the Number Format command button in the Value Field Settings dialog box to open the Format Cells dialog box with its sole Number tab.

  3. Click the type of number format you want to assign to the values in the pivot table on the Category list box of the Number tab.

  4. (Optional) Modify any other options for the selected number format, such as Decimal Places, Symbol, and Negative Numbers that are available for that format.

  5. Click OK twice, the first time to close the Format Cells dialog box, and the second to close the Value Field Settings dialog box.