Hey, welcome back to the MrExcel netcast, I'm Bill Jellen.
Hey welcome back to the MrExcel netcast, I'm Bill Jellen, well we’re talking about Excel 2010 this week. I want to talk about my next feature, this is something called a slicer. Slicers make pivot tables much more powerful. I’ll create a pivot table from this data set, insert pivot table, click OK and let’s just show revenue and profit by date. So there’s our pivot table.
Now in the past, if we wanted to filter this pivot table, we would take fields and add them to the report filter section and in Excel 2007, they gave us the ability, let’s say that we want to select multiple items and we could choose multiple customers but when we do that, well it only shows us the tag of multiple items, very, very un useful frankly, so let’s go back to all—I'm going to remove those items from the filter, actually let’s leave them there. We’re going to try the new slicer option so I come here to pivot table tools options and say, insert slicer. All right, I choose the fields that I want to build the filter on, region, product and customer, click OK. Now, they are going to give them to me in this stacked version and I don’t really like that, I'm going to resize these, make the region slicer a bit small because there are really only four regions that I have to deal with and I can change the color of that slicer and then I’ll take the product slicer over here and again I’ll resize that, doesn’t need to be that large. Choose a color there and then finally the customer slicer, I'm going to try and resize that and make room for more columns, that way I can see all the customers at once.
So I make it wider, slicer settings and in number of columns go to three, click okay and we have a color for that. Now, here’s how slicers work. Let’s say they were interested in product ABC, so I choose product A, B, C. Well instantly now, I can tell that we sold product ABC in all four regions. But, we did not sell it to Compton Petroleum or Sun Life Financial. So you can tell graphically what’s in and what's out. Let’s come here and say that we’re looking for the West region. All right well hey, West region with Chevron, HP and Wal-Mart that bought that product in the West. Let me hold down control and add Canada. And so nice that we have both Canada and West and there are more customers involved. And now maybe I want to see just specific customers so I choose Sears Canada and Chevron and Air Canada holding down control, all right. So now I get a report of the sales to these regions of this product of these specific customers and rather than the old tags here, we’re just having multiple items, what does that mean? We can tell exactly what items are included in the pivot table, a great improvement.
I mean my theory frankly is when they added the multiple items functionality of Excel 2007, that feature is like half done. They must have known that they were coming here, didn’t have time to do it in 2007 and they finally made it in 2010. Slicer is a great improvement to pivot tables, something to look forward to in Excel 2010.
Now I want to thank you for stopping by, I’ll see you next time for another netcast from MrExcel.
Transcription by:
Scribe4you Transcription Services