I will have a series of postings on a few of the powerful features of Microsoft Office that everyone should learn once they get past the basics of these applications. They are part of my consultant’s toolkit because they both

*add value*and

*save the client time and money*.

The capabilities I will share are not “rarely applicable” or designed to “show-off your skills”. They are frequently useful, meaningful, and beneficial. I list a few here so you get an idea of the features I am referring to:

- Pivot tables in Excel
- Style sheets in Word
- Tables in PowerPoint

This posting introduces you to pivot tables in Microsoft Excel by:

- Providing a simple real-life example of a pivot table and its benefits
- Presenting an overview of the key concepts of pivot tables
- Sharing several real-life examples to give you additional ideas on where to apply it in your work
- Sharing some practical tips on using pivot tables
- Listing additional resources to learn more about pivot tables.

**Note**: The pivot tables depicted in this posting are based on real-life uses. The data values (e.g., numbers, names, amounts) are fictitious, however, to remove any proprietary data.

SIDEBAR - AN EXAMPLE OF WHY TO LEARN NEW FEATURESA client sent me an Excel file with a long spreadsheet filled with sales data. He said he spent a full day each month formatting the information. Specifically, he spent time inserting new rows in the spreadsheet to put in column headings each time a page break occurred. I asked him if the “Rows to repeat at top” feature in Page Setup would work for him. He was not familiar with it. I showed him the feature and the few clicks needed to use it. If someone had not showed him this built-in capability of Excel, he would still be doing this work at considerable expense (eight hours versus one minute). This is independent of whether the formatting task itself added value to the company and could have been eliminated altogether. I asked him what else he did with the sales data to see if I could help him further. He mentioned he spent a couple of days each month computing sales by salesman, sales by customer, sales by customer grouped by salesman, and a few other views on the data. I asked him if he was familiar with pivot tables. He was not. Using an export from the sales system I showed him with pivot tables how he could create his reports each month in a couple of hours and with less likelihood of error. Bottomline: Three days each month was reduced to a less than half a day by learning and leveraging just two of the features of the tool he used. |

**REAL-LIFE EXAMPLE OF A PIVOT TABLE AND ITS BENEFITS**

One typical activity of a project manager is to track expenses on a project. For large projects, a project management tool or an accounting system may be used to track these expenses. For smaller projects or when you want more control over what and when you track expenses, a spreadsheet may be a good choice.

Here is a small portion of a spreadsheet I have used track expenses for multiple programs with multiple projects.

(Click image to enlarge) |

A project manager with this information can now ask and receive answers to multiple questions:

- How much have we spent to date? By month? What is our trend?
- How much have we spent on each project? On each program which has multiple projects?
- How much have we spent on consultants? On software?
- How much have we spent on each phase?
- Hold it…how much have we spent each month by phase and by project?

How much spent to date on all expenses tracked? One mouse drag gives the answer.

(Click image to enlarge) |

(Click image to enlarge) |

(Click image to enlarge) |

(Click image to enlarge) |

First, pivot tables can answer many questions important to people who use data to make decisions. By viewing the data in multiple ways the user can grasp a large amount of detailed data more easily, spot potential issues or trends, or find out more about an existing issue. Pivot tables allow this viewing of the same data in multiple ways.

Second, it is easy to change the way data is viewed. Once the data is set up in a spreadsheet and a pivot table structure has been created (fairly straightforward using Microsoft Excel’s built-in PivotTable Wizard), it literally is a mouse drag or mouse click to change the view of the data.

Third, the computations in pivot tables are done automatically. When you change the view, the calculations are handled for you. You do not need to write formulas.

Alternatives to pivot tables include writing formulas, using the subtotal feature of Excel, using filters on the data, or writing macros / purchasing add-ons to help redisplay or analyze the data. There may be cases where these alternatives are more appropriate or provide a view pivot tables do not support (there are some limitations to pivot tables.) But these alternatives tend to be more error-prone.

**KEY CONCEPTS OF PIVOT TABLES**

Three key concepts are the data table, the pivot table, and crosstabs.

**Data Table**

A

*data table*stores the data to be viewed, analyzed, and summarized. The top row of the data table contains the column headings. The rows below the top row contain the data with each row representing one record of data. Each cell on a data row contains a data value for that record.

(Click image to enlarge) |

