VIEW MORE SCREENCASTS   |   MISSINGMANUALS.COM HOME PAGE

	Click here to preview and order the book.

TRANSCRIPT

Hi, I'm Matthew MacDonald the author of Excel 2007: The Missing Manual and I'm going to talk to you about pivot tables.

Pivot tables are an often overlooked Excel feature for summarizing information. Although you can use a pivot table to analyze any amount of data they really shine when you need to chew through huge amounts of information like a year's worth of orders from an ecommerce company. Pivot tables are just one of Excel's many tools for data analysis.

In this screen cast we'll start with a table that lists over 2,000 orders. This is way too much information for a sensible person to sift through but if you summarize it with a pivot table you can find out what products are the top sellers, what countries generate the most business and a whole lot more.

You can also tease out hidden relationships. For example, you can find out the city where a specific product sells the best. To transform this information into a pivot table, first click on a cell somewhere inside the data then head to the insert tab and click the pivot table button.

At this point Excel selects all the data in your table, it tells you what it selected and it asks you were to put the new pivot table. The best option here is to choose "new worksheet." That way you can keep your pivot table separate from the actual information cutting down on worksheet crowding and it means you have less scrolling to do.

Initially a pivot table doesn't look like much. Excel creates a place holder box but it doesn't actually fill in any information. That's because you need to tell Excel how to analyze your data before it can create the pivot table.

The trick to creating a pivot table is the pivot table fieldless pane here in the right side of the window. It lists all the columns that are in your original table of data. These are all the columns you can use to create your pivot table but you don't need to use all of them. You tell Excel what to do with a column by dragging it down to one of the four regions underneath.

The first detail Excel needs to know is what piece of information you want to summarize. Usually this is a numeric piece of information like the number of orders that were placed, the number of units that were sold or the cost of each order. In this example we choose the quantity column which tells us how many items were sold.

Now Excel has enough information to create a pivot table but it's a really simple one. All this pivot table does is tell you how many items were ordered in total for a grand total of 51,317.

To get more value out of this pivot table you need to tell Excel how to subgroup your information. For example, maybe you want to know how many items were sold from each product category. To do this you'd drag the category column down into the row labels section. Now Excel creates a separate row for each category and it tells you the total units that it sold in each category.

Pivot tables are really easy to rearrange. In fact the name pivot table refers to the way that you can quickly pivot them to get a different vantage point on your data. For example, if you decide you don't want to see a summary of product categories but instead you want to see how many items were sold in each country, just drag the category column out of the row labels section and then drag the country column into the row labels section. Now Excel creates a completely new summary. This summary tells you, among other things, that customers from Argentina bought 339 items.

You can create more sophisticated summaries by using more than one column to group your data. For example, imagine you want to look at the country breakdown and the category breakdown. To do this you'd drag one column to the row labels area, I'm using the country here, and another to the columns label area. Here we're using category.

Now Excel does something very interesting. First it divides your results into rows based on the country just like it did before but now it subdivides each row into columns based on the category. Using this more detailed breakdown I can see that customers from Argentina bought 82 units of beverages but only 25 units of condiments. This is the starting point to a more sophisticated analysis. For example, I could determine what products do unnaturally well in particular markets. Maybe this could help me optimize marketing or identify a weak spot the company doesn't know about.

Happily you can still find the totals for each category and for each column. To get the country totals scroll to the end of the row. To get the category totals scroll to the bottom of the table.

Pivot tables can get much more detailed than this. Right now we have two levels of grouping, in other words, two ways the total information is broken down into category columns and country rows. However, for deeper analysis you can subdivide your columns into more columns or your rows into more rows.

For example, let's say you want to look at how individual cities compare when it comes to ordering products. You can subdivide the country grouping by city. All you need to do is drag the city column down so it's just under the country column in the row labels area. Now Excel splits each country row into city specific rows.

When you split rows or split columns by adding more than one piece of information to the row labels or columns labels sections, Excel gives you these nifty boxes. You can use them to collapse a section of your pivot table which temporarily hides some information out of the way.

For example, if I want to focus on all the orders in Brazil and Denmark I can simply collapse every other section in my pivot table. Now I see the country totals for each section and the detailed breakdown for Brazil and Denmark. And if I change my mind I can expand and collapse different groups to hone in on different information. Pivot tables are endlessly flexible this way.

There's a whole lot more you can do with pivot tables. You can change the way they look, using them to build chart, perform calculations and filter out just the data you're interested in.

If you'd like to start experimenting, download this workbook named Pivot Tests from the Missing CD page for Excel 2007: The Missing Manual or check out the detailed walkthrough in Excel 2007: The Missing Manual.