In Episode 898, I show off some of the deep features in Excel 2007 pivot tables and offer a sincere thank you to everyone at MrExcel.com.
Transcript of the video:
Hey, okay, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question is sent in by me.
Lately, I've been lucky in that the podcast numbering scheme has worked out perfectly with the day of the week.
If the podcast ends in 0 or 5, it is on a Friday and it makes life easy for me.
And I'm trying to figure out-- tomorrow is Thanksgiving in the United States-- do we keep up that podcast for tomorrow just to keep the numbering scheme, or is that just a silly reason?
So I ran a query of all the air dates and the episode numbers, and then calculated, basically, a digit 1 through 5-- what it ends in, what it's divisible by, and then the weekday-- and I'm going to use a Pivot Table to analyze this data.
We're in Excel 2007, so we go to the Insert tab; Insert PivotTable; and I'm going to move the episode across the top, and the weekday down the side.
Now, I need a way to count and I don't have any text fields here, so I'm going to take one of the numeric fields, like episode, knowing that it's going to come up with a number that's way too large.
But I can go to Field Settings and change that back to a Count.
I also want a way to analyze this by year to see if it's changed, so initially, I'm going to take the air date.
I have to move it to Row Labels, so that way I can access the group feature.
So I'll right-click, choose Group, and change that to "Years"-- that way, I just have the years going across.
And now that it's grouped, I can take the Airdate and move it up to the Report Filter so I can do some different analysis.
Now, a few things: This new Excel 2007 Pivot Table, I don't mind the Field List, but the Report Layout drives me crazy; I just want to go back to the good old Format.
And I want to make sure that it doesn't keep changing my column widths, so I right-click; go to PivotTable Options; and uncheck these two items.
So now I can make the column lists a bit smaller, and now I'm going to use one of the new Excel 2007 formatting schemes here.
We're going to go a Home, and Conditional Formatting, and apply a color scale where the largest numbers are in green; smallest numbers are in red.
Now, notice that I was careful not to include the Grand Totals because that would have thrown everything off.
And what I see is that, basically, for 51 to 52 weeks, I've managed to have this perfect numbering scheme where the episode that ends in 1 is on a Monday, and the episode that ends in 5 is on a Friday.
But there's been plenty of other times-- 48 weeks-- where it was off by 1, and-- 30-some weeks-- where it was off by 2, and then a few weeks here and there was off by 7.
And so it really isn't something that I've held to over the years, and so I might be free to take Thursday and Friday off.
Let's just do a quick look here in 2008.
In 2008, it has worked out almost perfectly for the entire year, that we've been in sync there.
And if we go back to one of the earlier years, you'll see that it was fairly scattered.
You know, it just depends on how the holidays fall.
So based on this, I think we could take Thursday and then, typically, Friday off from the podcast.
Now, you know I have to thank Leo Laporte.
Leo is the one who suggested this podcast when I was doing "Call for Help", and he even offered to help.
I mean, he basically offered to make it part of the Twit Network, and I didn't take him seriously.
I didn't think that Leo was really going to start to do a whole bunch of podcasts, and so I just started out on my own.
And so the other thing that I'm vaguely cognizant of is that i started the daily MrExcel podcast, and then **** and Leo started the daily Giz Wiz, you know, a few months after me.
And so I've always been ahead of them on episode counts, and basically I'm kind of proud of that and I don't want them to catch up.
So I just did a quick look here, they started on February 20th, 2006; they have 705 episodes.
I use the cool workdays function to figure out that in 720 Monday through Friday dates, they did 705 episodes.
I started 10/15/2005, and in 810 work days I did 795 episodes.
So we both have missed 15 days.
I'm still 90 episodes ahead of them.
Even if I take a couple of days off they will take years to catch up, so I'm using all of this decision to say, "Hey, we're not going to be here tomorrow." I want to thank everyone in the United States who's celebrating Thanksgiving.
They have a great thanksgiving holiday.
Friday, of course, most people have off, so we'll take Friday off and we'll be back on Monday.
And I want to send a sincere thank you to everyone, as we just celebrated our 10th Anniversary; I really appreciate everyone who's made MrExcel-- the site, the podcast, the message board, and everything-- a resounding success.
I know there's a lot of work on everyone's part in that community, to build a great, great site, and I want to thank you.
We'll see you on Monday for another netcast from MrExcel.
Today's question is sent in by me.
Lately, I've been lucky in that the podcast numbering scheme has worked out perfectly with the day of the week.
If the podcast ends in 0 or 5, it is on a Friday and it makes life easy for me.
And I'm trying to figure out-- tomorrow is Thanksgiving in the United States-- do we keep up that podcast for tomorrow just to keep the numbering scheme, or is that just a silly reason?
So I ran a query of all the air dates and the episode numbers, and then calculated, basically, a digit 1 through 5-- what it ends in, what it's divisible by, and then the weekday-- and I'm going to use a Pivot Table to analyze this data.
We're in Excel 2007, so we go to the Insert tab; Insert PivotTable; and I'm going to move the episode across the top, and the weekday down the side.
Now, I need a way to count and I don't have any text fields here, so I'm going to take one of the numeric fields, like episode, knowing that it's going to come up with a number that's way too large.
But I can go to Field Settings and change that back to a Count.
I also want a way to analyze this by year to see if it's changed, so initially, I'm going to take the air date.
I have to move it to Row Labels, so that way I can access the group feature.
So I'll right-click, choose Group, and change that to "Years"-- that way, I just have the years going across.
And now that it's grouped, I can take the Airdate and move it up to the Report Filter so I can do some different analysis.
Now, a few things: This new Excel 2007 Pivot Table, I don't mind the Field List, but the Report Layout drives me crazy; I just want to go back to the good old Format.
And I want to make sure that it doesn't keep changing my column widths, so I right-click; go to PivotTable Options; and uncheck these two items.
So now I can make the column lists a bit smaller, and now I'm going to use one of the new Excel 2007 formatting schemes here.
We're going to go a Home, and Conditional Formatting, and apply a color scale where the largest numbers are in green; smallest numbers are in red.
Now, notice that I was careful not to include the Grand Totals because that would have thrown everything off.
And what I see is that, basically, for 51 to 52 weeks, I've managed to have this perfect numbering scheme where the episode that ends in 1 is on a Monday, and the episode that ends in 5 is on a Friday.
But there's been plenty of other times-- 48 weeks-- where it was off by 1, and-- 30-some weeks-- where it was off by 2, and then a few weeks here and there was off by 7.
And so it really isn't something that I've held to over the years, and so I might be free to take Thursday and Friday off.
Let's just do a quick look here in 2008.
In 2008, it has worked out almost perfectly for the entire year, that we've been in sync there.
And if we go back to one of the earlier years, you'll see that it was fairly scattered.
You know, it just depends on how the holidays fall.
So based on this, I think we could take Thursday and then, typically, Friday off from the podcast.
Now, you know I have to thank Leo Laporte.
Leo is the one who suggested this podcast when I was doing "Call for Help", and he even offered to help.
I mean, he basically offered to make it part of the Twit Network, and I didn't take him seriously.
I didn't think that Leo was really going to start to do a whole bunch of podcasts, and so I just started out on my own.
And so the other thing that I'm vaguely cognizant of is that i started the daily MrExcel podcast, and then **** and Leo started the daily Giz Wiz, you know, a few months after me.
And so I've always been ahead of them on episode counts, and basically I'm kind of proud of that and I don't want them to catch up.
So I just did a quick look here, they started on February 20th, 2006; they have 705 episodes.
I use the cool workdays function to figure out that in 720 Monday through Friday dates, they did 705 episodes.
I started 10/15/2005, and in 810 work days I did 795 episodes.
So we both have missed 15 days.
I'm still 90 episodes ahead of them.
Even if I take a couple of days off they will take years to catch up, so I'm using all of this decision to say, "Hey, we're not going to be here tomorrow." I want to thank everyone in the United States who's celebrating Thanksgiving.
They have a great thanksgiving holiday.
Friday, of course, most people have off, so we'll take Friday off and we'll be back on Monday.
And I want to send a sincere thank you to everyone, as we just celebrated our 10th Anniversary; I really appreciate everyone who's made MrExcel-- the site, the podcast, the message board, and everything-- a resounding success.
I know there's a lot of work on everyone's part in that community, to build a great, great site, and I want to thank you.
We'll see you on Monday for another netcast from MrExcel.
