Show off your data in an Excel PivotTable with Slicers and Sparklines

Excel 2010 PivotTables are awesome! Excel 2010 PivotTables with sparklines and slicers are double awesome! You can follow along with me by downloading my example spreadsheet Excel PivotTable with Slicers and Sparklines workbook file. It contains sales data with outdoor gear products. My objective is to report  which products are selling best and what months are they peaking at. Sparklines will do the trick on this one. Sparklines are mini-charts in one cell that can show trends. I also want to see which how each store is performing. I will use slicers to interact with the data set. Slicers are interactive filter buttons.

I first begin with my spreadsheet. I create a Table (Control + T) with my data  because Tables make is super easy to add columns or rows and the data in which the goal is to refresh the data in the PivotTable report.

After I create a Table, I click the Insert tab and then click PivotTable.

PivotTable Excel 2010

I get a dialog box which I confirm the Table/Range I’m working with and the location of the PivotTable report. I  go with the default choice to have it on a new worksheet.

Create PivotTable Dialog Box

The first thing I do is place the fields I want to report in the respective areas. I can drag or check a box next to the field I want to report. Clicking the check box next to the field that contains numbers will be placed in the Values area. This is the heart of the PivotTable and the data will have the Sum function at all the intersections of the filters. I can drag a field column to to either the columns labels (stretch to the right) or the report filter is which will act as overall umbrella filter. The default check box for a field that has text data type will be placed in the rows section.

In the example below I dragged Store Number to the report filter and drag the Month of Sale field to the columns area. I used the MONTH function to obtain the month of sale prior back in the Table as a calculated column. Then MONTH function only has 1 argument, the date. The value results with 1-12 to represent the months. This function is perfect to connect my line sparklines with.   I clicked the check box for Product Name and Total and they automatically went to the Row Labels and Values respectively.

PivotTable Field List

Next I format the cells by right clicking one of the numbers in the PivotTable and choose Value Field settings. I click the Number Format button and choose Currency format with zero decimal places. Save as much space as you can for the report. Not displaying 2 decimals makes up for a lot of space over a 12 month period in the columns.

Next I insert a few columns to the left to make room for the slicers and sparklines. From the PivotTable Options tab or Insert Tab, I choose Insert Slicer and pick Product Description and Store Number. I rearrange them so they will fit the nicely in the empty space in columns A-C. The Slicer Tools tab has more opportunities for formatting and changing how many columns are displayed.

To show what months are my highest months, I add sparklines. First I click to the left of my first product (cell D5). Then I click the Insert tab, then Line Sparkline. I use the auto-fill to copy the range down.

The following is a screen shot of my dash board report.

Slicer and Sparklines in Excel 2010

You can practice with the data I used by downloading my Excel PivotTable with Slicers and Sparklines file.

Happy report making with PivotTables, Sparklines, and Slicers.

About Steve Chase

I'm a proud husband and dad to 4 boys! Microsoft Certified Trainer and Boy Scout Leader are some of things I call myself. The Cincinnati Reds are my favorite team! When not outdoors, I enjoy working with documents in Microsoft Office, QuickBooks, Visio and Photoshop. My wife, Erin, shares her awesome recipes on her blog at
This entry was posted in Excel and tagged , , . Bookmark the permalink.

3 Responses to Show off your data in an Excel PivotTable with Slicers and Sparklines

  1. Pingback: The Top 10 Posts of 2012 on my 1 year Blog Anniversary | Steve Chase Docs

  2. Paranidharan B says:

    Hi, I am using 2013 MS Excel and I am unable to get decimal values (19.20% 20.10% 21.19% 20.56%) in a single cell using Sparklines. It comes as (19% 20% 21% 20%) Can you pls help me in getting the decimals correctly. Thanks in Advance

  3. Steve Chase says:

    Hi Paranidharan,
    I’m not sure if I understand your question. Is the issue with the decimals within the format of the cells. If so make sure you don’t have =ROUNDDOWN() function happening. If on the other hand, you could create a custom format as 0.00% or format cells as 2 decimal places.

    Sparklines will show the overall trends. It could be that you could select the sparkline and create a custom value for the vertical axis minimum value. To do so, go to the Sparklines Design tab after click on the Sparkline. Then choose Axis–>Custom Value and enter zero. This will adjust the column or line sparkline to reflect all zero base line.

    Hope this helps. If I’m not understanding your question please let me know.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s