SEARCH

How-To Geek

Working with PivotTables in Excel

PivotTables are one of the most powerful features of Microsoft Excel.  They allow large amounts of data to be analyzed and summarized in just a few mouse clicks. In this article, we explore PivotTables, understand what they are, and learn how to create and customize them.

Note:  This article is written using Excel 2010 (Beta).  The concept of a PivotTable has changed little over the years, but the method of creating one has changed in nearly every iteration of Excel.  If you are using a version of Excel that is not 2010, expect different screens from the ones you see in this article.

A Little History

In the early days of spreadsheet programs, Lotus 1-2-3 ruled the roost.  Its dominance was so complete that people thought it was a waste of time for Microsoft to bother developing their own spreadsheet software (Excel) to compete with Lotus.  Flash-forward to 2010, and Excel’s dominance of the spreadsheet market is greater than Lotus’s ever was, while the number of users still running Lotus 1-2-3 is approaching zero.  How did this happen?  What caused such a dramatic reversal of fortunes?

Industry analysts put it down to two factors:  Firstly, Lotus decided that this fancy new GUI platform called “Windows” was a passing fad that would never take off.  They declined to create a Windows version of Lotus 1-2-3 (for a few years, anyway), predicting that their DOS version of the software was all anyone would ever need.  Microsoft, naturally, developed Excel exclusively for Windows.  Secondly, Microsoft developed a feature for Excel that Lotus didn’t provide in 1-2-3, namely PivotTables.  The PivotTables feature, exclusive to Excel, was deemed so staggeringly useful that people were willing to learn an entire new software package (Excel) rather than stick with a program (1-2-3) that didn’t have it.  This one feature, along with the misjudgment of the success of Windows, was the death-knell for Lotus 1-2-3, and the beginning of the success of Microsoft Excel.

Understanding PivotTables

So what is a PivotTable, exactly?

Put simply, a PivotTable is a summary of some data, created to allow easy analysis of said data.  But unlike a manually created summary, Excel PivotTables are interactive.  Once you have created one, you can easily change it if it doesn’t offer the exact insights into your data that you were hoping for.  In a couple of clicks the summary can be “pivoted” – rotated in such a way that the column headings become row headings, and vice versa.  There’s a lot more that can be done, too.  Rather than try to describe all the features of PivotTables, we’ll simply demonstrate them…

The data that you analyze using a PivotTable can’t be just any data – it has to be raw data, previously unprocessed (unsummarized) – typically a list of some sort.  An example of this might be the list of sales transactions in a company for the past six months.

Examine the data shown below:

Sample monthly sales data

Notice that this is not raw data.  In fact, it is already a summary of some sort.  In cell B3 we can see $30,000, which apparently is the total of James Cook’s sales for the month of January.  So where is the raw data?  How did we arrive at the figure of $30,000?  Where is the original list of sales transactions that this figure was generated from?  It’s clear that somewhere, someone must have gone to the trouble of collating all of the sales transactions for the past six months into the summary we see above.  How long do you suppose this took?  An hour?  Ten?

Most probably, yes.  You see, the spreadsheet above is actually not a PivotTable.  It was created manually from raw data stored elsewhere, and it did indeed take a couple of hours to compile.  However, it’s exactly the sort of summary that could be created using PivotTables, in which case it would have taken just a few seconds.  Let’s find out how…

If we were to track down the original list of sales transactions, it might look something like this:

Raw data

You may be surprised to learn that, using the PivotTable feature of Excel, we can create a monthly sales summary similar to the one above in a few seconds, with only a few mouse clicks.  We can do this – and a lot more too!

How to Create a PivotTable

First, ensure that you have some raw data in a worksheet in Excel.  A list of financial transactions is typical, but it can be a list of just about anything:  Employee contact details, your CD collection, or fuel consumption figures for your company’s fleet of cars.

So we start Excel…

Excel 2010 starting

…and we load such a list…

Raw data

