Excel Pivot Tables

This weekend, my dad was talking about trying to learn how to use pivot tables, so here you go dad. This one's for you.

A pivot table is a fancy excel name for a list summary. For example, let's say I have an excel list. Columns A and B are last and first name respectively. Column C is age. Column D is Amount. So to create a summary of this data:

• Start on an empty worksheet
• Select: Tools --> Pivot Table & Pivot Chart report
• Choose Microsoft Excel list and Pivot table and click next
• Go to your list and select columns A - D
• Click next
• Click Finish

If you get an error that says "Pivot table field name is not valid", you probably are missing a column heading. Excel won't create a pivot table unless every column has a heading.

You will now have a template on the left and a "Pivot Table Field List" on the right. Now you can start dragging and dropping your fields from the field list into the four separate areas in your pivot table. The four areas are: Page fields, Row Fields, Column Fields, and Data fields. As you drag and drop fields into the pivot table, your field names will appear as gray boxes. You can drag these gray boxes around as much as you like until you get your table to look the way you want.

So, in my example above, if you just want to add up the amount. Drag amount to the data field.... Hey!! Now there's one of my real annoyances with excel pivot tables. It thinks I want to count amount instead of add it up. Well, we can fix that. Right click on the data or the gray box and select field settings from the dropdown box. Here I can tell the pivot table that I want a sum (or average if I had chosen the age field) and pretty much anything else.

Annoyance #2 - Pivot tables always think you want your data in rows if you have two or more things that you've dragged into the "data" section. I like my data in columns so drag the gray box from the top left corner of the table into the "column" area.

There you go. Play around with it for 15 to 20 minutes and you'll be a pivot table pro.

Tomorrow -- the VLOOKUP function