Guest Author - Maria Elizabeth Romana
Excel Pivot Tables provide a tool to summarize, organize, and analyze complex and repetitive data. By using pivot tables, you can make flexible reports that allow users to easily pull just the views they need without requiring a brand new report for every different layout or subset.
Once you lay out the data as you want it, you can drag 'n drop the columns and rows in your summary table to get just the look and feel you want, or alternatively, you can drag 'n drop in the table designer.
In order to create a pivot table, you need to have your data in a single spreadsheet, laid out with one row per data record and a row of unique column headers at the top, like salesman, region, month, product, units sold. There can be no blank rows or columns in the table, but you can have individual items missing.
To create the basic pivot table, simply highlight the entire data table, then select Insert | Pivot Table from the menus. Choose "new sheet" as the destination, and you will be taken to the new sheet with the pivot table designer displayed. At this point, you will see a list of those column headers available to be dragged 'n dropped into the pivot table, as either page filters, column or row headers, or individual data items to be counted, summed, averaged, etc.
Once you decide on a basic layout for your summarized data, any changes you make to the original table will automatically be reflected in the pivot table with a simple right-click and "refresh" on the table (or use the pivot table menu's refresh). This is very handy for what-if scenarios or constantly changing source data.
With your basic layout selected, you can format the items in your pivot table by drawing on the "Pivot Table" design menus. If these menus aren't showing in the Excel menus, be sure to go to the pivot table page and click somewhere in the pivot table. This will make the two menus under "Pivot Table Tools" appear: Options and Design. Design is primarily about making the table look "pretty"; it offers a plethora of color and striping options, as well as subtotal and grand totalling options. The Options menu, on the other hand, is where the real formatting power is.
Under the Options menu and over toward the right, you will see the Field List button. If you click this, you'll get your original layout manager back. Within the layout manager, you can click on the down arrow next to any table value element and then choose "Value Field Settings". This allows you to manipulate that particular element in terms of the calculation used (sum, count, average, etc.) and the numeric format used to display it.
If you want to create a new, calculated field from some of your existing data, choose "formulas" from the Options menu, and then "Calculated Field". This allows you to create a difference or ratio or other combination of two or more existing fields in your table, for example, the percentage change in sales from one month to the next. After you define that calculated field, you can place it and format in the table just like any of the source data fields.
This has been a very brief overview of the power of Excel pivot tables. The important thing to remember is that although it can be time-consuming to get just what you want, you can reuse the table design by simply replacing the source data, and refreshing the pivot table. This makes the table design effort pay off in the long run.