Last week, in Episode #1411, Bill Challenged the audience to figure out how he was using the Pivot Table Slicer to update images in a Workbook. Today, with Episode #1416, we learn how to employ this technique. Learn Excel 2010 from MrExcel
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1416.
Jeff's Slicer Trick.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, last Monday, episode 1411.
I kind of put a challenge out there to try and figure out how I was using the slicer, the pivot table slicer to update the words and then last week it look like Smart Art, this week you'll see that it's just a shape.
In fact but and five people answered the question.
Now, figured out how to do it.
Although, it's funny last week, I was showing a picture and we had many different answers on the picture.
So, we'll visit the picture issue Wednesday but first of all, let's just talk about how to do this slicer tricks.
And congratulations to all five, who are going to get a copy of Learn Excel 2007 from 2010 from MrExcel.
The brand new book that just came out.
So, we'll scroll up in and take a look at it.
Here's the original data set one through five and here is the pivot table and you'll see that as I change the slicer, the pivot table is showing just a single value there.
That's it from that point it's a simple vlookup or an index function.
In this case to pull up the correct value, once we have a cell with that value then you have a shape.
Click on the shape and equal C8.
So, let's kind of just build this from scratch.
I have five choices at this time, so I'm gonna hold down the ctrl key and drag down.
You know, actually you could even do this.
We put the result and names of the winners cut and paste and then we're going to build a simple little pivot table.
So, insert pivot table, okay!
Now, what we're going to do is we've that slicer field is going to go up and the slices of the result will go here and row labels and normally we would have something in the values area but not this in this case.
We'll drag that out of the way, insert slicer, slicer.
Click [ OK ] now, this always shows up in a single column.
I like to change it up to five columns and resize.
All right! So, now as soon as we choose from the slicer, the pivot table will update with just the correct answer.
So, I could use that cell A4, the driver for shape or anything like that, let's just do it quickly here.
Insert shapes, this great facing arrow and then to get the text for the shape, just click here and say equal A4.
Here we go, a little bit of formatting and we're home free, all right!
So, that's cool you can use this for all kinds of types of things where we just have the slicer.
Basically, as a control instead of an activeX control, to choose from a simple list.
Again, that came from Jeff of a pivot stream, tomorrow I'm going to show you an amazing trick that pivots stream, lets us do with a power pivot when you don't have share point using power pivot on the web.
So, check back for that tomorrow.
Hey! Wanna thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1416.
Jeff's Slicer Trick.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, last Monday, episode 1411.
I kind of put a challenge out there to try and figure out how I was using the slicer, the pivot table slicer to update the words and then last week it look like Smart Art, this week you'll see that it's just a shape.
In fact but and five people answered the question.
Now, figured out how to do it.
Although, it's funny last week, I was showing a picture and we had many different answers on the picture.
So, we'll visit the picture issue Wednesday but first of all, let's just talk about how to do this slicer tricks.
And congratulations to all five, who are going to get a copy of Learn Excel 2007 from 2010 from MrExcel.
The brand new book that just came out.
So, we'll scroll up in and take a look at it.
Here's the original data set one through five and here is the pivot table and you'll see that as I change the slicer, the pivot table is showing just a single value there.
That's it from that point it's a simple vlookup or an index function.
In this case to pull up the correct value, once we have a cell with that value then you have a shape.
Click on the shape and equal C8.
So, let's kind of just build this from scratch.
I have five choices at this time, so I'm gonna hold down the ctrl key and drag down.
You know, actually you could even do this.
We put the result and names of the winners cut and paste and then we're going to build a simple little pivot table.
So, insert pivot table, okay!
Now, what we're going to do is we've that slicer field is going to go up and the slices of the result will go here and row labels and normally we would have something in the values area but not this in this case.
We'll drag that out of the way, insert slicer, slicer.
Click [ OK ] now, this always shows up in a single column.
I like to change it up to five columns and resize.
All right! So, now as soon as we choose from the slicer, the pivot table will update with just the correct answer.
So, I could use that cell A4, the driver for shape or anything like that, let's just do it quickly here.
Insert shapes, this great facing arrow and then to get the text for the shape, just click here and say equal A4.
Here we go, a little bit of formatting and we're home free, all right!
So, that's cool you can use this for all kinds of types of things where we just have the slicer.
Basically, as a control instead of an activeX control, to choose from a simple list.
Again, that came from Jeff of a pivot stream, tomorrow I'm going to show you an amazing trick that pivots stream, lets us do with a power pivot when you don't have share point using power pivot on the web.
So, check back for that tomorrow.
Hey! Wanna thank you for stopping by, we'll see you next time for another netcast from MrExcel.
