Excel Tips Weekly

- [Voiceover] Starting in Excel 2010, there's a new feature called a Slicer, and in Excel 2010 it was only available for pivot tables, but starting in Excel 2013, it's available for any data that's been converted to a table. In this worksheet I've got a list in columns A, B and C, and the same data has been reworked a bit. It's also in columns E, F and G. Now we could work with either one of these lists. I'm going to work with the list on the right. Here's what I'd like to be able to do: I'd like to have a chart of this, and I want it to be somewhat interactive in the sense that as I'm meeting with some HR professionals we'll be talking about some staffing issues, and we want to have the ability to use Slicers as we make our presentation.

One of the big advantages of Slicers is that we have a clear reminder at all times not only what is being shown, but what is not being shown. But we can't use Slicers with this list just yet because it's not a table. You can convert a list into a table simply by doing one of four things: After clicking inside the list, you can press Control T. Think of T for table. Control L, think of L for list. Or on the Home tab, you'll see a choice off to the right, Format As Table.

If you were new to the feature, that certainly wouldn't give you much of a rationale for using it, so you'll also find it on the Insert tab, and here the description I think makes some sense. "Create a table to organize and analyze related data." If you've not worked with tables very much, you probably are thinking well, it's just good for good visuals, and it is good for that, but it also lets us treat our data as a unit, and when we add adjacent data on the bottom or the right side, it becomes part of the table, and it certainly does make it easier to do sorting and filtering and use other features.

And it also opens the door to using Slicers. You cannot use Slicers on data unless it's a pivot table or unless it's data that's been converted to a table. So convert it to a table. There we are, click OK. If we don't like the look of that, we've got 61 choices out there. On this Contextual Design tab we could certainly find something we like probably. I'll leave this right here. Now let's create a chart just for columns F and G. Data's highlighted, I'll press Alt F1.

There's a chart. I want to make it a little bit bigger, a little bit wider, and I think it'll be easier to read also if I double-click one of the columns and reduce the gap width. I'm going to try 10%. I'll experiment with that a little bit perhaps. Yes, I like that look a bit better, I'll close that dialog box. Now for the moment the chart probably isn't giving us a whole lot of information, or it's not telling us a whole lot more than the data, but we can do filtering. We don't necessarily have to use Slicers, but there will be an advantage.

We could do filtering to the extent of saying, let's only, and because this is a table it automatically has filtering, let's focus on column E even though it's not in the chart, and click the drop arrow and say let's just see the full-time people. Now the labeling in the chart doesn't show us that, and furthermore the chart has shrunk, so that's kind of annoying, isn't it? To have to resize this every time you use, or potentially every time you use some of the filtering concepts. So let's move this chart. Now when you right-click and choose Move Chart, you do have the option of putting this on a new sheet, but that's a Chart Sheet, and you won't be able to resize it there the way you might want to.

So I'm going to cancel that, and actually what I'm going to do here is right-click and move the chart, and not put it on the new sheet but put it on the empty sheet that I already have. If you look at the bottom of the screen I've got a Sheet One down there, so I'm going to put this chart as an object in Sheet One. Click OK, it's over there. Now the next thing I want to do is to have a split screen. So I'll go to the View tab and create a new window, and then back to the View tab, Arrange All, I want a vertical arrangement here.

Windows of the Active Workbook, check this box. Be sure to check that, click OK. And for the moment that's not too helpful, but I want to keep that chart on the right side, and on the left side at the bottom of the screen, I want to choose Source Data. And scrolling around a little bit so I've got that data on the left side, I don't necessarily have to have that screen be 50, 50. Maybe I'll first click on the left side of the screen, make that a bit narrower, possibly. Although I might want to zoom in a little bit on it. Point is we can split this in different ways. But the left side of the screen's going to be containing the data, that other sheet that I copied the chart onto.

I can then, on that side of the screen, make the chart a bit bigger, resize it, consider how it might look. And also, if I wanted to, I can hide the ribbon there. We can do that with Control F1. There we are, so, we've got a good deal of screen space on the right. Now remember, the chart is tied to the data over on the left side of the screen. So we can use our filtering now, but better yet, let's use Slicers. And we might want to make the screen a little bit wider, but we can experiment with this.

So, to introduce Slicers into a table, momentarily here I'll make that screen a bit bigger so I can get to my commands a bit better, on the Contextual Design tab, it's active, in the active cells within a table, we can insert a Slicer. How about one for Status, one for Department. Click OK. And then we'll just have to experiment a little bit about the best layout choices here. These don't have to be this big of course, we'll make it about that big. And we want to resize these in such a way that we're not seeing scroll bars.

There we are, and that one's about right there, even though Project Contract Services is cut off a little bit. We don't necessarily have to change the look of these, but we can certainly do that too. Different look there, different look here, and so on. Now, resizing again. Coming back here, jumping over to the chart side, make that window a little bit narrower. Again, something you'll just have to experiment with a little bit to get this right. That chart a little bit smaller, move it around in such a way that we can see it. For now that's good enough.

So we've got our data here, what are we seeing on the screen right now? As we look at the Slicers, we're seeing the full-time headcount for all departments. Someone asks the question, how many full-time people do we have in Logistics? I'll click the Logistics box. And there it is, it's 20. We see it in two different places. What's the total headcount for Logistics only? So, under Status, this is in the data on the left side of the screen here, we'll click the drop arrow for Status. Remember, Status isn't in the chart, but it does affect what's being displayed on the screen there.

So let's say full-time and half-time, what's the total full-time and half-time? Or possibly all different status types here. Remember, we're looking at Logistics only. But did we really have to use the filter there? No, we could have used the Slicer. And let's go back and say OK, how about no filtering at all on Status. In other words, show all four categories. And there we're seeing the data that way, that's the breakout for the Logistics people only. We see the numbers. The filter arrows are still there and we can use them, but remember, the big advantage of Slicers is we can make our choices, and we can also be reminded of what we're not seeing.

And keep in mind too, if we wanted to add another department to this list, maybe we're talking about Logistics and Operations both. Already Logistics has been chosen, but if I want to choose Operation, I'll hold down the Control key and click Operations. Now a new feature in 2016 says rather than having to use Control every single time, if you activate the button here just to the right, this is the Multi-Select, you can click there, and I'll say OK, I want to add Quality Assurance here so I'll click that, I don't have to hold down the Control key, I've added that to the list.

So what are we seeing right now? We're seeing the complete headcount for all statuses for these three departments together. We're not seeing a grand total, however. And we can somehow arrange our data in a way to see that as well too, or have a temporary function out on the worksheet that would give us the total at any given time. Keep in mind if you do click column G, down in the Status bar you are going to be seeing that total. Right now it's 162, and that's a total of just the visible cells. So using the Slicer concept, and possibly using it with charts, we don't always have to use charts of course, but we've got some real ability here to make a presentation based on the data.

And on that worksheet on the left side of the screen that we're seeing on Source Data, we probably would want to experiment with the data we're seeing out here as well too, at a different time. There the data is organized differently, it's Department, Status, Headcount, instead of Status, Department, Headcount. Left to right, a difference in the order there. But we've got some real flexibility, and once again, this Slicer concept really gives us a strong visual for what we are seeing and what we are not seeing, and used in combination with charts, it makes for a very effective presentation tool.