Pivot Table Styles and Presentation

Pivot Table Styles and Presentation

Previous Session Summary

In this previous lesson we looked at how we can change the layout of a Pivot Table by moving the fields around the Field List Rows and Columns panes.
We also looked at how to delete a pivot table, how to remove subtotals columns or rows, and how to ensure that the Rows and Columns field labels are correctly populated without blank

Lesson Synopsis

In this lesson we look at Pivot Table style and formatting. We cover how to turn a compacted pivot table (where all the fields in the Rows quadrant are in a single column in the Pivot Table which can be expanded or collapsed) into a tabular format (where all the fields in Rows or Columns occupy their own separate column).
We also look at how change the formatting of a pivot table, such as changing the colour and border options by using one of the preset styles or, how to create our own individual style of pivot table.
Finally we look at how to remove Grand Totals Rows and Columns as well as other options in the Pivot Table Design tab.

Resource Listing

The Video

Play Video

Lesson Notes

Now that we’ve got a good grip on how to change the field’s around in order to include different data, and we’ve got the data that we want in the pivot table, we’re going to concentrate a little more on style.

Repeat Item Labels

Firstly, in the current Pivot Table we can see that there are some blank spaces in the Rows and Columns fields in the Pivot Table. We may want to adjust this so that all Rows and Columns are correctly labelled with no blanks.

 To this, bring up the field list by clicking on the Pivot Table and then left click on the field where there is blanks in the Row or Column quadrant and select Field Settings. From the resulting pop up menu, select the Layout and Print tab. Finally select “Show items in tabular form” and select ok. If you’d like there to be no blanks in all Rows and Columns quadrants, you can also go to the Design tab, then to Report Layout and finally select Repeat all Item Labels.

Changing Pivot Table Style

To change Pivot Table style, ensure that the pivot table is selected, then go to tab “Design”, and on the ribbon, you’ll see the section “PivotTable Styles”. Select the bottom arrow which looks like an eject button and this will show all of the available preset styles. To change the Pivot Table to a new style, simply select the new style that you like.  You can also generate your own style if you’d like to, using the option New Pivot Table Style.

If you want to develop your own through the New PivotTable Style option then these will then be reflected through in the Custom section of the PivotTable Style options.

To amend a previous Design, select the tab Design, and then click on the expand button in Pivot table styles. Right click on the Pivot Table you’ve created and that you’d like to adjust and select Modify. Through the modification options, you can change, fills, borders fonts in terms of styles and colours to suit your requirements.

The Layout section of the Design tab

In the Design tab, next to the PivotTable Styles templates, there is a section called Layout.

This section provides quick shortcuts for adding in and removing blank rows, subtotals and grand totals without having to right click on the field of the pivot table field list. You can add Subtotals and Grand Totals into the Pivot Table either on Rows or Columns, simply by selecting the relevant option in the Subtotals or Grand Totals buttons

The Report Layout dropdown provides some options for either having the table in a tabular format, so where all titles are repeated, and gridlines are on, or where it’s compact which is where titles are not repeated, and gridlines are removed. You can also opt to include or remove item labels for all rows and columns through the Report Layout dropdown.