The key design aspect of your data table is to determine how you may want to view your data. By month? By day? By region? By city? By product? By product grouping? By ship date? By month? A column needs to be set up and the data needs to be stored in the table for each dimension you would like to view. Each piece of numerical data you want to compute with (sum, average, minimum, maximum, etc.) should also be a column, such as units, sales, and costs.

**Pivot Table**

The

*pivot table*is where you view the data from the data table. The pivot table and the data table are two separate tables. The pivot table points to the data table behind the scenes.

The pivot table is also where you change the view so you can see the data in different ways ("slice and dice the data" or pivot it). You do this by moving the field names around, filtering which values you want to see (for example, only show the "Turtle Project"), and setting field values for advanced computations (such as sum, count, variance, running totals, percent of). Changing the view does not impact any of your data in the data table. For example, removing a field from your pivot table does not change the underlying data table.

**Crosstab**

A

*cross-tabulation*(or crosstab) is the intersection ("crossing") of two or more dimensions so that the intersection points represent the computations (sum, count, etc.) for each combination of dimensional values. Below is a subset of a data table.

(Click image to enlarge) |

There are two dimensions in the data table above:

- Gender, with possible values of Male and Female
- Handedness, with possible values of Left and Right

(Click image to enlarge) |

(Click image to enlarge) |

**ADDITIONAL REAL-LIFE EXAMPLES**

I have created scores of pivot tables over the years. I find value-added uses for them in many situations. Here are a few more examples to give you ideas.

I created this pivot table to track the status of over 200 individual projects each month while managing a large program.

(Click image to enlarge) |

(Click image to enlarge) |

(Click image to enlarge) |

(Click image to enlarge) |

**TIPS ON USING PIVOT TABLES**

Start simple and try it out. Build a simple data table with the information you think you need, put in column headings and a few sample data rows of mock data, build a pivot table using the PivotTable Wizard, and try it out. Does it answer the questions you have? Go back and make adjustments to your data table until you feel you have the right model. Once you get comfortable with the pivot table concepts and its capabilities, designing data tables properly will become natural.

Include computed fields in your data table, if appropriate. In the first real-life example, the Total Cost field is computed by multiplying the Units field by the Cost per Unit. Color this column (to remind you it is computed with a formula and not entered directly) and place at the far right of the data table. As you add rows to your data table, you can then copy the formulas to the new rows.

You can even use computed fields based on look-up values. For example, if the “Turtle Project” will always be associated with the “TRW Program”, you could make the Program field a computed value with a VLOOKUP in a Project-Program cross-reference table. Just be careful. Make sure that the association will always be a true statement. For example, a

*project manager*may be leading the “Turtle Project” today but could be replaced by a new

*project manager*later in the project. If you want to view the data by

*project manager*, you should not compute this field but manually fill it in each time for each record.

A pivot table is based on a range of rows in your data table. If you insert rows outside of this range, your pivot table will not know to use this data. To prevent this from happening, clearly mark the last row in the range of your data table. When you insert rows above and inside the range, the pivot table will automatically adjust and include the new rows. Here is an example:

(Click image to enlarge) |

While pivot tables are less prone to errors than other approaches, there are still some gotchas you should always check.

- First, refresh the values in the pivot table by pressing the exclamation point refresh icon. This makes sure the table includes any changes to the data table since the last time the pivot table was refreshed.
- Second, make sure the pivot table is referring to all of the data rows in the data table. Check that the range for the pivot table includes all of the rows you want in your results.
- Third, make sure there are no filters within the pivot table omitting some values from the results, unless that is what you wanted. Pivot table functionality allows you hide and filter certain dimensional values.
- Fourth, make sure the calculation on the crosstab is the calculation you want. A common mistake is for the computation to be a Sum when you want a Count or vice versa.
- Finally, as with the use of any spreadsheet, spot-check the answers to make sure they make sense and are not out of line of what you expect.

**ADDITIONAL RESOURCES TO FURTHER YOUR KNOWLEDGE**

Here are some places to find more information about pivot tables:

- The Help feature in Microsoft Excel. While it is not as extensive as it should be, it should be your first stop. More recent versions of Excel include "courses" to step you through simple examples and how-to's within your particular version of Excel.
- The Microsoft website with support for Excel.
- Any of the free Pivot Table tutorials online. Do a search on "Excel pivot table tutorial". I found this example on a website called ProductivityPortfolio. This tutorial provides examples in Excel 2007.

Happy pivoting!

## No comments:

## Post a Comment