A tutorial on using pivot tables in Excel based on SSAS tabular models
Part five of an eight-part series of blogs

Learn how to create, format and explore pivot tables within Excel based upon SSAS tabular models. This tutorial also shows how to create slicers, and combine them to create dashboards.

  1. Excel pivot tables in Analysis Services (Tabular Model)
  2. Setting up pivot tables in Excel
  3. Refreshing a pivot table
  4. Formatting numbers in a pivot table
  5. Formatting a pivot table and choosing display options (this blog)
  6. Drill-down and Quick Explore
  7. Slicers
  8. Things you can't do in tabular model pivot tables

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 24 November 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

Choosing a pivot table style

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:

Parts of a pivot table

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):

Row and column headers

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):

Banded rows

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

 

Note that not all pivot table styles affect all parts of a pivot table - for some styles, ticking some options has no effect.

Choosing to display or hide totals

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

Grand totals

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:

Design 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:

Row and column totals

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:

Subtotals menu

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):

Subtotal rows

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
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:

Field headers tool

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:

Pivot table options

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:

The Display tab

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

There are often two ways to do things to a pivot table: using the dialog box above, or using the ribbon.

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.

This blog has 0 threads Add post