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