Microsoft Excel

Working with PivotTables

PivotTables are one of the wildest but most powerful features of Excel that may take some experimentation to figure out.

We use PivotTables a lot when we develop spreadsheets for our clients. Once a client sees a PivotTable, they nearly always ask whether they can create one themselves. Although anyone can create a PivotTable, unfortunately many people tend to shy away from them, as they see them as too complex. Indeed, when you first use a PivotTable, the process can seem a bit daunting. Some persistence is definitely necessary.

You'll find that persistence will pay off once you experience the best feature of PivotTables: their ability to be manipulated using trial and error and immediately show the result of this manipulation. If the result is not what you expect, you can use Excel's Undo feature and have another go! Whatever you do, you are not changing the structure of your original table in any way, so you can do no harm.

Why Are They Called PivotTables?

PivotTables allow you to pivot data using drag-and-drop techniques and receive results immediately. PivotTables are interactive; once the table is complete, you very easily can see how your information will be affected when you move (or pivot) your data. This will become patently clear once you give PivotTables a try.

Even for experienced PivotTable developers, an element of trial and error is always involved in producing desired results. You will find yourself pivoting your table a lot!

What Are PivotTables Good For?

PivotTables can produce summary information from a table of information. Imagine you have a table of data that contains names, addresses, ages, occupations, phone numbers, and Zip Codes. With a PivotTable, you very easily and quickly can find out:

  • How many people have the same name

  • How many people share the same Zip Code

  • How many people have the same occupation

You also can receive such information as:

  • A list of people with the same occupation

  • A list of addresses with the same Zip Code

If your data needs slicing, dicing, and reporting, PivotTables will be a critical part of your toolkit.

Why Use PivotTables When Spreadsheets Already Offer So Much Analysis Capability?

Perhaps the biggest advantage to using PivotTables is the fact that you can generate and extract meaningful information from a large table of data within a matter of minutes and without using up a lot of computer memory. In many cases, you could get the same results from a table of data by using Excel's built-in functions, but that would take more time and use far more memory.

Another advantage to using PivotTables is that if you want some new information, you can simply drag-and-drop (pivot). In addition, you can opt to have your information update each time you open the workbook or click Refresh.