What is a Pivot Table?

I have trained too many Excel courses to count and a lot of people ask me what a pivot table is.  It is one of those features that either you know what it is or you don’t, the name does not give much away. Lets say that you have a list of clients and each order they have made.  It might look something like this:

Order	Client	Order Total	Order Date
1031	Sports Place	6320.95	1-Jul-09
1086	World of Sports	5800.98	1-Aug-09
1176	Sports Place	4555.37	2-Aug-09
1231	World of Sports	1205.5	2-Sep-09
1298	Sports Place	2690.95	3-Oct-09

You can see the clients but if you wanted to analyse the data a pivot table can do a lot easily.  Such as group the client names, so in the example above we have 3 Sports Place orders, a pivot table can merge them together into one row and add up all the totals so you can see the total amount ordered by the client.  It would look something like this:

Sum of Order Total
Client	Total
Sports Place	13,567.27
World of Sports	7,006.48
Grand Total	20,573.75

The information could be expanded to the following:

Sum of Order Total	Months
Client	Jul	Aug	Sep	Oct	Grand Total
Sports Place	6,320.95	4,555.37		2,690.95	13,567.27
World of Sports		5,800.98	1,205.50		7,006.48
Grand Total	6,320.95	10,356.35	1,205.50	2,690.95	20,573.75

The pivot table has 4 areas:

Row	Grouped data that populates down the rows, in this example the Client in the in Row area
Column	Grouped data that populates across the column headings, in the example the Months
Data	This is the calculation, in the example the Sum of the Order Total
Page	Will filter all the data, if you only wanted to see a particular year put the year into the page area

The list you use makes all the difference.  If you want to extract something (like the month) you need to have either a column with the month in or a date.

The integrity of your data also makes a difference.  If you misspell the Client name then Microsoft Excel will group the client names that match exactly, even if you have a difference of a space then they will not group together.

Try to create a pivot table using small amounts of data and have a goal of what you want to create, then try out more difficult pivot tables.



Get your Pivot Table Cheat Sheet now, go to http://www.katherinedavison.co.uk/products-page/microsoft-excel-2003/excel-2003-pivot-table-basics-cheat-sheet/