Microsoft Excel: Understanding Pivot Tables

Pivot tables can be a great time saver for presenting custom charts or tables that relate to a lot of data. In fact, Microsoft has made the process relatively easy and straightforward to set them up, but there are some tricks that can be employed after a table is constructed that can help the financial modeler develop an array of summary tables. Simply put, pivot tables allow you to display a variety of data in a summary table format in a process that takes only a few steps.

To begin, a financial modeler would merely click on PivotTable and PivotChart Report under the Data menu or PivotTable Wizard underneath the PivotTable menu found on the PivotTable toolbar (I normally do that latter because I have the toolbar open most of the time). In either case, Microsoft gives you a choice of where the data is located that you want in the pivot table analysis. To keep it simple, the default setting is likely to be the one you will use (Microsoft Office Excel list or database). The second step is to highlight the appropriate range of cells for inclusion in the analysis. You can either grab the database with mouse/keyboard, or you can define the range and just type in its name (it is important to note that creating a range with titles in the first row is a requirement to using pivot tables). Finally, you can place the resulting table in an existing sheet or specify a new sheet. For a basic pivot table, that is it. There is no real mystery or difficulty in setting this up, because, as stated earlier, Microsoft did a good job of making it easy to do.

Let us look at a situation where maybe there are two data points that require capturing. In a simple pivot table, you might want to know which companies sold the most widgets. You have the companies in the left-hand columns and in the right-hand column you have the total number sold (if you have a long list of repeating names in the database, pivot table automatically consolidate, one of the great things about it). But what if you wanted to know not only the sum of widgets sold, but the dollar value associated with them?

In your existing pivot table, right mouse click and then click on the wizard again. You will see a box called "Layout" that you will click. This is where you can drag multiple items into the "data" box of the pivot table, like number sold and total sales. After clicking the "Finish" button, you will now see a pivot table that has two pieces of information for each company. In addition, you can drag information into the column area and have data broken down by company name and, perhaps, day of the week I which the sales were made. This can be done by merely dragging and dropping – it doesn’t get much easier than that.

For those of you who like formulas and want to do some customer table building, there is a basic formula for use in conjunction with pivot table:

=GETPIVOTDATA(data_field, pivot_table, [field1], [item1], [field2], [item2],…).

In this instance, up to 14 field/item references can be used. The data_field refers to the sum or count of something, like number of widgets, and the pivot_table is, obviously, a reference to the pivot table you are using (is the top left corner of the pivot table and the cell should be absolute referenced, i.e., dollar signs). The field/item combinations refer to the label of the information you are seeking and then the specific search item. For example, if you had a pivot table with care sales by color, the field might be "cars" and the item might be "black" and, assuming your data_field was "sum of sales price" you would get the sum of all black cars sold that exists in your database.

This is meant to be an introduction to pivot tables. Honestly, I found out more about utilizing the various features (that would be another article) by just playing around with different format and data combinations. If you take a few hours just to break a pivot table down, understand the layout button and work with the GETPIVOTDATA formula, you will have a base understanding. Once you accomplish this, you will understand how a bunch of data in a spreadsheet easily can be broken down and conveyed in simple, yet effective, summary tables.