PivotTables: A comprehensive guide (part 1 of 2)

If you are anything more than a very basic Excel user, then this post is important to you. It introduces undoubtedly the most powerful single element of Excel: PivotTables. If you're new to them, this is one post I heavily encourage you to read.

PivotTables bring analysis to the masses. All you need to use them is a set of organised data and an appetite to analyse it.

Whether your data is made up of people in an organisation; defects in an IT system; risks and issues associated with a project; stock items and their location; invoices and their status. All of these datasets are the lifeblood of PivotTables.

So what do PivotTables do? Quite simply, they allow you to quickly and easily summarise and interrogate data. You can find the gender balance of employees by region; the average closure rate of defects by assignee; the number of open issues by severity; the number of stock items within each warehouse; the maximum time it has taken each client to pay their invoices.

So, let's get started.

First up, you need a dataset. Your data needs to be organised in contiguous columns (no empty columns in the middle), each column containing a specific attribute of the data set: gender, defect assignee, issue severity, stock location, invoice issue date.

And each row needs to be an item pertinent to the dataset: an employee, a defect, an issue, a stock item, an invoice.

Once you've got that, the best thing to do is to format the dataset as a Table. On the Home tab, hit Format as Table, and select your favourite colour scheme. This action does three key things:

• The range in which your data sits is immediately and automatically given a name. So instead of referring to it by cell references, you can simply reference its name
• The Table gains some lovely aesthetics—a special title row and alternate row shading thereafter. If the default colours aren't suitable, you can define your own by hitting New Table Style at the bottom of the Format as Table box. And if you scroll down in the Table, the column names nudge up to replace the A, B, C … column labels. Which is a lovely touch
• The data is treated as one. Filters and sorting automatically knows what to filter or sort, while adding new rows at the base of the Table—or columns to the right—ensure that they're kept with the family, and are added to the named range.

But while nice in themselves, many attributes of a Table are perfectly suited to PivotTables.

So let's create one.

While your cursor sits within the Table, from the Insert ribbon, hit PivotTable. This will bring up a dialog box.

Ensure the New Worksheet radio button is selected and hit Enter. Wherever possible, I always prefer my PivotTables to sit within their own sheet. You'll learn later that they are dynamic beasts of variable and often unpredictable size, so they don't sit well alongside other data.

A new worksheet will be created containing a frame that will form the basis of your PivotTable.

Each of your column names will appear in the list on the left. And the frame of the PivotTable itself will appear in the spreadsheet on the right. And the column names can be dragged and dropped, either into the table itself or into the four boxes below—each option results in the same outcome.

The latter post in this pairing introduces the analysis itself—which is where the arousal begins.

• Tweet