An examination of how people go about planning for the development of pivot tables, selecting data sources, integrating them, identifying desired results, formatting pivot tables, populating data and more. Develop a deeper sense of what’s possible.
One of Stephen R. Covey’s “7 Habits of Highly Effective People” is to “Begin with the end in mind!” and nowhere is that advice better taken than when building Pivot Tables in Excel.
The first step in planning is to clearly describe what you want users of your pivot table to know. What information do you want them to have, and in what ways will they need to look at it to add value to their analyses. By date or timeframe? By contributor? By geography? By physical attributes? Perhaps you’ll want to nest these, for example by contributor by month, or by month by contributor!
Be comprehensive in your description of the ways in which you want users to be able to view your data to assure that you deliver the highest possible value.
While we’re on the subject of data, it will likely be a good idea to check and evaluate your source data even as you’re setting your goals to make sure you have the data that corresponds to each way you’ll want to look at it. Do you have fields for date, timeframe, contributor, geographic region, size, color, volume, name, and more? Are all the data sources you will require readily available to you for inclusion in your pivot table? Will they be easily accessible? Are they all normalized to each other, so everything comes in with the same column title/field name? Is all the data ready for ingestion into your pivot table?
It may also serve you to evaluate the data even before you begin setting out goals. How large a body of data is it? If it’s not huge, you probably don’t need a pivot table. You can do just about anything you need to do on a regular Excel table.
Begin to sketch out what the tables will look like. Which columns first, second, last? Where will you need calculations including sum, average, count, and more to help summarize the data and begin analysis? Where will you want to be able to filter the data to compare different examples? Where will you want support for various analyses. For example, next to any list of values that you’re totaling you may want to display a column showing how much of a percentage of the total is represented by each value on the list.
Even think about adjacencies. Which columns will you be comparing to each other frequently? You’ll likely want to keep them together.
In your first attempts at constructing pivot tables be sure to save each version of the table as you try out new things. When you find yourself asking “what would it look like if I did this….” save a version right there, and then another version once you’ve added “this” to it. This way you can always back up to a point before where you tried out anything.
Know Before You Do
You’ll want to develop complete confidence that you know what you need to know before you do anything. Know your business, know what you need to know, know your data and then begin to build your pivot table armed with all that knowledge.