Hidden Gems #6: Pivot Tables

Sherlock HolmsThis month’s Hidden Gem is a timesaver, a powerful business analysis tool, and a persuasive sales technique. It is included in every edition of Microsoft Office, so you probably already have it. It encourages clarity, simplicity and makes understanding information simple for everyone.

And yet few small businesses know about it. So, what on earth is going on?!

We are on the trail of Pivot Tables, and it would seem that the biggest problem is that they are usually badly explained. I shall therefore take it upon myself to light my pipe, sit back in an easy chair, and see if I can do any better!

Case Notes...

The Target: Pivot Tables, a function which allows useful analysis of large amounts of information.

Whereabouts: Included in Excel, available in all versions of Microsoft Office (2003, 2007 and 2010), or available standalone from the Microsoft Store.

Modus Operandi: Start with large amounts of information, and extract from it key facts, trends and analyses without needing to know any programming or formulas.

Appearance:

Case History...

To understand Pivot Tables, we need to use a typical example. Here are the (admittedly fairly meagre) takings from a business in one day. In each row, we have a client’s name, whether they are a personal customer or a business, the source of the business (a visit to the shop, the web or a mail order), and the amount spent. It’s just the sort of spreadsheet that a  small business is likely to have.

In row 14, it would be fairly easy to tot up the profits for the day - you would use the formula =SUM(D2:D13) .

But even though we have only a few rows and even fewer columns, it would take a good deal of thinking to work out anything much more useful. Did you notice that Shah Services is the only client who had two orders? Could you work out what’s more profitable; selling on the web or keeping the shop open?

These are the sorts of questions which Pivot Tables are designed to answer. They do so without you having to write long formulas; and, better still, your original data remains untouched. This allows you to execute complex analyses without making your data unintelligible, or having multiple copies of the same data in different spreadsheets.

Let’s examine Pivot Tables in action.
Select the Insert tab, and then Pivot Table. The Pivot Table dialog box will appear. In most cases, you will want to use the existing spreadsheet (rather than an external data source; you also have the option to put results in the same workbook or a new one.

In the new worksheet which then appears, on the right hand side of the screen you will also see the crucial control forPivot Tables; the Pivot Table Field List. In this box, we can select and drag all the fields (‘fields’ is the techie name for Column Headers) into different roles in our analysis.

Let’s start with an easy one. By dragging ‘Client’ into the Row Labels box, and ‘Sum of Spend’ into the Values box, we see what each client has spent. It’s a list very similar to the one we started with, but Shah Services appears only once – the two orders have been combined.

Let’s perform exactly the same operation, but with Type as the Row Label.

This shows us, with absolute clarity, that business clients are much more profitable than personal visitors!

Now let’s put ‘Clients’ back in the Row Labels box and keep ‘Sum of Spend’ in the Values list, as we did earlier. But now, we’ll add the ‘From’ field to the Column Labels box. Excel gives us the ‘Who spent what’ analysis we saw before, but now with column splits to show the different contributions to sales made by each sales mechanism We can see who spent what; but also that the web is this business’ most profitable sales tool – perhaps those passing visitors to the shop aren’t worth the rent and shopfront!

For easy reporting, you can have as many row labels as you want. Here’s an example where we have put both the ‘Type’ and ‘From’ fields in the Row Labels box. This allows us to compare spends across all 6 possible permutations of sales source and customer type.

You will also by now have noticed the filter box. If we return to our classic example of ‘Client’ in the Row Labels box and ‘Sum of Spend’ in the Value box, we’ll try adding ‘From’ to the Report Filter box. This adds a neat filter to the displayed spreadsheet, allowing us to view a client/spend spreadsheet filtered by whichever sales source we choose – or indeed multiple sales sources; as you can select more than one filter value.

Here is the same table – now much shorter, because we have filtered it to show Shop Visits only:

Amongst other things, this shows us that trade in the shop tends to be low-budget personal visitors – a useful thing to know! But by now, you’ll have figured that this can be made even clearer – by adding the “Type” field to the Column Labels box.

Yup- in the above example, we’ve used all four fields of our original table to extract clear, actionable business information easily and effectively; whilst never disturbing the original data. Pivot Tables quite simply deserve to be your Number One analysis tool.

 Under the magnifying glass...

There is one more essential function in Pivot Tables which is well worth knowing before you explore. In the above examples, we have exclusively focused on totals. Indeed, in many business cases, it’s totals which matter!

But suppose we want to spot longer term trends? Here’s the spend according to sales method on the X axis, and customer type on the Y axis.

If I click the down-arrow on ‘Sum of Spend’ in the Values box, and then select ‘Value Field Settings’ and then ‘Average’, I can display averages instead of totals (there are plenty more options – maxima, for example – on offer, too).

The target exposed

Find out more:

Get Excel now!

Video demo of Pivot Tables

Dummies Video: Charting Pivot Tables