What’s a PivotTable?

The best way to understand what PivotTables are and how they operate is… to create one and experiment with it! The following example creates a PivotTable showing the total sales values of the top 10 best-selling products in an Excel table containing just over 2,000 invoices.

Here is the original table of sales invoices—the screen is split to show the total rows of data. The Table Name box in the TableTools, Design contextual tab shows the table is named ‘SalesInvoices’:

It is possible to create a PivotTable based upon any range of cells, but the advantage of using a table is that the PivotTable will adjust more automatically to include any new rows and columns of data added to the range.

The table contains seven columns:

• OrderID
• OrderDate
• CompanyName
• Country
• Salesperson
• ProductName
• ExtendedPrice

ExtendedPrice represents the total sales value of each invoice line – the quantity of items sold multiplied by the unit price of each item. A single invoice can have several lines, one for each different product ordered, so you will see the same OrderID for several lines.

Just glancing at 2,156 lines won’t easily tell you which products are selling best, which is where a PivotTable comes in.

Filtering by Top 10

Right-click any ProductName and from Filter, choose Top10:

From the Top 10 Filter dialog box you can choose whether to display items from the top of the list or the bottom; how many items to display—and whether that figure should just be a number, a percentage of the total, or the number of items that add up to a minimum value – and which field to base the top or bottom calculation on:

In this case, you can just leave all the default values set to show the top 10 products by invoice total, so click OK. The Top 10 list displays:

The list would probably be clearer if sorted in descending order of values to give a kind of league table. To do this, again right-click any ProductName, then from Sort, choose More Sort Options.

In the Sort dialog box, choose to use descending order, and then click the dropdown arrow to choose Sum of ExtendedPrice before clicking OK:

The Top 10 sellers are now sorted in descending order by value:

Recommended PivotTables

We’ve just seen how quickly we can set up a PivotTable from our source data, but there is an even quicker way: Recommended PivotTables. We can click in any cell in our source data and, from the Insert ribbon tab, Tables group, choose Recommended PivotTables. Excel presents a set of PivotTables based on your set of data – you can choose the most suitable with a couple of clicks:

AAT students and professional members can access a wide range of Excel resources and training here.

For more information about studying with AAT, click here.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.