Once we have the list open in Excel, we’re ready to start creating the PivotTable.

Click on any one single cell within the list:

Raw data - selected

Then, from the Insert tab, click the PivotTable icon:

Insert/PivotTable

The Create PivotTable box appears, asking you two questions:  What data should your new PivotTable be based on, and where should it be created?  Because we already clicked on a cell within the list (in the step above), the entire list surrounding that cell is already selected for us ($A$1:$G$88 on the Payments sheet, in this example).  Note that we could select a list in any other region of any other worksheet, or even some external data source, such as an Access database table, or even a MS-SQL Server database table.  We also need to select whether we want our new PivotTable to be created on a new worksheet, or on an existing one.  In this example we will select a new one:

Create PivotTable dialog

The new worksheet is created for us, and a blank PivotTable is created on that worksheet:

PivotTable Area

Another box also appears:  The PivotTable Field List.  This field list will be shown whenever we click on any cell within the PivotTable (above):

PivotTable fields

The list of fields in the top part of the box is actually the collection of column headings from the original raw data worksheet.  The four blank boxes in the lower part of the screen allow us to choose the way we would like our PivotTable to summarize the raw data.  So far, there is nothing in those boxes, so the PivotTable is blank.  All we need to do is drag fields down from the list above and drop them in the lower boxes.  A PivotTable is then automatically created to match our instructions.  If we get it wrong, we only need to drag the fields back to where they came from and/or drag new fields down to replace them.

The Values box is arguably the most important of the four.  The field that is dragged into this box represents the data that needs to be summarized in some way (by summing, averaging, finding the maximum, minimum, etc).  It is almost always numerical data.  A perfect candidate for this box in our sample data is the “Amount” field/column.  Let’s drag that field into the Values box:

amountonly

Notice that (a) the “Amount” field in the list of fields is now ticked, and “Sum of Amount” has been added to the Values box, indicating that the amount column has been summed.

If we examine the PivotTable itself, we indeed find the sum of all the “Amount” values from the raw data worksheet:

amountonlypivot

We’ve created our first PivotTable!  Handy, but not particularly impressive.  It’s likely that we need a little more insight into our data than that.

Referring to our sample data, we need to identify one or more column headings that we could conceivably use to split this total.  For example, we may decide that we would like to see a summary of our data where we have a row heading for each of the different salespersons in our company, and a total for each.  To achieve this, all we need to do is to drag the “Salesperson” field into the Row Labels box:

salespersonrow

Now, finally, things start to get interesting!  Our PivotTable starts to take shape….

rows1 

With a couple of clicks we have created a table that would have taken a long time to do manually.

So what else can we do?  Well, in one sense our PivotTable is complete.  We’ve created a useful summary of our source data.  The important stuff is already learned!  For the rest of the article, we will examine some ways that more complex PivotTables can be created, and ways that those PivotTables can be customized.

First, we can create a two-dimensional table.  Let’s do that by using “Payment Method” as a column heading.  Simply drag the “Payment Method” heading to the Column Labels box:

2dim1

Which looks like this:

2dim1data

Starting to get very cool!

Let’s make it a three-dimensional table.  What could such a table possibly look like?  Well, let’s see…

Drag the “Package” column/heading to the Report Filter box:

3dim1

Notice where it ends up….

3dim1data

This allows us to filter our report based on which “holiday package” was being purchased.  For example, we can see the breakdown of salesperson vs payment method for all packages, or, with a couple of clicks, change it to show the same breakdown for the “Sunseekers” package:

3dim1datafiltered

And so, if you think about it the right way, our PivotTable is now three-dimensional.  Let’s keep customizing…

If it turns out, say, that we only want to see cheque and credit card transactions (i.e. no cash transactions), then we can deselect the “Cash” item from the column headings.  Click the drop-down arrow next to Column Labels, and untick “Cash”:

nocash

Let’s see what that looks like…As you can see, “Cash” is gone.

nocashdata

