Slice and dice
istock_PeopleImages

EZ guide to PivotTables – slice and dice data

by
9th Mar 2017
Partner The Knowledge Base
Share this content

Simon Hurst sharpens the power of PivotTable slicers to carve data in a few mouse clicks and drags.

Objective

The first part of this series concentrated on why rather than how and sought to show that PivotTables can often be the quickest and simplest way to solve common Excel problems.

We then moved on in part 2 to look at the structure of a PivotTable in more detail as well as considering how data should be structured to work successfully as the source for a PivotTable. This time, we are going to consider how PivotTables can be used interactively to investigate the story behind the data.

The data

Last time, we used a PivotTable to create a top-ten league table of average house sales values in England, Scotland and Wales with just 17 mouse clicks using publicly available data.

We used the first CSV (comma separated value) file in the list - Average price.csv - and downloaded it straight into an Excel workbook before turning it into a PivotTable.

At the end of part 2 we left our PivotTable with a Years filter field, with more than one item selected, showing the unhelpful 'Multiple Items':

Slice and Dice

Before we unleash the full power of PivotTable Slicers to address this issue, and a great deal more, we will consider a few other aspects of PivotTable interactivity.

Manipulating a PivotTable

Introduction

Anyone who's had the unenviable tasks of producing printed reports for a large audience (where 'large' is any number greater than one) will probably have experienced multiple requests for the reports to be presented differently: summarised by this rather than that; shown with more detail; shown with less detail; showing a comparison of different periods. One of the great benefits of PivotTables is the ability to 'slice and dice' data in many different ways with just a few mouse clicks and drags.

Click and drag

You can choose the data to summarise, and the way it is summarised, with a simple click or two. Selecting the check box next to an item in the PivotTable Field List will add it to the PivotTable as an additional item in the Values area for fields recognised as numeric or as Row labels for other types of field.

Fields can be also dragged from the Field List directly into any of the four PivotTable areas and fields already in one area can be dragged to a different area or dragged outside of any area to remove them from the PivotTable itself (but leave them accessible in the Field List). Where there is more than one field in a single area, you can drag field up and down within the area to change the order. For example, in the Rows area, fields will be grouped in order – the top field in the area will be used for the first level of grouping, the next field down will be the second level of grouping and so on.

When fields are grouped, expand/collapse buttons will appear to the left of each item allowing the grouping to be collapsed and expanded to focus on the summary or to reveal the detail. The PivotTable Tools, Analyze Ribbon tab, Active Field group includes buttons to collapse or expand all the items within the active field in one go:

Slice and dice

Dropdowns

The more complicated the data you have to analyse, the more you may need to apply filters to concentrate on one area at a time. One way of choosing how to filter your data is to use the dropdown for each field placed in the Filters area. This allows for the selection of individual or multiple items to be used a filter criteria for the PivotTable. In addition, each field heading in the Rows or Columns area includes a dropdown with relevant sort and filter options. Depending on the Layout, each field will have its own dropdown or for the Compact Layout there will be a single dropdown that includes the ability to select the required field:

Slice and dice

The importance of sorting shouldn't be underestimated. Often the simplest way to convey the message behind your data is to sort it by value rather than the default alphabetical sort, immediately drawing attention to the most important items rather than those that begin with the letter 'A'.

Right-click

Any cell within a PivotTable can be right-clicked to access relevant options. This is perhaps most useful for cells within the Values area. The right-click menu for such cells allows you to select the aggregate functions used to summarise the data such as SUM, AVERAGE, MIN and MAX. You can also apply a number format to all the cells belonging to that field in the Values area.

Before bothering with any of the fancy formatting available within a PivotTable, make sure that the number format is as clear as possible (don't show numbers to excessive levels of precision, show zeros as dashes, don't clutter by incorporating currency symbols for every number where they are all the same currency).

Drill Down

Finally, it is possible to see the detailed transactions that make up a value in the Values area just by double-clicking on the relevant cell. This will insert a new worksheet containing all the rows from the full underlying data source (not just the fields added to the PivotTable itself). The PivotTable remains unchanged on its original worksheet – it's a good idea to give the sheet that the PivotTable is on a sensible name to ensure it can easily be found after any drill down.

Slicers

Although most of the above methods of working with PivotTables are reasonably straightforward once you know they exist and how they work, there is way of dealing with filtering that is even more intuitive and visual.

