The thing that is so fantastic about Pivot tables is that you can summarize huge amounts of data to help you and your users better understand what your huge amount of data has to say to you. You can also summarize small amount of data, too. And, then once you've summarized your data in a Pivot Table, you can "pivot" the data to look at in a different way.
I used to hate pivot tables. But that was back when they confused me, and I couldn't understand why they were called "Pivot" tables. Now, I get them. I really, really get them. And, I love them. I hope you will, too.
I've alluded to this before. Pivot tables are pretty easy, but they aren't exactly like Excel. I don't know what Microsoft has to say about this, but I personally consider Pivot tables to be an application within an application. If you try to use the data in a Pivot table just like you would the data in Excel, you'll hate Pivot tables. So, before we even start, I want you to commit to thinking of Pivot tables as its own application. Can you do that? Doing that will be the thing that takes you from User to Super-User! Yay, are all your dreams coming true?
Assuming you can commit, let's pretend that you are a flea market/pet shop owner. You have been keeping a list of the items that you've sold and when you've sold them, but so far, that data isn't really speaking to you. It's not telling you a story.
Click on Data-->PivotTable and PivotChart Report to bring up this wizard:
Because this is a basic lesson, let's go with the defaults, and then click NEXT. Excel is so clever, that it knows where your data is and will highlight it for you, as long as it contiguous. For this example, go with what Excel suggests:
and just click next.
Go with the defaults here and click Finish. You can choose where you want the pivot table to go right now, if you are so inclined. Doesn't really matter, though. You can copy and paste the whole thing if you need to move it somewhere else.
Here's what you will see:
Now just start dragging and dropping.
Oh, gosh. Before you start, let me give you some definitions:
Row Fields: Data that will appear in the Rows.
Column Fields: Data that will appear in the Columns.
Page Fields: I don't use this very often. And, I'm not missing out on much, either.
Data Fields: Here's where things get summarized. So, the data field will always be a number. It can be a sum, average, count, min, max, etc. You can put text fields here and then count them. You can put date fields here and count, min, max or average them. Or you can put a number data here and summarize it however you want.
I normally start with the Row Fields first. In this case, I want to see a summary of the items sold. So, click on ITEMS and drag and drop it to the Row Fields Box. Now Drag over Qty Sold to the data box.
Check it out! You've summarized your data by item.
So, you can see that you sold 97 Llamas, but only 21 Llama harness. There's an opportunity right there, I think.
This view is interesting, for sure. But, it doesn't give you any ideas about when your items were sold. Go back to your field list, drag and drop "Date Sold" to the Row Block. Say, now that's interesting.
But, you'd like to see it with the dates across the top. You can do that. Grab Date Sold, either from the field list or the pivot table, and drag it to the Column Block. Now look!
This is not easy to look at, though, is it? Since Pivot Tables are so great at summarizing, you don't want to do all that paging over. Ick. Pivot tables has a solution. Right click on any date in the column headings. Choose Group and Show Detail and then Group.
You get this dialog box.
To make things easy, choose months. You get this view. NICE.
I hope this has been educational. Tomorrow, I'll show you how to actually Pivot that data, really turning it on its side.
Love Pivot Tables.
Click here for the sample data to play with:
Pivot Data
