Setting up a Simple Interactive Dashboard Using Excel 2010 “Slicers”

This Tech Tip was submitted by Jim Fergerson, Director of Institutional Research and Assessment, Carleton College, Northfield, Minnesota.

Excel 2010 introduces a new “slicer” feature that makes it much easier to interact with and filter data contained in pivot tables.  Slicers make it easier to visualize what fields are being filtered, and they make it easy to set up a simple dashboard to explore your data.  Furthermore, when sharing data with others, your collaborators may find that the slicer’s visual “button interface” for selecting information is much more intuitive than interacting with standard pivot tables.

An IPEDS reporting example:  Below is a bare bones subset of a dashboard that I developed to help prepare our IPEDS Human Resources report.  I needed to be able to quickly review which employees were included in various categories without constantly drilling down to change the values or fields in the pivot table “Report Filter” (the upper left set of fields in a pivot table dialog box).  I also needed a way to make it very easy for Human Resources staff to interact the data to verify my employee lists.

Here, I’ve used several button filters to find all “Postsecondary Teachers in Biology, Chemistry, and Economics who are full-time, tenured, professors.”  I’ve also added an AGGREGATE function [1] to provide a count for whatever group I’ve currently selected. (I could also have added other functions to show average salary, average age, etc. for the selected records.)

TechTipGraphic1.PNG 

  1. Start with your cursor inside an existing pivot table. The “PivotTable Tools” menu (A) will become visible.
  2. Go to “PivotTable Tools/Options/Insert Slicer” (B).
  3. In the “Insert Slicer” box, check the fields you’ll want to filter on, one slicer per field.
  4. Position and drag to re-size the slicer(s) as appropriate for your needs. Once you have selected a slicer, you can apply a variety of custom formats using the “Slicer Tools” menu (C).
  5. Select buttons to filter as needed on one or more data fields.  (Control-click to select multiple items within a slicer.)
  6. Remember to “turn off” your filters using the box in the upper right of a slicer when you no longer need it!

TechTipGraphic2.PNG

My “ah ha!” moment was when I discovered that I could even use a single set of slicers to control multiple pivot tables to show different views of the data, as long as you work from a copy of the master pivot table.
 
For more information, visit YouTube and search on "slicers in excel 2010." Additionally, MicroSoft has documentation on "slicers to filter PivotTable."
 

[1] See Jim Fergerson, “Aggregate Function in Excel 2010”, in eAIR February 2013, http://www.airweb.org/eAIR/techtips/Pages/AggregateExcel.aspx.  

  
 

 Comments

 
To add a comment, Sign In
Total Comments: 13
 
Raj posted on 3/14/2013 9:54 AM
Great tip!! Cannot wait to use it for the summer reports!
Dale posted on 3/14/2013 10:11 AM
I look forward to utilizing the slicer feature with some of my reports. Some of my end users will be ecstatic.
Norma posted on 3/14/2013 10:38 AM
Can't wait to use this. It should make some of the reports easier to setup
Laura posted on 3/14/2013 10:42 AM
Great Tip! I am still running Excel 2007, but this is something to look forward to. I love pivot tables and this makes them that much better.
James posted on 3/14/2013 11:07 AM
So handy! Hope to see more like this.
Melanie posted on 3/14/2013 11:27 AM
Aweseome, easy-to-use tip! I'm only disappointed that I've had Excel 2010 for this long and haven't been using it all along.
Jim posted on 3/14/2013 11:41 AM
Addendum: Jim Fergerson--I just met with the HR and Dean of Faculty people to discuss the draft of the IPEDS-HR survey,and by filtering on the right slicers together, we could quickly browse to any employee and identify any last-minute coding issues that needed fixing. We are ready to lock!
Evelina posted on 3/14/2013 11:48 AM
This is such a great tool! We are just half-way through the new HR report and I will apply this right away. The opportunity to share data "processing" with other offices outside of IR is invaluable!
Kelly posted on 3/14/2013 11:55 AM
Great diagrams! Very practical and useful tip to make excel reports more manageable! Thanks.
Gary posted on 3/14/2013 12:28 PM
Nice job - I would imagine that for certain people this will make the difference between using or not using pivot tables.
Rob posted on 3/14/2013 1:07 PM
This is an excellent example of using common tools for business intelligence purposes. Thanks for sharing this with us Jim.
Terry posted on 3/15/2013 11:28 AM
I can't wait to use this one. My only problem will be alloting too much time to this exercise.
Deressa posted on 3/19/2013 2:09 PM
Excellent tip! We have an analytics software program that uses slicers, but I have never used them inside of Excel. I will definitely try this.