How to Analyze Data Using Excel PivotTables

How to Analyze Data using Excel PivotTables

When you compile data in a list, you often need to answer questions such as "How much revenue did the West Coast office generate last month?" or "What was the average number of customers served at each office in each quarter last year?"

Excel's PivotTables (Microsoft runs the words together, although you?ll find pivot tables in other spreadsheet programs) can provide those types of answers.

Do you want to group data by category? Use a PivotTable. Interested in comparing results by person, by quarter, or by category? Use a PivotTable. Need to answer questions that start with "How many?" or "How much?" PivotTables can do that, too. I?ll show you how to accomplish those tasks and more.

If you'd like to work through the examples I'll present in this article, you can download my Excel spreadsheet.

What Is a PivotTable?

On the right is a PivotTable that summarizes the data from the list on the left. The data from the Consultant field is arranged in rows, the data from the Category field is arranged in columns, and the table summarizes the numbers of clients each consultant is handling.

A PivotTable is an Excel tool for summarizing a list into a simple format. You create PivotTables from lists, as you define which fields should be arranged in columns, which fields should become rows, and what data you wish to summarize. You don't have to use all of the data in a spreadsheet?just the data and the fields you need to answer your questions. Once you've created the table, you can then see the answer to your question. You can later reuse the PivotTable to answer different questions by rearranging it.

Create a PivotTable

Before creating a PivotTable, consider the questions you want to answer, or which information you wish to extract from your data. This step will determine how you should construct the table.

Let's consider the data in this worksheet, and the question "How much did we earn from Development for each quarter in 2011?"

How to Analyze Data using Excel PivotTables
We'll use this data to illustrate how to create a PivotTable to answer the question "How much did we earn from Development for each quarter in 2011?"

To create the PivotTable, click somewhere in the list of data, choose the Insert tab, and click PivotTable. Excel will automatically select the area containing the data, including the headings. If it does not select the area correctly, drag over the area to select it manually. Placing the PivotTable on a new sheet is best, so click New Worksheet for the location and then click OK.

You might become confused at this point, because if you've never created a PivotTable, nothing you see on this screen will look familiar. In reality, it?s simpler than it looks. The PivotTable Field List panel, as its name suggests, contains the fields from your list; all you need to do is to arrange them in the boxes at the foot of the panel. Once you?ve done that, the diagram on the left becomes your PivotTable.

The PivotTable Field List panel contains all the fields from your data; you then arrange them in the boxes below.

In the PivotTable Field List on the right side of the screen, click the Quarter, Category, and Feescheckboxes; this is the data you need to answer the question "How much did we earn from Development for each quarter in 2011?" When you do this, Excel creates a PivotTable for you. The data might not be arranged exactly to your liking, but that's an easy fix.

If you want to see the categories as columns and the quarters as rows, for instance, drag Quarter to the Row Labels box and drag Category to the Column Labels box. You can now read the answer to your question in the PivotTable, because column B now contains the results for fees earned from Development for the four quarters individually, as well as the grand total for the year.

Next Page: How to Format a PivotTable

Subscribe to the Best of TechHive Newsletter

Comments