I am working on a project this week that requires me to wait while information compiles. What makes this project unusual is that I cannot continue with anything else until this is done, so I find myself with down time as the computer thinks.
This week I had the opportunity to train several people on the use of the Pivot table in excel. This is perhaps one of the most underused tool in the excel program. I believe that it is underused because many are intimidated by and don’t understand how it works.
The key to using a Pivot table knows what you want to get out of it, in other words, what data are you looking to see out of the table. So it requires a small amount of planning and some imagination to have the table set up to your needs. Here is a run through of the example I used to demonstrate the Pivot table.
(Originally I wrote the text with visual aids, the visuals would not translate to HTML so I have to leave them out for now. I may re-run this article when I figure this out.)
A pivot table is a cross-tabulation-another way of looking at the data. You can reorganize the database, and turn it (literally) on its side and inside out. And you can do it in any number of ways.
For example, you can set up a pivot table that has Investment Objectives and Customers in the rows and a Quarter End Value in each column, and has the data for the Account Values inside the cells.
A different way to view the data can be seen by moving the Investment Objective to the Columns we now have the Investment Objective broken out by customer and by Date that will be totaled on the bottom of the table. This brings out the Objectives by Date a little more distinctly than having each customer grouped by Objective.
Excel provides a wizard to help you create this kind of table. Here’s how to use the wizard.
1. Select the Data menu and choose Pivot Table and PivotChart Report to open the Pivot Table and PivotChart Report Wizard
In this dialog box you tell the wizard the kind of data you want to analyze and the type of report you want to create.
2. Make your selections.
For this example, the default settings work just fine-the Microsoft Office Excel List or Database option in the upper area, and the Pivot Table option in the lower area.
Click Next to continue
3. Type the source of the data in the Range box.
The wizard may already select the range of cells based on your table. Just assure that all cells are covered. Better yet, name the tab you table is on and you will simply have to put the name of your table in the dialogue box.
4. Click Next to continue.
In the final dialog box, you indicate where you want the report. I chose the New Worksheet option to create a new page for the report. By using the New Worksheet you have the option to delete the entire table without affecting your data. You do this by deleting the worksheet.
5. Click Finish.
Because I chose New Worksheet in Step 4, a new page opens with the layout of the Pivot Table, along with the Pivot Table toolbar and the Pivot fields.
6. Fill in the Pivot Table Layout.
To populate the Pivot Table Layout, select a field from the Pivot Table Field List, drag it to the Layout, and drop it. Where you drop it, of course, determines its appearance in the Pivot Table.
I begin with the rows. To make the names of the Customer appear in the rows, I selected Name and dragged it into the area labeled Drop Row Fields Here. I also needed to drop the Market Value into the Data field. In addition to the Customer names in the rows, Name becomes bolded in the Field List to indicate it’s in the table, as does the Market Value.
You can also drop items into the Columns the same way as the rows. I added the Date field to my columns so I could display the Name-Market Values by date and have a total at the end of my table.
Notice that each row in begins with the words “Sum of,” indicating that each cell entry is a sum. From the database, you know that each entry is an individual number, not a sum. What’s the story? In this database, each Name-Market Value combination is unique. That’s why the numbers in this pivot table aren’t really sums.
Note the down arrow next to each field name. Clicking the down arrow opens a drop-down list that allows you to display some or all of that field’s data.
One more area is left on the layout-Drop Page Fields Here, at the top of the page. Dropping a field into that area turns the table into a kind of multi-page catalog. Each page shows just the data for an individual in that field. This would work great if I wanted the Name-Market Value paged out for each Customer Service Representative or Sales person.
The Pivot Table toolbar provides a number of tools that allow you to craft the table’s appearance. Table Options, for example, opens a dialog box you can use to remove the row totals and column totals. Formulas enable you to create new fields by performing calculations on existing fields.
If you select PivotChart, Excel creates a column graph of the table. You can reformat this chart the same way you reformat any other chart. You can even change the chart type, although I advise against it. In fact, Excel has some chart types it doesn’t allow you to use for Pivot Charts.
The importance of pivot tables is that they allow you to get your hands dirty with the data. By dropping fields into and out of the table, you might see relationships and carry out analyses that might be missed using normal analysis methods.
Filed under: Excel
