The Power of the Pivot: Get Started with Excel Pivot Tables

If you rely on spreadsheets to manage any aspect of your business, pivot tables will make your life a lot easier.

Pivot tables are among the most powerful features of Microsoft Excel, yet many people are either unaware of them or intimidated by them. If you’re in either of those camps, this post will introduce you to pivot tables and walk you through the steps for creating one.

What is a pivot table?

A pivot table is a reporting tool that lets you sort, summarize, analyze, and present data in multiple ways while leaving your original data unchanged. Pivot tables work independently of your original rows and columns, thereby allowing you to create different views and reports without altering the original data table.

The benefits are many, including:

• The ability to generate and extract meaningful information from large tables within minutes.
• Time savings and efficiency. Pivot tables can be created in minimal clicks and you don’t need to write a macro or track down a programmer.
• Low memory usage. Pivot tables won’t slow down your PC.
• Automatic updating. Because pivot tables reference your original data, they stay in sync with updates and changes.

So let’s create a simple pivot table. But before we do …

Ensure your data is organized properly

Before you can create a pivot table, it’s essential your data is organized in a way Excel can understand. Otherwise, you’ll be embarking on a frustrating, futile and fruitless journey.

1. Organize your data in rows and columns, with each row containing information about one record, such as a sales order or inventory transaction.
2. Use headings. The first row must have headings for each column, and each heading should be unique.
3. Keep data types together. Each column should contain the same type of data, such as “text” or “currency” or “percentage” or “date”. Do not mix these in a single column.
4. No blank rows or columns. There can be blank cells (though keep that at a minimum), but do not have any completely blank rows or columns in the table.
5. Keep data isolated. Leave some distance between the dataset you’re using for the pivot table and any other data on the worksheet. To do this, keep a buffer zone around your target data using one or more blank rows and columns to ensure it’s isolated.

Create your pivot table

For this example, I’m using Excel 2010 (for Mac users, this is synonymous with Excel 2011) because it’s still the predominant version used by most small and mid-sized businesses. Good news: my colleagues tested it with Excel 2007 (PC/Mac) and the steps are nearly identical. Eggggsellent.

Here’s the data we’re starting with. We’ve got 3 salespeople who are selling 3 different products across 3 regions. Their sales are being calculated monthly.

Here we go.

Select the data

1.  Highlight the data – columns and rows – that you want to use. Be sure to include the header row.

2.  From the Excel ribbon, click the Insert tab.

3.  In the Tables group, click PivotTable.

4.  A Create PivotTable dialog box will appear. It should be pre-filled with the data range you just highlighted. If not, click on the worksheet and select the range manually.

5.  Select New Worksheet or Existing Worksheet as the location of your pivot table, and then click OK.

Add fields to the pivot table, and begin creating reports

After completing step 5, you’ll be faced with an empty pivot table that lives on the existing sheet or a new sheet, depending on the option you chose.

When you select any cell within the pivot table (cell A3 in this example), a PivotTable Field List will appear to the right. It has two sections: A top “checkbox” list and a bottom 4-square area. Notice that the top section’s list of options will match the original data table header row names.

(Note that I’ve dramatically tightened the Excel window to ensure all the important bits are in the image. Yours will likely not be this squished.)

6.  To build the pivot table, first, choose fields from the top section. For each option (criteria) you choose, it will be automatically placed

Power to the pivot

Once you start using pivot tables, I guarantee you will be hooked and wonder how you ever did your job (or your volunteer work or your hobbies or your finances) without them.

True, there are some very complex, interactive pivot tables, but they don’t have to be. Simple tables (like the one used in this post) can be tremendously effective at conveying useful and insightful information. It’s a good bet that you have oodles of data right now at your disposal just waiting to be converted and experimented with.

Dive in.

It will take some trial and error, but getting comfortable and functional with pivot tables is a relatively quick process.

Download a pivot table workbook and start practicing

Do a quick search and you’ll find multitudes of pivot table templates and tutorials. But if you’d like to download the workbook used to create the above examples, you can download it here. Feel free to play with it, modify it to fit your needs, and experiment with it.