HOW TO

What the Heck Is a PivotTable?

Get more out of Excel with this powerful feature.

Microsoft Excel

Spreadsheets and Data Analysis

View

One of Microsoft Excel’s most powerful features is the PivotTable, which lets you quickly take different views of the same set of data. Using PivotTables, you can answer a slew of questions without having to create complicated formulas and format their output.

Consider a table of data showing delivery drivers’ daily mileage:

You could figure out how many miles Susan drove, or which region had the most miles, by creating a series of formulas. But a PivotTable lets you answer both questions—and many others—with less work. Here’s how.

Pivot with a click

To create a PivotTable, select your data—it should be in a table with no blank rows or columns—and click Insert in the toolbar, then click Recommended PivotTables on the Insert tab that appears. Excel will automatically create a meaningful layout on a new sheet.

You can now easily see which region (West) had the most miles.

But what if you want to see how many miles Susan drove? That’s the beauty of a PivotTable.

Click anywhere in the PivotTable and Excel presents a pane to the right with sections labeled Field Name, Filters, Columns, Rows, and Values. In the Field Name section, check Driver and you’ll see drivers in your PivotTable. In the Rows section, drag Driver above Date. This makes Driver the primary and Date the secondary entry in each row.

The updated PivotTable now answers both the questions you had: You can see that not only did West have the most miles, Susan drove a total of 231 miles. To make the table even easier to read, uncheck Date in the Field Name area:

This is a very simple example, but it shows some of the power at your fingertips.

Keep in mind that PivotTables don’t change your source data—it’s still in the original sheet—so you can play around without fear of ruining anything. And if you add data to your source table, Excel updates the PivotTable.

Slice that data

Want to quickly modify which data you see in the PivotTable? That’s where data slicers come in.

Click anywhere in your PivotTable, then click Slicer on the Insert tab. A dialog appears listing each of the fields you’ve enabled.

Check one or more of these fields and a “slicer” will appear for each that lets you quickly filter data. For example, here’s our data with a Region slicer. Click any region to see only its associated data.

Using PivotTable and data slicers, you can easily analyze even the most complex data.

For more information on pivot tables, see Excel’s in-app help, or the online version, which has links to additional tips: