Save content
Have you found this content useful? Use the button above to save it to your profile.
Dashboard
istock_guvendemir

EZ guide to PivotTables – an interactive dashboard

by
3rd May 2017
Save content
Have you found this content useful? Use the button above to save it to your profile.

Simon Hurst brings together all the techniques covered so far in the series to create an interactive dashboard, powered by Slicers, that allows us to examine our house price data with just a few mouse clicks.

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 two 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.

Part three looked at slicing and dicing PivotTable date by moving data around and applying filters before introducing Slicers.

The data

We are looking at the house sales values in England, Scotland and Wales using publicly available data.

We used the first Comma Separated Value (CSV) file in the list - Average price.csv - and downloaded it straight into an Excel workbook, before turning it into the PivotTables we created in the previous parts of the series.

The story so far…

At the end of part three, we had placed two Pivot Tables on the same worksheet and connected a normal Slicer and a Timeline Slicer to both PivotTables. We did this by right-clicking on each Slicer and choosing 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, to make it easier to identify the PivotTables that you want to connect your Slicer to:

Report connections

Charts v Tables

There is a significant issue with including more than one PivotTable on the same worksheet. Most PivotTables expand and contract as you update the underlying data and refresh, or change the criteria applied. As Excel will not allow one PivotTable to overlap another, this makes it difficult for PivotTables to share the same sheet. You might think that applying a top or bottom filter of a fixed number of items might be a solution, but even a PivotTable with a fixed number of items can expand or contract as a result of the same value items causing ties.

An obvious solution is to use a PivotChart rather than a PivotTable. Charts can show different volumes of data within the same physical frame. In addition, when dealing with large amounts of data, charts can often convey the underlying data story more effectively than a set of numbers.

Accordingly, we will create our dashboard using four charts based on four different PivotTables. Each PivotTable is based on our average house price data. Our four PivotTables are created as follows using the techniques covered in the previous parts of the series:

  • Sales by month – Average value as Value; Region as Column; Date, grouped by month, as Row
  • Sales by year – Average value as Value; Region as Column; Date, grouped by Years, Quarters then Date, as Row
  • Total Sales By Year – Average value as Value; Date, grouped by Years, as Row
  • Sales by Country – Average value as Value, Region as Row

In each case, we have gone to the PivotTable Tools, Analyse Ribbon tab and entered a descriptive name in the PivotTable Name: 

Picot table name

We then need to go our four PivotTables in turn and use each as the basis for a PivotChart. Click on any cell in our PivotTable and, from the PivotTable Tools, Analyse Ribbon tab, Tools group, choose PivotChart. We can then select the type of chart to use. Note that not all Excel chart types are available for use with PivotTable data. In particular, none of the chart types introduced in Excel 2016 can be created with data inside a PivotTable.

We will use our Total Sales by Year chart as an example. We have chosen to use a column chart. We could also use a line chart if we were more concerned with the trend over time than with the comparison of one or more years.

It's worth taking a little time to make sure that each chart you create is as effective as possible. As well as choosing the most appropriate chart type, getting rid of any unnecessary clutter and applying a clear number format can all help increase the impact and clarity of a chart.

Here is the default column chart produced from our PivotTable, with some of the content we can adjust marked:

Pivot chart original

Below, we have deleted unnecessary objects, changed the number format and added a more useful title, as well as reducing the Gap Width of our Data Series.

We deleted our legend by simply selecting it and pressing the delete key. The average of Average_Price and Years Field Buttons were removed by right-clicking on the Field Button and choosing 'Hide All Field Buttons on Chart'. We can click in the chart title and type in our chosen title (or type a cell reference into the formula bar to link the title to the text in an existing cell). To adjust the Number Format and Gap Width we can double-click on the relevant part of the chart and set the Options as required:

Chart options

Assembling our Dashboard

Note the Move Chart option in the Actions section of the PivotChart Tools, Analyse Ribbon tab. Once we have created each of the charts linked to our four PivotTables, we can insert a new worksheet and rename Dashboard. We can then select each chart and use Move Chart to place our chart as an Object in: our Dashboard sheet.

Once we have accumulated our four charts on our Dashboard worksheet, we can widen column A and increase the height of row 1 ready to accommodate our Slicers and then arrange our charts in the remaining part of the sheet.

We can click in any of our charts and use PivotChart Tools, Analyse Ribbon tab, Filter group to add our Region_Name Slicer and our Date Timeline Slicer as covered in part three. We can then right-click on each Slicer and choose which of our PivotTables to connect. In our example, we connect the Region_Name Slicer to three of our PivotTables, leaving out Sales by Country. Our Timeline Slicer is also connected to three PivotTables but this time omitting Total Sales by Year:

Slicer connections

Rather than aligning objects individually, we can hold down the Control key and click to select multiple objects and then use the Drawing Tools, Format Ribbon tab, Arrange group, Align dropdown to align objects precisely. Here we have selected our two top charts, and our Region_Name Slicer and aligned them to the topmost object:

align

We now have our interactive dashboard where our Slicers each control three of our four underlying PivotTables and therefore the charts that are based on them:

Finished dashboard

Next time

Having started with four episodes that seek to explain what the point of PivotTables is, in future parts, we will cover some of the less obvious PivotTable features and techniques, and see how they might be used in practice.

 

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.