Join Gini von Courter for an in-depth discussion in this video Add a slicer for a table, part of Data-Driven Presentations with Excel and PowerPoint 2016.
- [Instructor] This is a different spreadsheet. It's called slicer and this provides information on customer identifiers removed so that it's anonymous so we can use it for an analysis. The five columns are order date, customer, region, sales region, and units. And if I wanted to filter this by sales region, it would be easy enough. I could simply click the dropdown and turn off select all and choose for example Atlantic, Central US, and Europe. When I do that, I can tell that this table is filtered in at least three different ways.
First, in the status bar it says 26 of 35 records found. Second, there's a filter icon on the filter arrow for the sales region column. A third thing and more subtle is that the row indicators have been turned blue and not all of the numbers are there. However, if I were to glance at this and immediately try to assess what sales region is missing, it might take me a minute. And if I had many more sales regions, if this was a list of countries or states and provinces, this becomes even more difficult.
So let me show you another way to filter which is called a slicer. To bring everybody back, select all. To add a slicer, we're going to click somewhere in our table and we're going to choose insert slicer. If you forget and you're not in the table and you choose insert slicer, then you get this request for a data connection. But if I click somewhere in my table and choose insert slicer, notice here are my columns. Slicers are typically applied to labels, to text-based columns, not to for example the number of units that were sold.
I'm going to choose a slicer and the slicer I'll choose is sales region. It's the slicer that our users are requesting and click OK and here's my slicer that quick and easy. I'm going to simply move it, size it up, modify its size, and now let's use it. I'd like to simply see European sales. Sales in the Atlantic US region. I'd like to multiselect. I can either hold Control or I can click the multiselect button which will allow me to select multiple items.
Turn it back off and the next time I click, I'm choosing one item. Click the clear filter button or hold Alt and hit C to clear the filter. I can have multiple slicers if I wish. Click back into our table, choose insert slicer and perhaps I would like a slicer for region. Now, these slicers are not connected at all. So if I choose Atlantic US, it is not simply going to show me the Atlantic US directly. It is, however, going to move the items that remain to the top of the list so it feels like I have that type of cascading filter here.
If I choose Europe, notice, Alaska, everybody else is still here, but the filter is always showing us the items that are actually present in the list at the top of the filter. Central US, Michigan. Atlantic US, North Carolina, multiselect, and Tennessee and Florida. I can size this slicer so that it's larger and I might want to do that because there are more entries.
Ideally, no matter which sales region I pick, I would like to be able to see all of the regions within that sales region without needing to scroll. So it appears that the original size, which was about here, works. We're used to working left to right. I'll put sales region on the left, region on the right. Clear this filter and notice that the filters are independent. If I simply want California, I can filter based only on California.
Turn off the multiselect. There's California and notice that Pacific US lights up. One more nice feature. If a scroll bar is needed, you'll get one. If you haven't used slicers before, I think you'll find them useful and engaging.
- Copying, pasting, linking, and embedding data
- Creating charts in PowerPoint
- Formatting ranges
- Restoring missing data
- Adding slicers
- Building charters
- Highlighting data
- Summarizing data with PivotTables
- Putting presentation elements together in PowerPoint