Check out this podcast entered into competition at: www.alphageekchallenge.com ! Click the "Vote Now" tab to view the contest entry.
Transcript of the video:
MrExcel podcast is brought to you by ”Easy-XL”!
Hey, welcome back, time for another MrExcel netcast, I'm Bill Jelen.
Hey, I have a cool trick today, I created this Pivot table that doesn't show up as a Pivot table, it actually shows up as a tag cloud.
So this is showing you how, it works, as we choose different decades from the slicers, up here we're getting different artists, this is from the Billboard Hot 100 chart.
I'm going to ask for your help here, if you're watching this on Monday or Tuesday, now there's just a few more hours to vote at Alpha Geek Challenge.
Currently I'm losing to Dan English, and this is frankly just a popularity contest, and I hate the fact that I'm losing.
So, like, you go out to Alpha Geek Challenge, and just click Vote now!
You can vote once per day, so if you happen to be seeing this on Monday, vote early and often.
Alright now, let's talk about how I created that, for the contest I use PowerPivot, but it's really just something to do with a regular Pivot table.
Insert PivotTable, and I'm going to put artists down the left-hand side, and then weeks in the heart of the Pivot table.
Now, the first thing I want to do is get just the Top 10, so here under Row Labels, I will choose More Sort Options, and say “I want to see this descending based on the sum of weeks.” Click OK, and then we're going to use the Value filters to get just the Top 10 artists.
Alright, now, in Excel 2007, what you're going to have to do is use the Report filter, I'll show you how that works.
Take the Report filter over, and we can choose one specific decade, 1960, and see the Top 10 artists, or we can come in and say “Select Multiple Items” and choose 2 decades and see the artists.
Alright, so that's what you're going to do in Excel 2007, now here I'm in the beta of Excel 2010, they've actually improved this a lot.
We're going to add something called a slicer, and add the decade as a slicer, and the advantage of the decade is, now it's very easy to go through and choose multiple items.
So 60s through 80s Elvis was #1, 80s and 90s Madonna's #1, alright, but this is still a really boring table, so watch how I make this kind of come to life.
Here I'm going to increase the width, and I'll build a little formula here, =A4, and I'm going to make that centered and much, much larger, we’ll go up to 36 points.
And choose a text color for it, maybe even choose an interesting font, I don't know.
And now I'll copy this down to get all 10 values, and to add some variety I want to change the color on each one, so we choose a different color, and go a little bit smaller.
for the 3rd item, we'll go smaller by 2, and again change the color there, you get the general idea, I'm not going to do all 10.
And this is the really important part, we want to make sure that there is no fill for these, so we'll open the Paint Bucket and choose No Fill, that makes sure that they're transparent.
Now over here, where the tag cloud is going to be, we do want to add some fill, so we'll add some interesting color there, I'll just go with something light, like one of the light blues.
Alright, so I choose my very first item, right, Ctrl+C to copy, but I'm not going to do a paste, I come over here, open up the paste drop-down, and in Excel 2007 you'll do Paste Picture as Link, it's near the bottom.
Here, I'll do a linked picture, alright, and what we get is a picture of that cell.
Now I'm going to do the exact same thing, I'm going to have to do this 10 times, copy, and then come over here and do again, Paste Linked Picture.
The beautiful thing about this is, because they're transparent, I can actually arrange them in an interesting way, kind of a random way, so that way they're near each other, but not exactly on top of each other.
We'll go back to take a look at our Internet Explorer page to see how this actually looks when it's all working.
So, as you choose the various decades, the items down here update automatically.
Doesn't look like a Pivot table at all, a very cool visualization, where you take that Pivot table data and create, basically, what looks like a tag cloud that updates in response to the filters.
Thought this was really cool, again, need your vote, AlphaGeekChallenge.com, you have until Tuesday at midnight, East Coast, US time!
Want to thank you for stopping by, I was in Seattle last week, went to see Mike Girvin, we recorded 4 new Dueling Excel podcasts, so watch for those to come back on Friday.
Will try to have some more podcasts for you this week!
Hey, welcome back, time for another MrExcel netcast, I'm Bill Jelen.
Hey, I have a cool trick today, I created this Pivot table that doesn't show up as a Pivot table, it actually shows up as a tag cloud.
So this is showing you how, it works, as we choose different decades from the slicers, up here we're getting different artists, this is from the Billboard Hot 100 chart.
I'm going to ask for your help here, if you're watching this on Monday or Tuesday, now there's just a few more hours to vote at Alpha Geek Challenge.
Currently I'm losing to Dan English, and this is frankly just a popularity contest, and I hate the fact that I'm losing.
So, like, you go out to Alpha Geek Challenge, and just click Vote now!
You can vote once per day, so if you happen to be seeing this on Monday, vote early and often.
Alright now, let's talk about how I created that, for the contest I use PowerPivot, but it's really just something to do with a regular Pivot table.
Insert PivotTable, and I'm going to put artists down the left-hand side, and then weeks in the heart of the Pivot table.
Now, the first thing I want to do is get just the Top 10, so here under Row Labels, I will choose More Sort Options, and say “I want to see this descending based on the sum of weeks.” Click OK, and then we're going to use the Value filters to get just the Top 10 artists.
Alright, now, in Excel 2007, what you're going to have to do is use the Report filter, I'll show you how that works.
Take the Report filter over, and we can choose one specific decade, 1960, and see the Top 10 artists, or we can come in and say “Select Multiple Items” and choose 2 decades and see the artists.
Alright, so that's what you're going to do in Excel 2007, now here I'm in the beta of Excel 2010, they've actually improved this a lot.
We're going to add something called a slicer, and add the decade as a slicer, and the advantage of the decade is, now it's very easy to go through and choose multiple items.
So 60s through 80s Elvis was #1, 80s and 90s Madonna's #1, alright, but this is still a really boring table, so watch how I make this kind of come to life.
Here I'm going to increase the width, and I'll build a little formula here, =A4, and I'm going to make that centered and much, much larger, we’ll go up to 36 points.
And choose a text color for it, maybe even choose an interesting font, I don't know.
And now I'll copy this down to get all 10 values, and to add some variety I want to change the color on each one, so we choose a different color, and go a little bit smaller.
for the 3rd item, we'll go smaller by 2, and again change the color there, you get the general idea, I'm not going to do all 10.
And this is the really important part, we want to make sure that there is no fill for these, so we'll open the Paint Bucket and choose No Fill, that makes sure that they're transparent.
Now over here, where the tag cloud is going to be, we do want to add some fill, so we'll add some interesting color there, I'll just go with something light, like one of the light blues.
Alright, so I choose my very first item, right, Ctrl+C to copy, but I'm not going to do a paste, I come over here, open up the paste drop-down, and in Excel 2007 you'll do Paste Picture as Link, it's near the bottom.
Here, I'll do a linked picture, alright, and what we get is a picture of that cell.
Now I'm going to do the exact same thing, I'm going to have to do this 10 times, copy, and then come over here and do again, Paste Linked Picture.
The beautiful thing about this is, because they're transparent, I can actually arrange them in an interesting way, kind of a random way, so that way they're near each other, but not exactly on top of each other.
We'll go back to take a look at our Internet Explorer page to see how this actually looks when it's all working.
So, as you choose the various decades, the items down here update automatically.
Doesn't look like a Pivot table at all, a very cool visualization, where you take that Pivot table data and create, basically, what looks like a tag cloud that updates in response to the filters.
Thought this was really cool, again, need your vote, AlphaGeekChallenge.com, you have until Tuesday at midnight, East Coast, US time!
Want to thank you for stopping by, I was in Seattle last week, went to see Mike Girvin, we recorded 4 new Dueling Excel podcasts, so watch for those to come back on Friday.
Will try to have some more podcasts for you this week!