Slicers were introduced in Excel 2010 and enhanced in Excel 2013 when the Timeline Slicer was introduced to provide an even more intuitive way to work with date and time filters, and the use of basic Slicers was extended to Excel Tables as well as PivotTables.

In essence, Slicers are very simple. They just provide a more visual way of filtering the data.

Because PivotTables expand to the right and down, Slicers should generally be placed to the left of, or above, a PivotTable. To achieve this, it's a good idea to insert a row above your PivotTable and a column to the left of the PivotTable and make the row deep enough, and the column wide enough, to accommodate your Slicers.

To add Slicers to a PivotTable, just select any cell within the PivotTable and use Insert Slicer or Insert Timeline from the Filter group of the PivotTable Tools, Analyze Ribbon tab. A Slicer allows you to select one or more fields from the full field list for the PivotTable but the list of fields available for a Timeline will be restricted to date and time fields. Here we have added Slicers for Region_Name and Years in our inserted and expanded column A and a Timeline based on the Date field in row 1:

Slice and dice

Note that Insert Timeline only allows for Date and Time fields to be used, and that our PivotTable is currently filtered to show only the top 10 Region_Names.

In our example, we have included the Years Slicer on the left to show how it displays the three years selected in the Years filter dropdown rather than just 'Multiple Items'. However, the Timeline is flexible enough to allow us to dispense with the Years filter and Slicer altogether. At the right of the Timeline a period dropdown allows you to switch between Years, Quarters, Months and Days:

Slice and dice

Here we have changed our Timeline to group by years and then dragged from the 1995 train carriage to the 1997 one.

Slicers and Timelines also have their own Options Ribbon tabs. We have used the Columns option in the Slicer, Options Ribbon tab to display three columns and made column A wider to show more of each region name.

Items can be selected in both types of Slicer by clicking individual items, clicking and dragging to select multiple contiguous items or clicking while holding down the Control key to select multiple items that are not necessarily contiguous. Both types of Slicer include 'Clear Filter' buttons in the top right-hand corner and a standard Slicer also includes a 'Multi-Select' button. When this is selected, multiple items can be selected, or deselected, without needing to use the Control key. This has obvious advantages for tablet and smartphone users. The Timeline includes the additional ability to drag the end of a selection to extend it left or right or to hold down the Control key and drag the existing number of items selected forward or backward in time.

Timelines and dashboards in spreadsheets

We have yet to reveal the most important Slicer feature. Slicers and Timelines can be connected to multiple PivotTables as long as those PivotTables are based on the same data source. Here we have created two separate PivotTables both based on our original table of data. Just for illustration purposes, we have included a pasted picture link of our second PivotTable on the same sheet as our first table:

Slice and dice

We can right-click on a Slicer and choose Report Connections to connect our Slicer to multiple PivotTables. It's useful to give your PivotTables descriptive names as well as naming the worksheets that they are on. By selecting multiple PivotTables we can control them all from a single Slicer:

Slice and dice

Next time

Slicers give us the power to go from the above example to something like this:

Slice and dice

Next time, we'll see how.

 

Replies (5)

Please login or register to join the discussion.

avatar
By tomnqm
17th Mar 2017 11:28

Excellent couple of posts, I use pivot tables quite a lot but you're approach has shown me a couple of new tricks and would be a great way to introduce newbies to the power of pivot tables. Many thanks!

Thanks (0)
avatar
By tomnqm
17th Mar 2017 11:28

Excellent couple of posts, I use pivot tables quite a lot but your approach has shown me a couple of new tricks and would be a great way to introduce newbies to the power of pivot tables. Many thanks!

Thanks (0)
Simon Hurst
By Simon Hurst
17th Mar 2017 12:57

Many thanks for your comment, certainly worth repeating.

Thanks (0)
avatar
By PeterJK
25th Mar 2017 20:57

Hi I'm returning to EZ after an absence.
Have you covered formatting pivot tables?
I find that Pivot tables lose formats on refresh.
Any advice?
PeterK

Thanks (0)
Replying to PeterJK:
Simon Hurst
By Simon Hurst
27th Mar 2017 07:54

Hi Peterk. No, you haven't missed any discussion of formatting apart from applying a number format to the values area which was covered in part 1. There are options for preserving formatting and column width in the right-click menu, PivotTable Options, Layout and Format section. Are you finding that PivotTabls are losing formatting after refreshing in spite of these and, if this is the case, which particular formats and which version of Excel?

Kind regards

Simon

Thanks (0)