New Excel Pivot Table features: Slicers and Sparklines

Slicers

This newer Excel feature is available in Excel 2010 and 2013. Slicers may be used in place of Report Filters to offer a more user-friendly method of filtering reports. To create a Slicer, begin with your cursor in any one cell inside your Pivot Table. Then, on the Insert tab or Pivot Table Options (Analyze, Excel 2013), locate the Slicer button and click it. You can choose any listed field for your Slicer. If you don’t see a list of your Pivot Table fields, it’s possible you did not first click inside the Pivot Table. Close the selection dialog box and try it again. You may choose multiple Slicers and position them wherever you like. To create a “dashboard” like selection center, insert a column to the left of your Pivot Table and widen it enough to place all your Slicers there. Slicers can be customized in size and appearance. With any Slicer selected, you should notice a Slicer Contextual tab at the top right of your ribbon.

Advanced tip: If you use Get­Pivot­­Data formulas to view Pivot Table results on another work sheet, you may position your Slicer on that work sheet. In other words, you can position your Slicer on a different work sheet than your Pivot Table. If you create a chart from that Pivot Table and also place it on that new work sheet, the Slicers will also affect the chart. While Report Filters can mostly take the place of a Slicer in an earlier version, it cannot be placed on a different work sheet.

Sparklines

Along with Slicers, Sparklines were also added as a feature. Sparklines enable you to place small graphs representing, for example, trends that can be integrated right with your data. So if you were reviewing a portfolio of product sales and determined that it wasn’t performing as well as expected, you could apply Sparklines to visualize the sales trends for each product to determine which might negatively affect the performance of the portfolio. To insert Sparklines:

1. Select the blank cells to the right of your data where you’d like to place Sparklines.
2. On the Insert tab, click a Spark­­lines button from the Sparklines group.
3. With your cursor in the Data Range field, select the entire range of cells that the Sparklines should represent. Be careful not to select column titles or grand totals.
4. Click OK to see the Sparklines.

Work-around for earlier versions

Copy and paste the values Pivot Table to a new work sheet. Next, create a chart from the first single row of data. Remove gridlines and the title, legend and axis titles using the Chart Tools Layout tab. Save this chart as a template from the Chart Tools Design tab, Type group, to avoid doing so many steps for each row. Name it something like “Spark Line.” For the remaining series, Select the next row of data cells, and using the Other Charts button on the Insert tab in the Charts group, select All Chart Types from the menu. Then, select Template and your newly created Sparkline template.