HOW TO

What is a PivotTable?

Get more out of Excel with this powerful feature.

Microsoft Excel

Turn data into insights

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, your data must be in table form, with no blank rows or columns. You can create a PivotTable manually (this gives experts a bit more control), but the easiest way is to let Excel do the work. Select your data table, then click Insert on the toolbar and then Recommended PivotTables in the Insert ribbon that appears. Excel will automatically create a meaningful layout on a new sheet based on your source data. Here’s the default PivotTable created from the data above:

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

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 labelled 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 North have the most miles, Susan drove a total of 215 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 power at your fingertips.

Keep in mind that PivotTables don’t change your source data, 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 what data you see in the PivotTable? That’s where data slicers come in.

Click anywhere in your PivotTable, then click Slicer on the Insert ribbon. A dialog appears listing each of the Field Names you’ve enabled in your table.

Check one (or more) of these fields and a “slicer” will appear for each that gives you one-click access to specific data. For example, here’s our data with a Region slicer. Click any region to see only its associated data.

Using PivotTables and data slicers, you can easily analyse even the most complex data.

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

    Microsoft Excel

    Turn data into insights

    VIEW