A simple, practical and non-scary example of how Excel PivotTables can work for you.
Excel’s PivotTables are a great feature but one that seems way too hard for most of us … so here’s a simple and practical example of how PivotTables can help.
A little fear of PivotTables is understandable. It’s one of those Office features that made promises which the first versions could not deliver. You may have tried PivotTables in the past and struck all manner of hassles. Eventually Microsoft got the bugs out of PivotTables and made them a lot easier to use. So we’d urge you to give them another try.
We’re using Excel 2007 in this article but the same principles apply to later versions of Excel (Excel 2013 has a nice touch we’ll mention below). Excel 2003 and before also have PivotTables in some form.
Grouping, count-ing and SUM-ing
A good use of PivotTables is to summarize a list of information. Get a count of matching items or add up sub-totals.
You could do these things laboriously with manually created COUNTIF’s and SUMIF’s but PivotTables will do it for you in a fraction of the time and automatically update if the source information changes.
Call History Analysis
For this PivotTable example we’re going to show you something that many people might do.
Choosing a mobile phone carrier or plan can be difficult but it starts with a look at your current call history. How many calls are you making?, How much time are you taking? Which phone numbers are called the most and for how long? How many calls are diverted to voicemail and what’s that costing you? Knowing those things will help you pick the best value plan for your needs. Some plans have a cheaper or free rate for calls to specific numbers you nominate or calls to mobiles on the same network.
So we’re going to take a sample call history and use PivotTables to give us the answers we need.
This isn’t a theoretical example, it’s a task Peter just did for some friends. We’ve changed the call details to protect their privacy, that’s all.
Normalizing data
The call history should be available from the phone carriers web site. Most will have a web page listing the call details that you can select and copy that list into Excel so it looks a little like this:
Now we need to make sure the data is ready for the PivotTable. Data geeks call that ‘normalizing’ and us humans might call ‘fixing up’.
PivotTables look at your data and try to figure out what to do based on the fields. Usually it guesses correctly but sometimes not. Some PivotTable confusion can be fixed just by checking the source data before you start.
In this case the ‘Destination’ (number called) column has digits which Excel thinks are numbers … of course they are phone numbers that make no sense if you try to add them up (which Excel will try to do). To prevent that select the B column and choose Home | Number | Text to force Excel to consider those cells as text labels. We’ve made the list a little easier to read by replacing some known phone numbers to labels (e.g. ‘Leo’s pager’).
The Duration column has been converted automatically into times (the colon between the digits did the trick) and that’s good since Excel can do time addition easily. (e.g. 0:30 + 0:30 = 1:00 ). But we’ve changed to a specific time format so the value will look sensible throughout.
We’ll ignore the Date & Time, Type and Cost columns for this example.
Finally a few small touches to make the source list easier to read and work with. Added data columns (Data | Filter), formatted the headings (Changed ‘Destination’ to ‘Number Called’) and moved the irrelevant ‘Date & Time’ column to the right so the two columns we’ll be using are on the left. None of these are necessary, just Excel niceties.
Making a PivotTable
Select the A and B columns and choose Insert | PivotTable. As you can see, Excel has used your selection as the PivotTable data and will place the table in a new worksheet/tab by default.
In Excel 2013 there’s a nice new feature ‘Recommended PivotTables’ which looks at the cells and suggests PivotTables to use. This does a pretty good job IF you’ve normalized the data (especially where text labels can be mistaken for number values). But we’re using Excel 2007 in this article so we don’t have that helper.
In the new worksheet is a blank PivotTable with the data columns ready to use.
This is where many people get confused and we don’t blame you. The whole thing is very scary looking and complicated (the Recommendations feature in Excel 2013 should do a lot to make PivotTables more accessible).
First, select ‘Number Called’ from the Fields list since that’s the column we want on the left.
As you can see, Excel has listed the unique values only with no duplicates – that’s part of the PivotTable magic.
To get a count of the phone calls to each number, click on the Duration field and drag it down to the Values box. Excel will guess that you want a Count of the matching cells and give you this.
Now we have the number of calls made to each number.
To get the total duration of calls to each number, drag the Duration field down to the Value box again. This will give you another Count column (Excel’s not super-smart). To change the calculation, click on the down arrow/wedge on the right of the Value entry and choose Value field settings.
Now change the Summarize value field by from ‘Count’ to ‘Sum’.
And there you have it. 275 minutes of calls, over a quarter in duration to a single number. Many calls to voicemail (101). Just those nuggets of information are enough to help choose a better mobile phone plan (one with at least, say, 300 minutes a month, free calls to Donna’s mobile and voicemail).
Refresh
A PivotTable is usually ‘linked’ meaning that a change in the source data will be reflected in the PivotTable when you click Refresh to update it.
In this example you could add more information (a new or past months calls) to the Call History worksheet. Or replace the call history with someone else’s details instead. The PivotTable will update when you press PivotTable | Options | Refresh.
Tidying up
The sharp eyed will have noticed some little glitches in the PivotTable plus things to improve.
There’s a ‘Total’ entry on the PivotTable (row 24 above) because the source columns also have a total. There are a few ways to fix this. One option is to remove the Total line from the source list then click Refresh at PivotTable | Options to update the list.
The (blank) row on the PivotTable is because we chose entire columns from the source worksheet not a specific range. To remove that row you could specify a narrower source range (e.g. A2:B101) or, more easily, remove (blank) from the PivotTable display. Click on the Row Label heading then deselect (blank) from the filters list. However there’s a risk to this approach if you change the source data.
Aside from that we added some more columns to show a % of the call duration used by each number and overall average call duration for each number. Do that by repeating the steps to add the ‘Sum of Duration’ column (see above) but change the ‘Summarize By’ or ‘Show Values’ as tabs to suit.
Sort the PivotTable by the number of calls (click on a heading and choose the sort order or there are Sort options on the PivotTable | Options ribbon).
Add some Excel supplied formatting and you’re done.
Give PivotTables a try
Now you’ve seen the basics, give PivotTables a try. The Undo button is your friend when things start looking strange (they sometimes do with PivotTables, so don’t panic … Undo).
Try it with some data of your own. Any time you have a list of entries with many duplicates, there’s a place for PivotTables. A small business could start with a list of sales (date, product, price sold etc).
Remember to ‘normalize’ or tidy up the data before you begin.
PivotTables Example
To give you a ‘leg up’ here is the sample worksheet we created to write this article. That will give you some sample data and sample PivotTable to work from.
The worksheet is PivotTable example.xlsx made in Excel 2007. Naturally any later version of Excel will open it. We suggest you right-click on the link and ‘Save as’ to your computer. Simply clicking on the worksheet link may open it up in an unexpected, different default app (like Google Docs or Office Online).
The legalistic world we live in means we have to say that the worksheet is provided ‘as is’ with no guarantees or warranties. It’s intended only as a sample for people to use in their own exploration of Excel’s PivotTables. Naturally, the file has been checked for viruses etc. and .xlsx files cannot contain working VBA code. As with any file you receive, you should rely on your own checks before opening it. We regret that we can’t provide additional or individual support .
See Also
- Why PivotTables get confused and how to fix them
- PivotTables: selecting and changing data sources
- Putting Health Data into Excel
- Data Mining ‘fun’ coming soon
- Office 2013: the real startup guide
