Grouping Records in Excel Pivot Tables
You can use Excel pivot tables to summarize sales data when trying to forecast sales. One of the ways that you fine-tune a summary in a pivot table is to group a row field or a column field.
When you group a row field or column field, you combine the values in the field. For example, you might have a field that shows the dates when sales were made. Excel’s pivot tables make it possible for you to group individual dates like September 4 and September 6 into weeks, like September 1 through September 7. Or you can group into months, or quarters, or years. It all depends upon how you want to forecast — by week, by month, by quarter, by year. Keep your time period in mind.
Knowing when to group records
If you followed the steps in the preceding section, you have a pivot table that summarizes revenue, but it might still do so day by day. The reason is that the table you’ve based the pivot table on shows revenue by day. Summarizing revenue by month (or week, or quarter, or year) would be much better. Here’s how to do that.
Notice that the underlying table has several identical dates. The pivot table combines any identical dates into just one row, adding up their revenue along the way. One of the things that pivot tables do is to combine identical values in a table into the same item in a row, column, or filter field.
But you don’t want to forecast by individual dates. That’s not the right time period. In this kind of situation, you need to group individual dates that belong to the same month into one row of the pivot table. Then all the revenues for the grouped dates are also totaled.
Of course, you’re not restricted to grouping on months. You can use seven-day periods, quarters, years, and others (such as three-day or ten-day periods). If you’re concerned about time of day, as you might be with online sales, you can use the hour of the day as a period. Or if your raw data shows the time of day when a sale was made, you can group the individual hours into 8-hour shifts.
Creating the groups
Start with the day as the row field as shown here.
After you have a pivot table set up, grouping the individual dates in the row field is simple. Follow these steps:
- Right-click any of the cells in the column of the pivot table that has the dates — for example, cell E7 in the figure.
- Choose Group from the shortcut menu.
- Choose Months from the list box in the Grouping dialog box. See the following figure.
- Click OK.
Because you want to summarize your revenues by month, you can accept the default selection, Months. When you click OK, the pivot table changes and looks like the one shown here.
You always need to know how you’re going to get out. If you want to ungroup the records, do this:
- Right-click in a cell with grouped records.
In the figure, that’s a cell in the Row area.
- Choose Ungroup from the shortcut menu.
Now you have the row field back to its original state.
If you want to change a grouping level from, say, Months to Quarters, you don’t need to ungroup first. Just right-click a grouped cell, deselect Months, and select Quarters. Then click OK.
In forecasting, grouping in pivot tables tends to occur with date fields. But the capability extends to any numeric field. You could group on discount percentages to view sales in which the price was discounted less than 5%, from 5% to 10%, 10% to 15%, and so on. Or you could group by length of vehicle leases.