Excel 2010 & 2013 Data Slicers Taking Pivot Tables To A New Level Of Control

August 13, 2013 admin 1 Comment

If you are a Microsoft Excel power user then you have probably been using pivot tables for quite some time. Pivot table functionality is among some of the most powerful features within Excel because it can turn a flat table of raw data into a powerful database with the ability to move data from rows to columns and to the page by position with just a couple of clicks of a mouse. In addition, a good pivot table user can do some interesting things such as creating formulas that exist within the pivot table or taking a field such as dollar sales and creating a dollar share with that same field. If you are a really creative Excel pivot table user then you have probably created various templates using, but hiding, pivot tables so that you can arrange your data in many creative ways such as a dashboard, a controlled tabular report or even a combination of both of these elements.

In Excel 2010 a new functionality was added… Data Slicers. Slicers are one-click filtering controls that narrow the data shown in PivotTables and PivotCharts. Slicers can be used interactively to display data changes when you apply filters. For example, you can create a PivotTable report or chart that shows sales by year, and then add a Slicer that represents promotions. The Slicer is added as an extra control in the PivotTable or chart, and lets you quickly select criteria and instantly show the changes. You could also embed the breakdown by promotion in the report itself, by including the field in the row or column heading, but Slicers do not add extra rows to the table, they only provide an interactive view into the data.

Data Slicers

The new Excel Data Slicer functionality really opens up how you can build a more intuitive templates that clearly identify which items are included or excluded from a particular pivot table view. Formerly, using only pivot table fields it was not as easy to see which items were included or excluded from a particular filtered view. In the following example it is easy to see which sizes, type, product category, flavors, geography and periods are included in the data portrayed on the left side of the report.

Data Slicers are available only in Excel 2010 and Excel 2013 and are very easy to use and create as the following steps will demonstrate:

Create your pivot table as you normally would do
Layout a consolidated report such as the following –
Click anywhere in the pivot table and click on the Pivot Table Options (Excel 2010) or Analyze (Excel 2013), and click on the Insert Slicer option –
Place a checkmark beside each pivot table element you want to have displayed as a Slicer, by left clicking in the box to the left of the element and click the OK button when done –
Excel will insert a Slicer for each of the pivot table elements you placed a checkmark next to –
The Slicers can be moved by dragging them around on the screen and grabbing the handles on the sides, top, bottom or corner to change their size. You will note in the example above that all of the elements for each Slicer are highlighted so the data display on the left has no filter and is displaying all data for all geographies, time, sizes, type and product category at one time.
By clicking on the following criteria – Sizes = 16, Type = Half & Half, Geography = Denver and Time= Latest 52 Weeks…the data will be filtered now to display those specific settings, and you will see only two items in the Product Category are now highlighted –

Timelines

These days everybody boasts of a massive spreadsheet. But almost no one needs all the data at same time. We are always filtering data for the latest quarter, 6 months starting Mother’s day or 8 weeks from November 1st etc. Of course, you can use auto-filter and select all the dates. But it is a pain. Thanks to Timelines, filtering for dates is a breeze. You can add timelines for any date column in a pivot table / pivot chart. I am sure your internal clients & bosses will love it.

Really, the Timeline (only available in Excel 2013) is just another type of Slicer that only operates on date formatted fields. You can set up and use the Timeline Slicer in the following manner:

Set up your pivot table and Data Slicers in a manner outlined above
Click anywhere in your pivot table and click on Pivot Table Options (Excel 2010) or in Analyze (Excel 2013), and click on the Insert Timeline item in the Filter section of the ribbon. If you have pivot table fields that are date formatted those date fields will be presented as optional fields to create a Timeline from
Select the date formatted field you wish to make a Timeline from; click on the box to the left of the field to place a checkmark and then click on the OK button –
Excel 2013 Will Pl. a timeline Data Slicer onto your spreadsheet and you can move it around and change the size of it as we discussed above and the Data Slicer section. When the timeline is displayed all available fields are highlighted, however you can adjust and select only the periods that you want to display in the pivot table report.
The Timeline slicer can be viewed in year, quarter, months or day aggregations to limit the data viewed in the pivot table report. For the purposes of this example I will select the month option. In the following example we have filtered on one market and set the geography slicer and one product in the product slicer, and we have selected the months of May and June. We can control the slicer by grabbing the ends of the slide along the timeline so we could click on May and then grab the right end of the slider and slide it to June –

As you can quickly see, the Microsoft Excel Data Slicer and Timeline filter functionality provide a powerful user interface to allow users with little to no pivot table experience to quickly and intuitively filter on the data they want to see.

Insight, Information & Consulting Services, Inc. is a data visualization consulting firm that helps its Consumer Package Goods clients better “see” their data. We deliver data visualization tools from cost-effective Excel & PowerPoint templates to a turnkey data visualization tool that will allow your users to see their data in PowerPoint templates using only a web browser. For more information, please visit our website at www.insightinformation.net or call Rick Pensa at 770-425-4243.