Microsoft Excel Training – Using Slicers

When it comes to Microsoft Excel training, no education is complete without fully understanding how to use Slicers. In this Directions Training blog post, the Office Guy offers an insightful overview of the Excel filtering tool.

More notes from the “Office Guy”…

As the products in the Microsoft Office Suite have evolved over the years I’ve noticed something that I call the trickle-down effect. That is, a feature may be introduced in one version of a product (say the 2010 version) that can only be used in special circumstances is then expanded for more general use in the next version (the 2013 version).

A good example of this is a tool known as a Slicer. This is a feature of Excel that was introduced in the 2010 version, but it was only available to use if the user was working in a Pivot Table – it would appear only on the contextual Pivot Table Tools tab. So, if you never worked in a Pivot Table, you wouldn’t even know that this tool existed. But in Excel 2013, Slicers are available in simple Excel Tables as well as Pivot Tables.

Microsoft Excel Training – What is a Slicer?

So, let’s back up a little – what is a Slicer in the first place? Essentially, a Slicer is a tool used to filter data. Unlike traditional filters that were only available from a drop-down menu, Slicers are graphic filtering mechanisms that make it easier, faster and more intuitive to find and focus on specific data.

Let’s take a look at what this means by comparing traditional filters to slicers. Pictured below is a simple list of sales data stored in an Excel spreadsheet:

 

Let’s say that this list goes on for many more rows, and eventually data for other Regions is also part of this list. Or, if you needed to only see the data for one Region but that Region was several hundred rows down the list. Filters that allow you to narrow down the visible data by selecting only certain categories have always been available but these filters required using drop-down menus that could be added to the heading row at the top of the list.

Microsoft Excel Training – Using Slicers to Filter Data

So, how would you be able to see and utilize Slicers to filter your data in an easier and more intuitive way? First, you would need to turn the list into a simple excel Table. This is done by going the Insert Tab, and in the Tables group clicking the Table button as shown below:

This opens the dialog box pictured below:

Click OK you now have turned your list into a simple Table where the filter drop down menus are now available in the headings at the top of each field in your list and also makes available a contextual Tab of Table Tools:

 

At this point you could begin to filter the list by conventional drop down menus by checking off the categories of data you want to see in each field:

If you filtered the list by several fields, you would then have a narrowed down view of your data. The example shown below has filters applied to three different fields. The fields that were used to filter the data would show a different icon (a funnel) that would indicated that the field was filtered. But, you couldn’t see what criteria was applied to each field, and if you wanted to remove a filter from a field and change the criteria, you had to go back to the drop-down menu for each field:

Microsoft Excel Training – Slicers Simplify Data Filtration

So, how does a Slicer make this an easier and more intuitive process? By inserting slicers, the criteria available to filter each field are always visible which makes it easier to understand what filters have been applied in each field. Slicers also make it easier and more intuitive to change or clear filter criteria from a field.

The following screen shots illustrate the process of inserting and working with Slicers. To begin, you’ll need to turn your list into an Excel Table as described earlier in this article. Then on the Table Tools contextual Tab, in the Tools group, click the Insert Slicer button: 

This will open up a dialog box that allow yon to choose the fields you wish to filter with a Slicer:

After you choose the fields and click OK, the Slicers will display on your work sheet. At first, the Slicers will be stacked on top of each other:

But, the slicers are treated like any inserted graphic object, meaning that you can click and drag them to whatever position you choose.

You are now ready to “Slice” your data. In each field simply click on a button for a particular category of data you wish to view in each field. If you wish to select more than one category in each field, simply hold down the Ctrl key on your keyboard and click on additional category button. In this example the user has selected two categories in each field:

You can then easily see which categories have been chosen in each field since the slicers remain visible, and the chosen category buttons remain in color.

When you need to remove filters from a particular field, there is an icon in the upper-right corner of each slicer that allows you to remove the filter from the field:

So, you now have an easier, more visible and more intuitive means to filter your data sets. And, you don’t have to be using a Pivot Table in order to take advantage of this tool since the feature introduced in the 2010 version has “trickled-down” for more general use in the 2013 version of Excel.