Hey welcome back to the Mr. Excel net cast. I am Bill Jelen. Basics—we start out with massive amounts of data and they say, ‘how are we going to analyze this?’ Well let us fire up the pivot table and see if we can solve this problem.
Hey welcome back the to Mr. Excel net cast. I am Bill Jelen. Hey, let us take a look at Excel 2010sPiivotTable features. Now back in episode 1067, I covered the new Slicer feature but there are some other cool things that have given us in pivot tables.
So let us create a quick PivotTable here, click OK and I am going to put a couple of different fields down the left hand side and then Revenue, COGS, to console 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 was always a way to solve that in Excel 2007.
We’ll come back here, Report Layout, we could say Show an 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 have been in one of my seminars or read any of my books, you know that for me, most to the time, a PivotTable is a means to an end. I take this PivotTable, 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, it is very frustrating that they do not fill in this items here in Column A and in the past podcast. I’ve gone through in showing 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 have given us an options that we do not have to go through that whole dance anymore.
Back here on the PivotTable 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 ion 2002, the ‘Guerilla Data Analysis’ using Microsoft Excel, I complain bitterly about this and I know one time I was out at Microsoft, I saw that book on the table they had little sticky notes and so you know, back from 2002 and here to 2009, it took 7 years but that has finally been corrected. That is a huge, huge improvement in PivotTables. So especially if you are taking this data and then want to do something else with it, you further analysis. So a great, simple, little feature that they added in would make the life much easier for those who much use pivot tables in order to get a data set that we can do then further analysis with.
Hey I wan to thank you for stopping by. See you next time for another net cast form Mr. Excel.
Hey thanks fro stopping by. We’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services