Formatting

This is obviously a very powerful system, but so far the results look very plain and boring.  For a start, the numbers that we’re summing do not look like dollar amounts – just plain old numbers.  Let’s rectify that.

A temptation might be to do what we’re used to doing in such circumstances and simply select the whole table (or the whole worksheet) and use the standard number formatting buttons on the toolbar to complete the formatting.  The problem with that approach is that if you ever change the structure of the PivotTable in the future (which is 99% likely), then those number formats will be lost.  We need a way that will make them (semi-)permanent.

First, we locate the “Sum of Amount” entry in the Values box, and click on it.  A menu appears.  We select Value Field Settings… from the menu:

dollar1

The Value Field Settings box appears.

dollar2

Click the Number Format button, and the standard Format Cells box appears:

dollar3

From the Category list, select (say) Accounting, and drop the number of decimal places to 0.  Click OK a few times to get back to the PivotTable…

dollar4

As you can see, the numbers have been correctly formatted as dollar amounts.

While we’re on the subject of formatting, let’s format the entire PivotTable.  There are a few ways to do this.  Let’s use a simple one…

Click the PivotTable Tools/Design tab:

PivotTableDesign

Then drop down the arrow in the bottom-right of the PivotTable Styles list to see a vast collection of built-in styles:

styles

Choose any one that appeals, and look at the result in your PivotTable:

formatted 

Other Options

We can work with dates as well.  Now usually, there are many, many dates in a transaction list such as the one we started with.  But Excel provides the option to group data items together by day, week, month, year, etc.  Let’s see how this is done.

First, let’s remove the “Payment Method” column from the Column Labels box (simply drag it back up to the field list), and replace it with the “Date Booked” column:

date1

As you can see, this makes our PivotTable instantly useless, giving us one column for each date that a transaction occurred on – a very wide table!

date2

To fix this, right-click on any date and select Group… from the context-menu:

date3

The grouping box appears.  We select Months and click OK:

date4

Voila!  A much more useful table:

date5

(Incidentally, this table is virtually identical to the one shown at the beginning of this article – the original sales summary that was created manually.)

Another cool thing to be aware of is that you can have more than one set of row headings (or column headings):

2row

…which looks like this….

2rowdata

You can do a similar thing with column headings (or even report filters).

Keeping things simple again, let’s see how to plot averaged values, rather than summed values.

First, click on “Sum of Amount”, and select Value Field Settings… from the context-menu that appears:

dollar1

In the Summarize value field by list in the Value Field Settings box, select Average:

average1

While we’re here, let’s change the Custom Name, from “Average of Amount” to something a little more concise.  Type in something like “Avg”:

average2

Click OK, and see what it looks like.  Notice that all the values change from summed totals to averages, and the table title (top-left cell) has changed to “Avg”:

average3

If we like, we can even have sums, averages and counts (counts = how many sales there were) all on the same PivotTable!

Here are the steps to get something like that in place (starting from a blank PivotTable):

  1. Drag “Salesperson” into the Column Labels
  2. Drag “Amount” field down into the Values box three times
  3. For the first “Amount” field, change its custom name to “Total” and it’s number format to Accounting (0 decimal places)
  4. For the second “Amount” field, change its custom name to “Average”, its function to Average and it’s number format to Accounting (0 decimal places)
  5. For the third “Amount” field, change its name to “Count” and its function to Count
  6. Drag the automatically created sigmavalues field from Column Labels to Row Labels

Here’s what we end up with:

combo

Total, average and count on the same PivotTable!

Conclusion

There are many, many more features and options for PivotTables created by Microsoft Excel – far too many to list in an article like this.  To fully cover the potential of PivotTables, a small book (or a large website) would be required.  Brave and/or geeky readers can explore PivotTables further quite easily:  Simply right-click on just about everything, and see what options become available to you.  There are also the two ribbon-tabs: PivotTable Tools/Options and Design.  It doesn’t matter if you make a mistake – it’s easy to delete the PivotTable and start again – a possibility old DOS users of Lotus 1-2-3 never had.

