Excel 2010 finally gives you an option to fill in the blank spots in the outermost row fields. Episode 1078 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 netcast.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
So, how we're going to analyze this.
Well, let's fire up a pivot table and see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Hey, let's take a look at excel 2010's Pivot Table features.
Now, back in episode 1067, I covered the new slicer feature, but there's some other cool things, they've given us in Pivot Table.
So, let's create a quick Pivot Table here, click OK, and I'm going to put a couple of different fields down the left hand side and then Revenue, Cost of Goods, Sold and Profit.
Going across, and one frustration, that we had in excel 2007, was that it puts both Region and Product in column A, but there is always a way to solve that, in Excel 2007. We came back here Report Layout, we could say "Show in Outline Form" and then we get back to the normal view, where we have Region in column A and Product in Column B.
But, you know, if you've been in one of my seminars or read any of my books, you know that for me, most of the time, a Pivot Table is a means to an end.
I take this Pivot Table, I then want to go through and convert to values, that way I can do other things with it.
But when I convert it to values, this is very frustrating that they do not, fill in these items here in Column A.
And in past podcasts, I've gone through and shown you how to use Edit, Go To, Special, Blanks and then, equal, up arrow, Ctrl+Enter, to fill all those in.
But now, finally they've given us an option so, that way you don't have to go through that whole dance anymore.
Back here on the Pivot Table, Tools, Design tab, go to Report Layout and say, "Repeat All Item Labels" and it fills in the word Central all the way down.
This is excellent. The very first book, that I wrote back in 2002, The Guerilla Data Analysis Using Microsoft Excel, I complained bitterly about this, and I know, one time I was out at Microsoft, I saw that book on the table, and they had little sticky notes.
And so, you know, back from 2002 here to 2009, it took 7 years, but that has finally been corrected. That is a huge, huge improvement in Pivot Tables.
Especially, if you're taking this data, and then want to do something else with it, you know, further analysis.
So, great simple little feature that they added in, will make life much easier for those of us using Pivot Tables, In order to get a data set, that we can then do further analysis with.
Hey, I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
Well, thanks for stopping by, Will see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
So, how we're going to analyze this.
Well, let's fire up a pivot table and see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Hey, let's take a look at excel 2010's Pivot Table features.
Now, back in episode 1067, I covered the new slicer feature, but there's some other cool things, they've given us in Pivot Table.
So, let's create a quick Pivot Table here, click OK, and I'm going to put a couple of different fields down the left hand side and then Revenue, Cost of Goods, Sold and Profit.
Going across, and one frustration, that we had in excel 2007, was that it puts both Region and Product in column A, but there is always a way to solve that, in Excel 2007. We came back here Report Layout, we could say "Show in Outline Form" and then we get back to the normal view, where we have Region in column A and Product in Column B.
But, you know, if you've been in one of my seminars or read any of my books, you know that for me, most of the time, a Pivot Table is a means to an end.
I take this Pivot Table, I then want to go through and convert to values, that way I can do other things with it.
But when I convert it to values, this is very frustrating that they do not, fill in these items here in Column A.
And in past podcasts, I've gone through and shown you how to use Edit, Go To, Special, Blanks and then, equal, up arrow, Ctrl+Enter, to fill all those in.
But now, finally they've given us an option so, that way you don't have to go through that whole dance anymore.
Back here on the Pivot Table, Tools, Design tab, go to Report Layout and say, "Repeat All Item Labels" and it fills in the word Central all the way down.
This is excellent. The very first book, that I wrote back in 2002, The Guerilla Data Analysis Using Microsoft Excel, I complained bitterly about this, and I know, one time I was out at Microsoft, I saw that book on the table, and they had little sticky notes.
And so, you know, back from 2002 here to 2009, it took 7 years, but that has finally been corrected. That is a huge, huge improvement in Pivot Tables.
Especially, if you're taking this data, and then want to do something else with it, you know, further analysis.
So, great simple little feature that they added in, will make life much easier for those of us using Pivot Tables, In order to get a data set, that we can then do further analysis with.
Hey, I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
Well, thanks for stopping by, Will see you next time for another netcast from MrExcel.
