Enhance your Microsoft Excel Pivottables with Pivotchart and slicers

Creating a Pivot Charts

Pivot Table

In the previous two articles in this series, we looked at creating a Pivot Table in Microsoft Excel based on textual data such as the file extension or author name, and investigating date information.

At the end of the second article, we created a PivotTable, grouped by year, quarter and month. In this article, I’m going to add some graphics to this, and a bit of interactivity, to allow the user an easy way to filter on the results he or she wants.

So, firstly, let’s create the chart. It’s very easy.

Firstly, click inside your existing PivotTable.

Secondly, go to Insert – Charts and pick a chart.

That’s it.

Line Chart

With just those two steps, I have converted my table into a chart. Remember I said that in my last article that there seemed to be information lacking from 2008, 2009, 2011 and 2012. Well, that’s brought vividly to life in this presentation. No wonder people say that a picture is worth a thousand words.

If you want the chart to be in a separate spreadsheet within your workbook, just right-hand click on it, click Move Chart and select “New sheet” and press OK.

Pie chart

Of course, that’s not the only type of chart you can create. A pie chart allows you to see what percentage a particular year is of the entirety. Instead of selecting line chart, I chose pie chart, then right-hand clicked on it and selected Add Data Labels – Add Data Callouts.

Now, you can narrow down the date range by using the filters provided in the Pivot Table and Pivot Chart (which are linked – so a filter in the Table also filters the Chart). But that’s not completely obvious to the user – for starters, it’s not self-evident that the arrows indicate that you can filter in the first place. So let’s make it even more obvious.

Did you like this article? If so, why not master PivotTables with 5 HOURS of video – click here for more details.

Slicers

Insert Slicers

The next part of this article needs Excel 2010, 2013 or Office 365. If you have Excel 2007, then let’s see what you can have if you upgrade in the future.

To do that, click in either the Chart or the Table and go to PivotChart (or PivotTable) Tools – Insert Slicer. A new dialog box appears, asking what you would like the user to filter on. This is important; whilst you can see a date range, you might want to add more functionality by allowing the end user to be able to select (say) the Author(s) that he would like to concentrate on.

Now, while this table is based on the field called “Date”, and they are shown as being grouped by years, selecting the same date filter doesn’t give years; it gives months. Not January 2010, February 2010, but just “January, February” – as in, every single January, every single February.

To select a quarter or years slider, you have to go to the bottom of that dialog box.

Slicer shown

A new sub-window appears, with all of the years used in the Pivot Table. You can now select which year(s) you want to concentrate on by either:

  • clicking on a single year,
  • clicking on one year and then holding down the Ctrl button while you click additional years, or
  • clicking on the first year and the holding down the Shift button while you click on the final year, to get all of the years in between.

For instance, this is the result if you select the years 2004-2008. Note that the PivotTable and the PivotChart have both been updated using the single slider.

Slicer with Filter on date

To remove your selection, and to show all of the years ago, press the little button with a red x in the top-right of the slider.

In this instance, we have grouped the information by date. If you choose a different slider, say the metadata “Album artist”, then only the years which have the select artist are shown.

Slicer with Filter on Album

In just a three clicks, you can set up your Pivot Chart from your Pivot Table, and another four clicks your Slicer can be available. An immediate graphical presentation and interactivity with just 7 clicks.

If you liked this article, please share or like it using the buttons to the top-right or left of this page.

Want more? Why not master PivotTables with 5 HOURS of video – click here for more details.

Next Article

In the next of these articles, we’ll be adding a timeline to create additional interactivity.

Additional articles.

Leave a Reply

Your email address will not be published. Required fields are marked *