Pivot tables - more exciting than the 100m sprint. Really.

Off the top of your head, right now, what are the top 5 burning questions you have on Track & Field performance in the last 3 Olympic summer games?

While you ponder that, here are mine....

1) How many medals are there to be gained in these categories?
2) Which countries have won the most Gold medals in each of the last 3 years?
3) Is there a country that is always a bridesmaid (ie - always the silver, never the gold)?
4)How do women fare by country? Is there a significant change in the top 3?
5)Which year had the highest number of countries winning medals?

Your first question should now be, "How are you going to find all that out?". And my answer shall be, "With the most effective data analyser Excel has to offer - the pivot table".

I know a lot of people who would prefer to wash (by hand) the USA's Olympic team's training kit, than use a pivot table. Trust me, a basic understanding of pivot tables is all that is needed to make you ditch the sweaty socks, and embrace the beauty of the table. So here it is...a taster course in basic pivot tables.

In brief, a pivot table looks at a big clump of data, and acts like Tom Cruise in Minority Report. You know that scene where he stands in front of the big clear screen and pulls images from left and right onto the screen, to come up the bigger picture? Well, that's what a pivot table does. You select how you want to look at your data, which parts you want summarised, if you want it added / counted / averaged, and you press ok. You'll be rewarded with a better overview of your data. Just try look less tense than Tom Cruise.

That's the important part, to not be tense. Because pivot tables were made to be played with. Don't be scared to break it, you can't really. You'll just change the look of it. You can change it back or start again, it is not going to affect the underlying data.

Speaking of which, here is our data (not all of it, I don't have the space!). I downloaded some stats from www.databaseolympics.com, and manipulated them to come up with this table:

This continues down to row 302, and covers 2008, 2004 and 2000.  Something to note - I have a heading over each column. This is essential, otherwise your pivot table is not going to work.

So, to create a basic pivot table, you select your data (including headings) and go to Insert - Pivot Table or press ALT D, P. A dialog box will pop up to take you through creating your pivot table.
 - In Step 1, select Microsoft Excel list or database, and PivotTable.
 - In Step 2, just confirm you have the right data
 - In Step 3, select where you want your pivot table, and click finish.

You will now get a little blank image on your sheet with pivot table written in it, and a Field List box on your right. If this Field List box doesn't appear when you click on the pivot table image, go to PivotTable Tools - Options - Field List, and make sure it's highlighted.

To create a simple summary of medals won by year, event and gender, you need to click and drag the following items in the Field List to the areas below as shown here:

Note that under Values, we have Sum of Medal. If you wanted to change that to count or average or Minimum, we could click on the downward arrow, select Field Value Settings, and select how we wanted to treat the data.

Also, you may want to change the look and design of your table. Go to the PivotTable Tools - Design tab. From here you can change your table layout (Report Layout - I always prefer Tabular), the look of your table (PivotTable Styles - just scroll down the choices until you find one you like) and whether you want sub totals and totals. Excel will automatically add them.

So here is what our pivot table looks like:

That answers our first question - how many medals per category? But how do we see which country has won the most Gold medals, and if there is always a runner up? We act like Tom Cruise...go to the Field List box, and drag and drop to your hearts content to manipulate the table into various formats.

Here is our answer for questions 2 and 3. After the USA, Russia is always chasing those Gold medals. I created this table by:
 1) Adding the medals and countries as row labels, and years as a column label.
 2) I then clicked into the Grand Total column, right clicked, and selected Sort - Sort Largest to Smallest
 3) Then I narrowed the field down to the top 3 by right clicking on the Country header, selecting Filter - Top 10, and changed it to the Top 3
4) And I selected only the Gold & Silver Medal types from the drop down box.

To answer question four, we are going to drag Gender into the Report Filter area and remove the Medal item from the field list by just dragging it out. Once you have done that, select Women from the drop down box in the Report Filter:

And look at that...Russia has taken the lead.

To answer question five, we actually get rid of the Medals won item completely, and drag the Country item into the Value field. As this is a text field, Excel automatically counts it. Here is the answer:

Now to show you a really cool trick...What happens if you want all the underlying data that comprises the 99 total for 2008? Just double click on the 99, and Excel will create a full table of that data in a new worksheet for you. It will look something like this (again, I haven't included the full table):

This is incredibly handy if you need to zero in on some specific result from your table, and provide the back up data.
As you can see, there are so many ways to use pivot tables, and I have only scratched the surface here. But don't be afraid to give them a bash... they really are fun and so useful once you get the hang of them.

A new tool which Microsoft has added in Excel 2010 is the Slicer, which is brilliant if you want to create an interactive pivot table. I'll be covering this in my newsletter, so if you want to learn more, sign up here.

Enjoy the Olympics!

How do I sum thee? Let me count the ways.....

Do you know what I really love about Excel? The fact that everyone uses it differently. There are so many ways to reach the same answer, and people will use the techniques that they have been taught, or find the easiest.

Take the simple task of adding totals and subtotals to data, for example. I had a debate with my better half the other day about this very subject. He went for  the subtotal function. I went for pivots. It wasn't too heated (because hopefully we aren't that sad yet!), but it made me think about all the ways you could do it.

So here are some examples of how to get sub totals and totals for data with different categories (and bear in mind these are not all of the ways to do it... but we don't have all day!). Please note too that I am going to ignore the most simple and obvious choice of the sum function. Pure snobbery, but we have to have standards...

Our test data for today is riveting...unit sales on widgets, sprockets and gadgets:



Subtotal Formula Function
If we side with my better half, we can get subtotals for each product by doing the following:

 - Select all the data, and sort it by Product.
 - Insert lines between each product type
 - Add the sales for each product grouping by using the subtotal formula function as follows:

Things to note on this method:
 - When typing the Subtotal formula, note that the 9 (or you can use 109) refers to 'adding'. If you type in" =subtotal(" into your spreadsheet, you will notice a little drop down box appears giving you all sorts of lovely options, like average, max etc. The number you type in directly after the open brackets will determine what magic your formula will perfom.

 - The formula in the total line at the bottom will ignore all the subtotals above it. Handy.

Subtotals with grouping

An easier way to do this is click on the data, go to your data tab, and select the Subtotal button. You can then select what type of formula function you want, for which columns and hey presto, you have the same as above. You also get little brackets along the left side of your sheet that you can open and close for ease of viewing the totals, like below:

SUMIF Function

Has anyone seen the issue with this method yet? Well, one of the issues anyway...What happens if we want totals by colour too? You have to rearrange everything. Or, you can do this:

By using the SUMIF function, you can create sub totals by product or by colour, without having to touch the data. I've shown the formula above for clarity, but what it is basically saying is "Look in column B for any cells that match "Widget". Where they do, add up all the corresponding figures in column D". You can then drag this formula across to get the totals for all your products and colours.

The plus side of this is that you don't have to rearrange your data to get totals for different categories, and it is easier to add more data.

Pivot Tables

In my mind, this is still the cleanest option. By using a pivot table to get subtotals and totals, you can cut and slice your data however you choose. A basic pivot of the data would look like this:

By using the Pivot Table Field List, you can then choose whatever options you like for your pivot table by mixing it up.

For example, if you wanted these totalled by colour, simply swop the positions of Product and Colour in the Row Labels box.

This is a basic Pivot Table, because they deserve a whole blog on their own. But if you would like to see how I would mix this data up even more in a pivot, why not subscribe to our newsletter?

At the end of the day, you may say To-may-to and I may say To-mah-to, but we'll both be happy with our way of doing things. I just hope this has shown you a couple of other ways to try add it all up....

Thanks for reading, and happy spreadsheeting!