One of the great new features in Excel 2010 is the Slicer functionality. Slicers are a great visual way to filter your pivot table. Episode 1067 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netast, I'm Bill Jelen.
While 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 alright 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 to 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 unusual 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're going to give them to me this stacked version and I don't really like that I'm going to resize these make the regions Slicer a bit smaller because there are really only four regions that I have to deal with and I can change the color of that Slicer and then we'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 come here to Slicer Settings and in number of columns go to 3, click OK and we have a color for that.
Now here's how Slicers work; let's say that we're interested in product ABC, so I choose product ABC well instantly now I can tell it 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 alright well hey west region it was Chevron, HP, and Walmart that bought that product in the West.
Let me hold down Control and add Canada and so now you see that we have both Canada and West and there are more customers involved, and now maybe I want to see just specific customers so choose Sears Canada and Chevron, and Air Canada, holding down Control, alright 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 said 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 to Excel 2007, that feature was like half done they must have known that they were coming here, didn't have time to do it in 2007 and finally made it in 2010.
Slicers are a great improvement to Pivot Table, something to look forward to in Excel 2010.
I want to thank you for stopping by; we'll see you next time for another netcast from MrExcel.
While 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 alright 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 to 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 unusual 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're going to give them to me this stacked version and I don't really like that I'm going to resize these make the regions Slicer a bit smaller because there are really only four regions that I have to deal with and I can change the color of that Slicer and then we'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 come here to Slicer Settings and in number of columns go to 3, click OK and we have a color for that.
Now here's how Slicers work; let's say that we're interested in product ABC, so I choose product ABC well instantly now I can tell it 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 alright well hey west region it was Chevron, HP, and Walmart that bought that product in the West.
Let me hold down Control and add Canada and so now you see that we have both Canada and West and there are more customers involved, and now maybe I want to see just specific customers so choose Sears Canada and Chevron, and Air Canada, holding down Control, alright 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 said 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 to Excel 2007, that feature was like half done they must have known that they were coming here, didn't have time to do it in 2007 and finally made it in 2010.
Slicers are a great improvement to Pivot Table, something to look forward to in Excel 2010.
I want to thank you for stopping by; we'll see you next time for another netcast from MrExcel.
