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.

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 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 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 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.

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

Spreadsheets and Data Analysis

View