What is a pivot table? (Excel)

Posted on 2011-10-25
Last Modified: 2012-05-12
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?

Question by:Steve_Brady
    LVL 39

    Accepted Solution

    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, but you'll get all your questions answered on EE.

    LVL 80

    Assisted Solution

    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.
    LVL 50

    Assisted Solution

    Hello Steve,

    Jon Peltier has a good number of resources on his site. Start with , which is the first link on this page  and then continue with the other links on that page.

    cheers, teylyn

    Author Comment

    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!  :)

    Author Closing Comment

    Thanks -- great responses!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now