Join Nate Makdad for an in-depth discussion in this video Inserting slicers, part of Creating Interactive Dashboards in Excel 2013.
- Slicers are an easy to use Excel object that allow you to change the filters to one or many connected pivot tables. They play a really important role in this dashboard concept because the filters are going to allow us to see only the data that's relevant. It's going to allow us to add and replace filters quickly by clicking on the slicer options and then apply our selections across all of our pivot tables at once and all of our charts at once so that we can analyze all of our metrics by using the single click. Slicers are available for PC users in Office 2003 and greater and for Mac users it's slated in the newest office release, Office 2016.
So why use slicers? Slicers are quick to learn and fairly intuitive so they immediately give your users a lot more analytical power. They also remove the need to create replicas of the reports so this is about kind of removing that bottleneck to data because we're going to give our viewers the power to filter down the data in a very quick fashion. They also let us do quick analysis. So because of that filtering capability across these metrics as we start to identify outliers, we can filter around it or filter on it so that we can start to really hone in on our data. So they work both as a get me down to the level of data I need, but also work as an analysis tool.
Slicers also work across our multiple tables. But they only work across these multiple tables because they're all coming from the same data file. So if I had multiple sheets here or I had two different tables, I wouldn't necessarily be able to apply my slicers across all of those because the data needs to all be sitting on the same data table. So before we get started we're going to add our bar charts in for our other two pivot tables. So I'm going to click on our new openings and I'm going to now go to Insert and my bar chart.
And then I'm going to do the same for our average days open. I wanted to do those first because it's going to help us see the power of the slicers. But then the next step is going to be to click again on the pivot table, and I'm going to want to go up to Insert and then underneath Insert under Filters, there are the slicers. So once I click on the Slicer button, it's going to give me all of my available slicers and these are just coming from the Pivot Table Field list. And so I know from my requirements gathering that I need Agency, I need Borough, I need Division, I need the Highest Degree listed in the job requirements.
I also need the Posting Type, the Work Location, and then so though our snapshots are all going to be the same here if you were trending data or building in a time series these would be very valuable pieces because then people can filter on the time range that they want. So I'm just going to go ahead and add them because time series are usually pieces of information that we've got to add to reports, and I'm going to press OK. So now that I've got my slicers added into the report when you first insert the slicers off of a pivot table they're actually only connected to the one pivot table.
So we need to connect our slicers to the other pivot tables. I can do this by selecting one of my slicers. I'm going to start with Agency, and then I'm going to right click and go to Report Connections. And then I just need to check the boxes next to the other two pivot tables. So I'm going to continue to do that for the rest of my slicers. Now you can also force Excel to do this for you by inserting slicers first and then copying and pasting the pivot table.
I didn't go that route because I was demoing some of the other options of the pivot table but we will see that in a later step. Okay, now all of my slicers are connected together and connected to each of my pivot tables. So now we want to move to the Controls section of our template because if you remember we have our foundation, our controls and then our content. And slicers are going to act as our controls or part of our controls. So I'm going to go ahead and click on each slicer. First I'm going to start with Agency and then I'm going to hold down the Control button and select Borough, Division, Degree, Post Type, our Snapshots, finally Work Location.
I find it easier to move them just by hitting Ctrl+X and then scrolling up. And now I'm going to go to A4 and hit Ctrl+V to paste them in. Now I also want to align my slicers in a way that I think is going to make sense to people coming in. So I usually start by doing business first or organizational hierarchy first, and then regions, and then time. You may find that you like to organize it a little bit differently. That's just the way I'm going to organize mine.
And then I'm going to add my Highest Degree type and Posting Type. And you'll see now we've run out of room. Now one of the things is that as we built our original content layers, we knew that we might need to expand them or we suspected maybe our total sections may not have been big enough. So I'm going to just add an extra couple of rows for the next round because I'm going to go ahead and put these in.
And then I'm also going to trim my top section just a little bit because I now want to put in some text around the fact that these are filters. So I'm going give a little bit of help text. I'm going to make this a little bit bigger. I don't want it quite as big as my title. I want to create a little hierarchy in the differences here but I want it bigger than what's in the slicers. So I'm pick 20 at about halfway to give it the right view. So now I'm going to go ahead and type in my Filters and then I'm just going to say, "Make Selections to Filter Data".
Now I want to go grab my charts and bring them up so we can see the slicers in action. I'm going to put them in our content layer. And we can continue to work with where these charts might go. But now if I take a look at say our slicers up here as I pick on Admin for Children's Services you can see all of our charts are adjusting. And as I make additional selections and I can add by doing Control, we're going to add an additional data, our charts are going to continue to add values.
I can also remove values if I go to where I have a selected value in this dark blue. Say I want to get rid of Outside City or N/A, now I'm going to remove those so I'm only seeing the data associated with my selections. So the white here is I've deselected it. So just the last point to make really around slicers here is once I've made an active selection you'll see the colors start to change in slicers, and so these are visual cues around what's still available in the data. The dark blue means these are all available selections. The lighter color has been filtered out, so I can't pick these because they are not available based on my Agency selection.
And when I want to back this out, I can go up to where I've made an active selection in the upper right hand corner and hit Clear Filter. So filters and slicers are really one of the two main components of our dashboard and provide an easy and intuitive way to build flexible filtering into our report.
- Defining your requirements
- Setting up data
- Adding reference tables
- Creating hierarchy in the layout
- Choosing color and typography
- Importing data
- Inserting slicers
- Recording a macro
- Editing VBA code
- Improving the user experience