Dynamically presenting data via chart slicers

- The slicer feature was introduced in Excel 2010, but only available with pivot tables. Starting in Excel 2013, if you convert data to a table, then you can use a slicer. Now, if you've not used the feature, you're not even aware what it is, it's a visual way to do filtering. Now let's imagine we wanna make a presentation of this data, we might wanna show different states at different times. And we might also want to use a chart. Let's convert this data to a table. There are four ways to do this, you can do it from the insert tab in the ribbon, there's a choice called Table.

From the Home tab in the ribbon, you can also choose Format as Table. You can press Control L, think of list, or Control T. We'll simply press Control T. Now within the data make sure you have no empty rows, empty columns and you don't have to highlight it. Make sure, also, your titles are in a single row. So I'll simply press Control T. Excel analyzes the data, sees the scope of it. I'll click OK and we've got some visuals applied. If we don't like those, there's some table styles up to the right we can make changes with. A quick way to create a chart in Excel is Alt F1.

There it is. I'll make it a little bit smaller, first of all for the title let's say, right now I'll just click it and press delete. And then resize this a bit so we can see the chart and the data. Move over to the side a little bit. Maybe even shrink it some more. Now, when we use filtering in charts, we, for example, we see arrows here. But, if I choose the filter for State and unselect them all and just choose California and Florida we see those and that's all well and good, what are the other states we're not seeing? Well there was Texas and Illinois and Pennsylvania.

I, the presenter, might remember all those names, the others might not. If there's a down side to filtering, it's this idea that you're not always aware of what you are not seeing and sometimes that's important. So, I'm going to press Control Z here and introduce a slicer here by clicking within the data, going to the Design tab and Insert Slicer. Now you don't really have to go to the Design tab. On the Insert tab you'll also see Slicer out here, so either way. Slicer. Now you might be inclined to check all these, but I'll just click one of these options for the items, say Chairs.

The important one is State. And we'll click OK. Now you can see why the Chairs is not gonna be very viable. We can see just the chairs related to this number. That doesn't really make a lot of sense. Let's get rid of this one, we'll simply right-click here and Remove Chairs as a slicer. When slicers appear sometimes you wanna resize them like this one. You can add colors and do other things too. If there's a downside to slicers, it's the idea that sometimes when you've got more than a few, it fills up a lot of screen space. Now, the beauty of this is, let's say we do wanna see California and Florida like we did before, so I'll click California and with the Control key, I'll click Florida.

Notice for the moment we're only seeing California in the chart. Now, with the Control key, I'll click Florida. And not only are we seeing the date for those two states, but we are reminded of the data that we're not seeing. Let's also put in Texas here with the Control key. There we see and so what's happening is in effect we're doing filtering, but we're doing it in a very visual way. And you can imagine someone who's not too proficient with Excel, just getting a short lesson on how to use filtering. In other words, a manager of a certain level who doesn't know Excel that well might see data like this and be really curious about how he or she could make a good presentation.

That person would only need to learn a little bit about slicers, here. And you can see clearly here what we are viewing in the chart and what's not being shown. Now, if we didn't wanna show tables, all we need to do for any of these items here is just hide the columns. Maybe we're most interested only in the actual furniture items, chairs, beds, and tables, and couches. So we'll hide rugs and lamps. Or actually, just drag across columns F and G, right-click and Hide. So we do have control over the data that way. Now it's up to you to remember, of course, that they are hidden and hidden data doesn't normally appear in a chart.

Although, you can have it appear in a chart. So at a later time, drag across here and right-click and Unhide. And now that data will appear in the chart. Now there's another downside to this kind of dynamic charting that you wanna keep an eye on. I'm gonna show California only for the moment. Notice how the chart tops out at two million. I'm going to show Illinois. Now there it tops out at 1.5 million because there's less data there. The volume of the data is less. Not truly wrong, but if we're trying to show California in contrast, in other words, if we're bouncing back and forth, you see how the scaling changes.

So possibly, what you might want to do here, is initially unfilter, in other words, show all states. Now there we see it goes to two million. So we might wanna just leave that as the default, rather than having automatic scaling. So if you right-click one of these numbers here, that's on the axis there, you can format the axis and then off to the right here where you see maximum, it says auto. Change that to be 2 million. I'd stress that this is not absolutely critical and not important for everybody but if we do that, then it ensures, and I'll close that frame there, For example, we're looking at Illinois again, it too tops out at two million, but it didn't before.

And Pennsylvania too and so on. So, some advantages here. Now, if we transpose the data, let's first show what it looks like here, if we take this data, copy-paste it and then transpose it, it will look like this. Now here too, we can convert this into a table, I'll press Control T as I did before, turn this into a table and create a chart, Alt F1 like before, click Chart Title. Something else about charts you should know too, you may or may not know this, but if you Switch Row Column and I do this almost routinely every time I create a chart, it simply displays the data differently.

Not always better, sometimes better, sometimes not. It gives you a different read on the data as we do this. So I'm gonna use this approach here, shrink this chart a little bit, so we can see it. Move it down here a little bit, there we are. Now we're in the same general approach but this time if we introduce slicers, I'll do it from the Insert tab, Slicer, It's the States that aren't gonna give us very many good slices but items certainly will. This way. So when you're confronted with this kinda data, I think you wanna decide, you probably don't wanna transpose and do both, but do one or the other, in other words, the data as we see it on this sheet versus the data as we saw it on the other sheet.

But with these slicers now we can say let's just focus on certain items here. How 'bout just Lamps and Tables? Using the Control key we can select these. If we selected items and then want to deselect one, we also use the Control key. Let's focus on Lamps only. So using Control I click Tables and there we are. And the legend within the chart adjusts each time. So I think you can see how working with slicers and with charts is going to be effective. Now remember in each case, you've got to make sure that the source data is a table, otherwise you cannot use the slicer capability.

And remember too, to not filter means show all the data. And of course at different times, you will want to do that. So I think slicer is a valuable addition to certain people's arsenal of presentation skills.