An Introduction to Pivot Tables

If you manage any size of spreadsheet, large or small and full with data, pivot tables can probably help you.

What is a pivot table?It is a tool provided in Excel to create automatic tables used to organize, condense, and/or analyze a set of data.

Why use a pivot table?Because employing pivot tables is often a faster, more flexible, and more powerful way to do analysis than with standard excel functions.They are also easy to learn, fun to use, and require no programming (such as Visual Basic) experience.

This series of articles will explain how to use pivot tables and engineer pivot tables for the benefit of a fictional tax-preparation business owner.

Imagine the simple case of a self-employed tax preparer.He will not charge the same price to each customer because tax returns vary in difficulty and length.Furthermore, he may charge less to friends and family or charge a higher rate near the tax deadline in April.The best accounting practice would be to record each transaction.The following picture shows a list of the tax preparer’s sales for the 2010 tax season.

If you could see the whole list, there were 62 sales.Suppose the business owner wants a list of all his clients in order to send them a thank you note.Furthermore, he’d like to know how many sales each customer gave him and the total revenue so that he can make projections for the next year.How would he gather this information?

Certainly, he could sort the list by name and then use SUM() functions to add the sales in dollars and COUNTA() to find the number of sales.But this would be tedious, typing out the functions over and over and selecting the ranges repeatedly.Furthermore, mistakes are possible.In about 30 seconds, I created a pivot table to supply all of this information.

In the following articles, we will teach you how to build this pivot table as well as more advanced pivot tables and provide you with additional ideas on how to use them.  At the end, you will have skills that will save you time,  enhance your analysis, and allow you to provide help to others.