Excel PivotPoint Tables

This past week I attended a webinar program that demonstrated the use of Excel PivotPoint tables for analyzing information in large spreadsheet documents. As I sat through the program, I remember reaching a point where I said to myself, wow, this is really cool! And after the program ended and I had a chance to process the information while talking to some of my colleagues, they also expressed much the same sentiment about the usefulness of this component of the Excel program. I thought I would use this blog entry to simply describe what it does.

A Pivot Table is an excellent means to analyze data that is contained in a list. For example, imagine that you have a spreadsheet that contains the following information: Clothing Type (dresses, belts, pants, ties, shirts), Region (pacific, mountain, south, midwest, northeast), Sales (listed in dollars), Quarter (1,2,3,4) and Sales Type (Retail, Wholesale). And now imagine that you want to extract information from this table, which we will say contains 2400 entries. Say, you wish to look at the breakdown of wholesale versus retail sales, for each clothing type, broken down by region of the country. How can you easily do so? Well, a simple filter will not work, not with 2400 entries and with several different variables to consider. And even if you could, it would require a number of steps to get to the information, which would still be in columnar form and would require additional steps to provide the financial data. So, in a situation like this, a Pivot Table works perfectly, and has the added benefit of being able to be constructed in minutes. Not only that, but it is a simple matter to manipulate the data, which is why it is called a Pivot Table; one can pivot the axes in just two simple clicks of the mouse. Even better, a Pivot Table is something separate from the original data, so you can alter the table without affecting your original data source. It is in fact dependent on that original data.

There are a couple of prerequisites to set up a Pivot Table. You need to have a single title row across the top of your spreadsheet, with unique field headings. For the example I used just above, the headings would be as noted: Clothing Type, Region, Sales, Quarter, and Sales Type. And there can be no breaks in the data, i.e., you cannot have a missing line at line 1400, so no empty columns or rows. To make a table all you need to is make sure you have clicked on any cell within the spreadsheet; then, go to the Data menu at the top of the menu bar. Click on that, and use the pull down menu to go to the entry for “Pivot table and Pivot Chart Report…” This will open a dialogue box called the Pivot Table and Pivot Chart Wizard. Assuming you are working solely with Excel data, select the data you wish to analyze (here, it will be “Microsoft Office Excel list or database”) and then click “Next.” It will then ask you where the data is you wish to assess, and by default will enter the full spreadsheet range. Click “Next” again. It will then ask where you want to put the Pivot Table report. You can enter it either on the sheet you are working from, or have it open on its own sheet. Pick whichever you wish.

Once you do, you will be provided with a blank Pivot Table and a floating Pivot Table Field List (you use this for developing the table). From here, all you need to do is drag and drop. If you look at the blank table, you will see it has several components: on the left is a part that says “Drop Row Fields Here”, across the top is one saying “Drop Column Fields Here”; above that is one saying “Drop Page Fields Here,” and finally is the main box, which says “Drop Data Items here.” You would go to the floating box and simply drag the items you wish to analyze to the appropriate location- which depends on what you wish to analyze. For example, you could drag “Clothing Type” to the “Drop Row Fields Here” location and also drag “Region” to the “Drop Column Fields Here” location. As soon as you do, you would find that the table would tell you how many of each clothing type sold in each region of the country. And all done in seconds. And such tables can be made more complex by additional dropping; we could actually divide the clothing sales type into retail and wholesale with one simple additional drag and drop.

It is actually best to see this demonstrated. This link, to a website at Duke University, provides a very nice demonstration of how what I describe above is done. http://it.fuqua.duke.edu/public/2001PivotTableIntroduction.pdf

The beauty of this tool is the ease it offers for doing detailed data analysis. It is up to the creativity of the user to find the best ways in which this can be used in educational settings.