If you’re working in Office 2007, you might want to check out our article on how to create a PivotTable in Excel 2007.

We’ve included an Excel workbook that you can download to practice your PivotTable skills on.  It should work with all versions of Excel from 97 onwards.

Download Our Practice Excel Workbook

Web2.0 programmer by day, singer by night, Aussie geek Mark Virtue keeps the How-To Geek flag flying Down Under.

  • Published 03/22/10

Comments (13)

  1. Chris

    Fine article, just one small correction. In the early days, Microsoft’s first spreadsheet product was not Excel, it was called Multiplan. This was pre Windows and I still use it from a command prompt under XP.

  2. Alec S.

    Thanks for the tutorial. I have been trying to figure out what the heck pivot tables are for quite some time now and never could because the explanations (eg Wikipedia entry, etc.) were always just useless descriptions instead of pictures (video would be even better!) Of course I could understand them best if I had actual cause to use them, but then again, I could say that for everything I want to learn… :D

  3. Bob

    Roll on PowerPivot!

  4. Robert in SF

    I wish there was some succinct way to explain how to think of your data question to help design the right pivot table for the answer…

    I have a hard time deciding if it’s a data field, or the row or column field I need to drop the field on.

    Something like, ” I want to see the ‘calculation’ of the ‘data field” for each ‘row field’ by ‘column field”….

    It could be translated as “I want to to see the total sales for each salesperson by month”

    I would get turned around with dealing with multiple groupings, such as sales totals per salesperson, per product, by month.

    When there is an example in front of me it makes sense, but if I have my own data set, I get paralyzed.

    Any ideas?

  5. Don

    PivotTables weren’t exclusive to Excel. Lotus Improv had them, and had them first.

  6. David Levine

    Pivot tables are great for data analysis. I have been using them for years ever since I learned about them. Great article!

  7. 216

    btw, your method for editing Pivot Tables works the same in Office 2007 as well

  8. Bryn_OfficeTeam

    Thank you for posting this great tutorial on working with pivot tables! Pivot tables can be complex to work with and this guide will be helpful for many Excel users!

    You should share your tips and tricks with the community over on Facebook: http://www.facebook.com/microsoftexcel

    Cheers,
    Bryn
    MSFT Office Outreach Team

  9. Santo

    Excellent article. This is something that I have been waiting for. Please give more tutorials about Excel and its advanced features.
    Keep up your good work.

  10. Alf

    Okay, looked at using pivot tables but nowhere do I see if the format can be saved. Does one need to go through all that each time? Considering there is no type ahead (autocomplete) incorporated in many Microsoft products this may not be a stupid question! (type ahead autocomplete technology has only been around since day 1 and the brains at MS have not twigged to it!) How pathetic!

  11. Jakeu1701

    I see where you changed Row Labels to Values, but is there a way to have it show the labels so it shows Salesperson for the first names and Date Booked over the months? Basically, similar to what it showed in Excel 2003?

    We use several forms and Y/N columns that having the header in our pivot tables would make things easier to read/understand. I know there is the “Classic PivotTable Layout” option, but I would prefer keeping with the new format, just with the labels. Any suggestions?

  12. Forrest

    Hey – excel-lent summary of pivot tables – they certainly are by far the most powerful aspects of Excel I can think of.

    I do have one question that no amount of Googling has uncovered for me, however….

    On the Value Fields Settings –> Summarize Value Field By drop-down list – you only have a few options – Sum, Count, Average,etc.

    Is there any way of adding other functions to that list? I’d like to do three pivot charts that show the Mean (Average), Median and Mode of a data set – but I need the Median and Mode functions in here… it would make one bit of analysis I do on a regular basis SO much easier…

    Ideas anyone?

  13. AT--o

    Good Tutorial

Enter Your Email Here to Get Access for Free:

Go check your email!