A tutorial on using pivot tables in Excel based on SSAS tabular models
Part seven 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
  6. Drill-down and Quick Explore
  7. Slicers (this blog)
  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.


A slicer gives you complete control over what data you're looking at in a pivot table:

Slicer overview

Here we've used the CTRL key to look at amphibian and reptile product sales only.


A slicer is no different from adding a filter to a pivot table - it's just much easier to use, and looks prettier!

The rest of this page shows how to create and format slicers, and how to control multiple pivot tables using the same slicer (which is as impressive as it sounds).

Adding slicers

Begin by creating a pivot table as usual:

Normal pivot table

Here we're initially showing total quantity sold by centre type and quadrant.


On the Analyze tab of the ribbon, choose this option:

Insert slicer option

Click on any cell in your pivot table, then click on the Insert Slicer tool on the Analyze tab of the pivot table.

Tick one (or more) fields for which you want to create a slicer:

Choosing slicer field

You can use the CTRL key to select more than one box, to create more than one slicer at a time.


The initial slicer isn't that helpful, as it's only one column wide and appears in a strange place - time to change this!

Formatting slicers

Usually the first thing that you'll want to do is to change how many columns a slicer displays:

Options / columns

Click on the slicer, then choose to change the number of columns on the Options tab (here we've chosen 2 columns).

It's then up to you to create a space in which to put your slicer:

Inserting rows

There's no magic feature I've ever found to do this - inserting rows normally does the trick!


You can now drag your slicer into its new home:

Resizing slicer

You can also click and drag on any handle at the edge of a slicer to resize it (as I'm doing here).


Want a more exciting slicer?  You could change the slicer style:

Slicer style

Click on this symbol on the Options tab of the ribbon to show an even wider range of styles, or even create your own!

Controlling multiple pivot tables

One of the best things to do with a slicer is to use it to filter several filter tables:

Multiple slicers

Here the user has chosen to see data for Bird sales in the North region for Shopping Park stores.


To do this, first create one or more slicers and cut/paste them to the worksheet you want to hold them.  You could now clean up this worksheet:

View options

Deselecting Gridlines and Headings makes a worksheet look cleaner.


For my example above, I've also hidden any columns and rows not being used:

Hiding columns

Select the columns (or rows) you want to hide, right-click and choose this option.  None of this so far has anything to do with slicers, but I'm just trying to add value!


For the slicers themselves, it's a good idea to change their caption to be more readable:

Changing slicer caption

Here I've changed the caption from the default CentreTypeName to the more readable Centre type, using the Options tab of the ribbon.


It's also a good idea for each slicer to give it a better name:

Slicer name

Click on each slicer, and use the name box shown to give it a more sensible name (don't forget to press the ENTER key when you've done this).


It's also a good idea to give your pivot tables better names, to make them easier to locate:

Renaming a pivot table

Change the name in the Analyze tab of the pivot table, as shown here.


Now go back to your slicer, right-click on it and choose which pivot tables it controls:

Report connections

Right-click on a slicer and choose this option to specify which pivot tables it affects.


You can now tick which pivot tables the slicer controls:

Pivot table list

Tick the pivot tables you want this slicer's choices to affect.

Note that you can also tell a pivot table which slicers should control it (looking at the same problem from a different angle).  To do this, click in each pivot table and choose this option:

Filter connections

Select the Filter Connections option on the Analyze tab of the ribbon.


You can now tick/untick the slicers which you do/don't want to filter this pivot table:

Choosing slicers

Tick the slicers which should control this pivot table.

Note that you can do the same thing with timelines to control dates filters, but we'll have to wait till the blog section on calendars to see this.

For the final part of this tutorial, I'll just show a couple of things which you can't do in a pivot table based on a tabular model (but can do in a normal pivot table).

This blog has 0 threads Add post