Summarizing Sales Data with Pivot Tables
IN THIS CHAPTER
Getting used to the idea of pivot tables
Putting a pivot table on your worksheet
Grouping similar records together
Staying out of pivot table trouble
If you’re going to create good forecasts, you frequently need to match up some amount of revenue with the period that the revenue came in. Or, if you need to deal with the number of units sold rather than revenue, you need to pair a count of units with the period when they were sold. Excel’s pivot tables give you an excellent method of doing that, if you set your data up right.
When you have your pivot table, it often makes sense to look at your revenues from different viewpoints — usually in terms of months or quarters or years, but often by means of other variables such as ranges of commission percentages. Pivot tables refer to this as grouping. In this chapter, I cover building pivot tables and grouping dates.
Understanding Pivot Tables
A pivot table summarizes one variable — typically, one that can be counted or summed, such as units sold or revenue — in terms of another variable, typically one that comes in categories. Pivot tables are the most powerful method in Excel of summarizing any kind of data, including sales information. That makes pivot tables a useful way to prepare a baseline for your forecast.
A pivot table is often based on an Excel table. (If you’re not yet a table maven, check out Chapter 6.) Excel tables put different variables, or fields, ...