Formatting a pivot table and choosing display options

There are many ways to improve (or otherwise) the appearance of a pivot table.  Let's start with changing the pivot table style.

Pivot table styles

You can click in any pivot table and choose to apply one of the pre-set styles:

You can choose one of the built-in styles, or even create your own corporate style using the link at the bottom of the dialog box (shown selected here, but not explained further in this blog).

What happens then depends on which of the options you have selected on this ribbon tab:

You can tick any one of these four parts of a pivot table - the results are shown below.

Ticking Row Headers and Column Headers makes the top row and left column appear in a different format (as set by the pivot table style chosen):

A pivot table with row and column headers formatted.

Ticking Banded Rows will show every alternate row in a different format (Banded Columns does the same with alternate columns):

This is what banded rows might look like (setting both banded rows and banded columns creates a checkerboard effect).

Choosing to display or hide totals

The following pivot table has both row totals and column totals displayed:

The row totals are on the right in green; the column totals at the bottom in orange.

The easiest way to choose what to display is to click on any cell in the pivot table then choose the following tab:

Click on any cell in the pivot table, then click on the Design tab.

You can now choose the combination of rows and columns to be displayed:

This option would display both row and column totals (not surprisingly).

Displaying or hiding subtotals

You can choose the following option from the Design ribbon tab shown above to show or hide subtotals:

The subtotals menu options - see below for what subtotals look like.

Showing subtotals at the bottom of a group adds (as you'd expect) an extra row to each group (where there is one):

I've shaded the subtotal rows to show what they look like.

Hiding or displaying captions

Which of these two pivot tables do you prefer:

Without field headings	With field headings

According to your answer, you should select or deselect the Field Headings tool on the Analyze tab of the Excel ribbon:

Here I've decided I prefer the left-hand pivot table, without field headers.

Other options to play around with

There are many more ways in which you can customise how a pivot table looks, most of which you can set in the pivot table options dialog box:

Click on any cell in a pivot table, then right-click to bring up the short-cut menu and choose this option.

Here are some of the options you can set:

There are many tick boxes you can experiment with on the Display and other tabs.

The next part of this blog looks at two ways in which you can analyse the number in a particular table cell: drill-down and quick explore.