BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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.
- Excel pivot tables in Analysis Services (Tabular Model)
- Setting up pivot tables in Excel
- Refreshing a pivot table
- Formatting numbers in a pivot table
- Formatting a pivot table and choosing display options (this blog)
- Drill-down and Quick Explore
- Slicers
- 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:

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

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.
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.
- Excel pivot tables in Analysis Services (Tabular Model)
- Setting up pivot tables in Excel
- Refreshing a pivot table
- Formatting numbers in a pivot table
- Formatting a pivot table and choosing display options (this blog)
- Drill-down and Quick Explore
- Slicers
- Things you can't do in tabular model pivot tables