Did you know that Excel Pivot Tables can do so much more than just show your data and sum or count it?
Are you saying, "Debbie, what are slicers?"
Let me explain...
Slicers were added in Excel 2010 to filter PivotTable data. In Excel 2013 & newer versions, you can now use slicers to filter any table data. Slicers clearly indicate what data is shown in the table after you filter the data. They include buttons so that you can quickly filter data without having to use drop down lists to find the items you want to filter. Timelines are a type of slicer that is specific to date ranges.
A slicer typically displays the following elements:
The Slicer Header – indicates the category of the items in the slicer.
Unselected filtering button – indicates that the item is not included in the filter.
Selected filtering button – indicates that the item is included in the filter.
Clear Filter button – removes the filter by selecting all items in the slicer.
A Scroll Bar – enables scrolling when there are more items than are currently visible in the slicer.
Border moving and resizing controls – allow you to change the size and location of the slicer.
Now that we've described what they are and their elements, let's talk about how to use slicers.
Using slicers
To filter your data, just select one or more of the buttons in the slicer.
You will likely that you will create more than once slicer to filter a data table or PivotTable report.
You can create a slicer that is associated with the current data table or PivotTable. You can also create a copy of a slicer.
Once you create a slicer, it appears on the worksheet alongside the table data, in a layered display if you have more than one. You can move or resize it as needed. Once created, a slicer can also be used with another table or PivotTable.
You can create slicers that work with the current data table or PivotTable or you can create a stand-alone slicer that can be associated with any other table at a later time.
If you already have an existing table, you can add slicers to improve your current table and its usage.
The Insert Slicer command is available from the Insert tab on the Ribbon. The Insert Slicers dialog box helps you quickly create your slicers, based on the data in the table you selected.
To Insert Slicers, follow these steps:
Place your cursor anywhere in the table.
Select the Insert tab from the Ribbon.
Select Slicer.
4. In the Insert Slicers dialog box, select the check box(es) of the fields from the table for which you want to create a slicer.
5. Select OK.
6. To apply your filters, just select the buttons for the items you want to include. You can hold down the CTRL key while selecting to choose more than one button.
Now that your slicer box appears, users can simply click on an item to filter the Pivot Table by that item. If a user continues filtering by data and needs to reset, simply click on the Filter button in the upper right corner with the Red X to clear (or remove) filters (#4 in the image above).
If you do not like the location of the slicer box, simply click and drag it around the page to placed it in the best area for users. This is particularly helpful if you setup more than one slicer. You can organize them how it best makes sense for the person using the table to look at data.
It really is that simple.
If you're interested in an entire course on Excel Pivot Tables, Analyzing Data, Macros, and more, CLICK HERE for a 50% discount.
