• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

What is a pivot table? (Excel)

Note -- this is one of three threads with the following titles:

   • What is a pivot table? (Excel)
   • What are the main purposes or benefits of pivot tables? (Excel)
   • What are some scenarios in which pivot tables are most useful? (Excel)


I am a bit embarrassed to admit this but despite having used Excel for many years and having come across references to pivot tables on numerous occasions, I really don't know what they are or anything about them.  Nevertheless, the frequency with which I see them mentioned suggests that they are a valuable tool and worth knowing how to use.  For that reason, I would appreciate any comments that answer the following question:

   • What is a pivot table?

3 Solutions
Pivot tables are analysis tools that will enable you to summarize and analyze (huge) sets of data in a meaningful and flexible way.

I like the tutorials on office.microsoft.com, but you'll get all your questions answered on EE.

For what it's worth, I went almost 20 years as an Excel power user (in the engineering profession) before finding a good use for PivotTables.

Every time I tried exploring the feature, I found the wizard too confusing, and the appearance of the resulting PivotTable is pretty ugly. PivotTables require a manual refresh to get them to update if there is new data, and they are locked by default into fixed addresses for data (e.g. $A$3:$G$1703).

It is mandatory that you normalize the data before starting. Normalizing means that you list your records in columns and fully populate each of the properties. The example below shows the right way and the wrong way to do it.
 Examples of not normalized vs normalized data
The workaround for the fixed address range is to use either a dynamic named range (any version Excel) or a Table (Excel 2007 and later).

The workaround for the ugly appearance of the report is to readjust your requirements for appearance. Excel likes to impose its own formatting when you refresh the PivotTable.

PivotTables are more commonly used in Sales or Financial fields because of the need to summarize the data (e.g. make a table of sales by Salesman or by customer). You'll find that for those things the PivotTable can do, it is much faster than the equivalent formula-based (SUMPRODUCT, SUMIFS or COUNTIFS) solution. You will also enjoy the fact that Excel populates a list of the required members for each of the fields (e.g. all of the salesmen and all of the customers)--one of the more difficult tasks in a formula-based approach.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello Steve,

Jon Peltier has a good number of resources on his site. Start with

http://peltiertech.com/Excel/Pivots/pivottables.htm , which is the first link on this page

http://peltiertech.com/Excel/Pivots/  and then continue with the other links on that page.

cheers, teylyn
Steve_BradyAuthor Commented:
For what it's worth...


As is my custom, I'm going to leave this thread open for quite a while.  However, I do want to tell you that the first part of your post is "worth" a lot!  LOL  Not only did it eliminate the "bit of embarrassment" I mentioned above but I doubt I will ever hesitate asking anything -- which I think I should already know -- again.  So thanks for including "a bit of" personal history to assuage my pain!  :)
Steve_BradyAuthor Commented:
Thanks -- great responses!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now