Before Microsoft Excel added the PivotTable tool, you had to use summarizing functions and features to organize and analyze your data. It was often hard, or worse, elusive work. If you’re still working that way, you’re working too hard. Try Excel’s PivotTable tool to turn your raw data into meaningful reports. If it sounds too complex, it really isn’t. It’s a flexible tool that will help you be more productive.

I’m using Microsoft 365, but you can use earlier versions. You can download the .xlsx demonstration file or work with your own data. The browser edition fully supports the PivotTable tool.

LEARN MORE: Microsoft 365 Consumer pricing and features

What is a pivot table?

Excel’s PivotTable tool is easy to use, but if you’re not familiar, you might be stymied a bit, at first. A pivot table is an object that allows you to analyze data by reorganizing and summarizing a specific range of data for reporting purposes. The display is separate from the source data and doesn’t change the source data in any way. In Excel, PivotTable refers to the tool that helps you create a pivot table. It’s a great tool whether you have large or small amounts of data because you can pivot the data to get the information you need quickly in a meaningful layout. Specifically, you can reorganize by sorting, grouping, summing, counting, and so on. 

Before we continue, let’s discuss the terms data and information. Data refers to the values in the two-dimensional column and row format. Information is what you glean from that data in a meaningful way, for instance, which sales personnel met their quotas or which student in each class has the highest GPA. Using a pivot table, you might show monthly sales by products, sales regions, or even personnel. If you’re a teacher, you might show student averages by class or subject. I keep a running total of income by clients using a pivot table. In a two-dimensional spreadsheet, you can’t easily discern meaningful information.

Now, let’s start pivoting!

SEE: Windows 10 logo key shortcuts to speed your work (free PDF) (TechRepublic)

Step one: Identify the source data

Before you can create a pivot table, you must identify the data you want to analyze. The data should not contain blank rows or columns, subtotals, and should be in tabular format. Figure A shows a simple set of sales data by date, personnel, and region. Now, let’s begin the process of displaying sales totals by personnel using a pivot table. Click anywhere inside the data set to begin and then do the following:

1. Click the Insert tab and then click PivotTable in the Tables group.
2. In the resulting dialog, make sure the range is correct (it always is but this is a good way to catch a blank row or column off screen).
3. Specify whether you want the new pivot table in the existing worksheet or in a new worksheet. We’ll work in the existing worksheet so we can see both the data and the pivot table. To do so, click the Location control’s arrow, which will reduce the dialog to only that control. Then, click the anchor cell for the new frame (in the sheet), and then return to the dialog by clicking the arrow. As you can see in Figure A, I clicked F3 as the anchor cell.
4. Click OK.

Figure A

At this point, you have a pivot table frame (Figure B), but it’s empty. In the next section, you’ll build the pivot table using the frame options.

Figure B

Step two: Identify the fields and values

With the frame in place, you’re ready to position the columns, rows, and values. Let’s consider what we want and how to achieve it before we start dragging fields around: We want to display sales totals.

The PivotTable Field pane offers the settings you need to fill the frame. If you don’t see it, click inside the frame. If you still don’t see it, right-click the frame and choose Show Field List from the resulting submenu. Once the pane is available, drag Personnel from the top pane to the Rows field and then drag Amount to the Values field. The PivotTable tool will default to a sum for that field. (You can change it by clicking the down arrow and choosing Value Field Settings.)

As is, the PivotTable is a bit rough (Figure C). Fortunately, you can add formats and change field names to create a better report. First, click F3 and replace Row Labels with Personnel. Then, click G3 and replace Sum of Amount with Total Sales. Finally, format the values in the second column as follows:

1. Right-click the Grand Total header cell.
2. Choose Number Format from the resulting submenu.
3. Choose Currency in the Category list.
4. Click OK.

Figure C

The subtle changes shown in Figure D complete the picture. With a few clicks and drags, you now know the total sales for all personnel. Congratulations! You just created your first pivot table. Now, let’s review a few ways to change the information displayed by the same pivot table.

Figure D

Let’s practice

Creating that table was easy; modifying it can complement or completely change the information the table displays. Figure E shows the result of dragging the Date field to the Columns list. The result is a subtotal for personnel by date–with only one change. The end total (Grand Total) is the same as it was in the original table.

Figure E

Now let’s really mix things up by dragging Region to the Rows field. Doing so displays even more details about the source data and those date subtotals are now split between regions, as shown in Figure F. Notice that the grand totals for the person and the region still equal the date subtotals. All we’re doing is adding more information by displaying more details. 

Figure F

Let’s try one more change: Let’s display the date totals by quarters. To do so, click any date header cell (G4 through J4) and choose Group from the resulting submenu. The grouping options are very flexible and are worth exploring later. For now, deselect Months in the By list, select Quarters, and click OK (Figure G). All of the dates fall in the second quarter, as you can see in Figure H. As you add more dates, a quick refresh of the pivot table will show first-, second-, third-, and fourth-quarter dates automatically. 

Figure G

Figure H

There’s much more

You’ve seen the tip of the iceberg, and the only way to learn more is to start exploring. Add and delete fields; drag rows to columns and column to rows, check out the different field and grouping settings. Don’t be alarmed if you create a mess! That’s part of the learning process. I can’t tell you how many times I’ve thought, “That’s not what I meant to do,” when working with a pivot table. Once you’ve got the hang of it, you’ll find lots of reasons to turn your data into meaningful information via the PivotTable tool.

Also see