Introduction
If you’re a Telecom Professional (and due to this, use Excel everyday 
) then this tutorial is for you!
If you’ve never worked with a PivotTable in Excel, you’re certainly missing out on one of the best features in the product. The ironic part is that many users actually avoid PivotTables, as the name makes them sound intimidating. But they certainly don’t need to be!
On top of the core functionality shown there, PivotTables are very versatile, and are one of the preferred ways to allow users to explore data, refining it to show the insights they need, and removing unneeded or irrelevant items with ease.
Laying the Groundwork: A simple PivotTable
Let’s take a look at an example, built from a table with the following headers.
Creating the PivotTable is relatively straightforward. We:
- Click somewhere in the data table on the “Data” worksheet
- Go to InsertàPivotTable and click OK
- From the field list that shows up at the right, we
- Drag Type and Brand into the Rows area
- Drag Value and Quantity into the Values area
- Drag Category into the Filters area
- We then:
- Click in B3 and enter “Sales ($)”
- Click in C3 and enter “Sales Quantity”
The PivotTable, as shown on both the worksheet and PivotTable field list, looks as follows:
(This is the view from Excel 2013, but it will look very similar in Excel 2007 or 2010.)
Using Filters
Now, what about this filtering ability? Believe it or not, it’s staring at you, just waiting to be clicked. Those little arrows… the ones that you probably wondered about… they want you to click them!
Let’s try it. The category filter (shown in B6) currently reads “(All)” as it is showing all records. If you click it you’ll see that it actually contains 2 values; Beer and Liquor. These are the only two unique values from the Category column of our source table. And selecting Beer from that list will give you a PivotTable filtered to show only items that belong to the Beer category:
How do you like that? Every record that doesn’t have a sales category of Beer is pruned away, leaving only Ales and Lagers on our PivotTable. We could drill even further into this table by clicking the drop down arrow in cell A3, showing us only the records for Lagers or Ales, hiding the rest of the results from the Pivot.
The root issue with Pivot table filters
The ability to filter a PivotTable is fantastic, but again, they have one big issue: accessibility. When we create a PivotTable and send it to someone else, if they aren’t familiar with how PivotTables work, they may not feel comfortable drilling through and seeing what exists. These buttons certainly don’t invite you to use them, and if they never get used, then the value is lost.
Enter the “Slicer”
It sounds like the title of a horror movie, but in Excel 2010 Pivot Table filtering changed forever (and for the better) with the introduction of “Slicers”. Simply put, Slicers are new tool built to make Pivot Table slicing easier and more inviting. Unfortunately, however, the name is both accurate to its function, and scary sounding.
There are a total of 3 things that you need to know in order to use Slicers in your data sets:
- How to create them. Fortunately this is pretty easy.
- How to use them. Again, easy.
- How to take praise when you become the office hero. Sorry, you’re on your own with this one!
In order to add a slicer, you’ll need two things:
- Excel 2010 or higher, and
- A PivotTable
Creating a Slicer
Creating a slicer is super easy. Click any cell inside your PivotTable, and:
- Excel 2010: Go to PivotTable Tools à Options à Insert Slicer
- Excel 2013: Go to PivotTable Tools à Analyze à Insert Slicer
You’ll be prompted with a list of all fields that are available to your PivotTable (all the headers from your original data table). Just check the boxes of the fields you’d like slicers for, as shown in the image below:
Next you click OK, and they appear on your worksheet:
Now tell me, aren’t those MUCH nicer to look at than the little drop down arrows?
Using Slicers
Okay, so we have a slicer now. And it’s pretty. But does it do anything? Of course!
Click Beer:
Do you see what happened here? It filtered the PivotTable, just like if we used the category filter! In fact, it even updated the category filter for you.
One thing to be aware of here is that we don’t have to have the Category filter on the PivotTable for the slicer to work. This filter was only there because we added it to the PivotTable at the very beginning.
Let’s drill a little further in. Click “Lager” in the Type slicer:
Nice, it’s filtered down even more!
But hmm… what if I want to clear those filters?
Clearing Slicer Filters
If you look carefully at the image above, you’ll see that in the upper right corner of the slicer there is a picture of a funnel with a red x on it. Let’s click the one on the Category filter and take a look at the results:
If this is the first time you’ve ever seen slicers in action, it’s kind of like one of those “spot the differences” tests that your kids play. What changed?
- The Category filter on the PivotTable has been reset to show All values, not just Beer
- The Category slicer is showing Beer highlighted (no change there), but Liquor is now slightly shaded. This indicates that it contains no data. Why? Because the Type is filtered to only Lager records, meaning that no liquor records would show since Lagers are beer.
- Ale, Scotch and Vodka are each shown with white backgrounds. This means that they could be used to filter the table and records exist. Why? Because the Category slicer is not restricting any data (and Liquor is only reacting to the Type slicer’s selection.)
Let’s put this to the test. Click Scotch in the Type slicer and take a look at what changes:
Obviously the PivotTable contents changed, but so did the Category slicer. Now Beer is greyed out, which makes sense; because the PivotTable is filtered to show only Scotches, none of the remaining items from the Beer category will be shown.
The great thing we can see here is that it is very easy to drill through our data. We can click any of the slicer items to immediately affect the PivotTable, and removing the filters set by the slicer is very easy as well.
Selecting Multiple Items
Drilling into single items is all very well, but what if we wanted to display multiple items on our Pivot… say we wanted to filter it to show both Ales and Scotches. How would we do that? We can’t set the Category slicer to Beer, as that would filter out the Scotches. And we can’t set the category slicer to Liquor either, as that would filter out the Ales which are contained in the Beer category. So plainly the Category filter must be cleared and we’ll need to do our work in the Type slicer.
But then what? Every time we click Ale it drills into Ales, and when we click Scotch it drills into Scotches, but at the exclusion of the other. How do we make one selection stick while we select the other?
We hold down the CTRL key as we select the items.
Let’s try it:
- Remove any filters on the category slicer by clicking the red x (if necessary)
- Click Ale
- Hold down the CTRL key and click Scotch
And voila!
Conclusion
Slicers Are Awesome
They are so much more visually appealing than the old filter methods that they almost beg users to click them and explore their data. While it’s true that the method to select multiple items could be a bit more intuitive, you now know that to control multiple items in a slicer you hold down the CTRL key.
Today, we’ve see 3 things:
- How to create them. Fortunately this is pretty easy.
- How to use them. Again, easy.
- How to take praise when you become the office hero. Sorry, you’re on your own with this one!