Explaining PivotTables

PivotTables are one of the wildest but most powerful features of Excel that may take some experimentation to figure out. When you first use a PivotTable, the process can seem a bit daunting. Some persistence is definitely necessary.

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.

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?

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 updated each time you open the workbook or click Refresh.

Guidelines for Creating Tables/Lists for Use in PivotTables

When you create a PivotTable, you must organize the dataset you’re using in a table and/or a list. As the PivotTable will base all its data on this table or list, it is vital that you set up your tables and lists in a uniform way.

In this context, a table is no more than a list that has a title, has more than one column of data, and has a different heading for each column. A list often is referred to in the context of a table as well. The best practices that apply to setting up a list will help you greatly when you need to apply a PivotTable to your data.

Following guidelines help you setting up your table or list:

  • Headings are required, as a PivotTable uses them for field names. Headings should always appear in the row directly above the data. Also, never leave a blank row between the data and the headings. Furthermore, make the headings distinct in some way; for instance, boldface them.
  • Leave at least three blank rows above the headings. You can use these for formulas, critical data, etc. You can hide the rows if you want.
  • If you have more than one list or table on the same worksheet, leave at least one blank column between each list or table. This will help Excel recognize them as separate entities. However, if the lists and tables are related to each other, combine them into one large table.
  • Avoid blank cells within your data. Instead of leaving blank cells for the same data in a column, repeat the data as many times as needed.
  • Sort your list or data, preferably by the leftmost column. This will make the data easier to read and interpret.

If you follow these guidelines as closely as possible, using PivotTables will be a relatively easy task.

Pivot Tables

  1. Explaining PivotTables
  2. Create a PivotTable
  3. Automate PivotTable Creation
  4. Save or Share Pivot Table Without Source Data
  5. Extract PivotTable Data Using GETPIVOTDATA