Although most of your summarization and calculation needs can be accomplished with standard pivot table settings, there are special situations where you might want your report to be summarized even further.
For example, transactional data is typically stored with a transaction date. It is common that you will want to report this data by month, quarter, or year. The Group option allows you to quickly and easily consolidate transactional dates into a larger group such as month or quarter. Then you can summarize the data in those groups just as you would with any other field in your pivot table.
As you will learn in the next section, grouping is not limited to date fields. You can also group nondate fields to consolidate specific pivot items into a single item.
Grouping Date Fields
Figure 5.33 shows a pivot report by date. With two years of transactional data, the report spans 700+ rows. Yes, 700 rows are a summary from the original 41,550 rows, but managers will often want detail by month instead of detail by day.
Figure 5.33. When reported by day, the summary report spans 700+ rows. It would be meaningful to report by month or year instead.
Excel makes it easy to group date fields. Right-click the date heading or any date item, choose Group and Show Detail, and then choose Group, as shown in Figure 5.34.
Figure 5.34. Right-click a field heading to access the Group menu.
When your field contains date information, the Grouping dialog box, shown in Figure 5.35, will appear. By default, the Months option is selected. You have choices to group by Seconds, Minutes, Hours, Days, Months, Quarters, and Years. It is possible and usually advisable to select more than one field in the Grouping dialog box. In this case, you will want to select Months, Quarters, and Years.
Figure 5.35. Business users of Excel will usually group by months, quarters, and years.
There are several interesting things to note about the resulting pivot table. First, notice that Quarters and Years have been added to your field list. Don't let this fool youyour source data is not changed to include these new fields; instead, these fields are now part of your pivot cache in memory. Another interesting thing to note is that, by default, the Years and Quarters fields are automatically added next to the original date field in the pivot table layout, as shown in Figure 5.36.
Figure 5.36. By default, Excel adds the new grouped date fields to your pivot table layout.
When Grouping by Months, Include Years
Although this is not immediately obvious, it is important if you group a date field by month that you also include the year in the grouping.
Examine the pivot table shown in Figure 5.37. This table has a date field that has been grouped by month and year. The months in column A use the generic abbreviations Jan, Feb, and so on. The copier sales for January 2006 are $2.715 million.
Figure 5.37. This table has a date field that is grouped by both month and year.
If, instead, you had chosen to group the date field only by month, Excel still continues to report the date field using the generic Jan abbreviation. The problem is that dates from January 2006 and January 2007 are both rolled up and reported together as "Jan." Having a report that totals Jan 2006 and Jan 2007 might only be useful if you are performing a seasonality analysis. Under any other circumstance, the report of $6 million in copier sales in January will be too ambiguous and will likely be interpreted wrong. To avoid ambiguous reports like the one shown in Figure 5.38, always include a year in the Group dialog box when you are grouping by month.
Figure 5.38. If you fail to include the Year field in the grouping, the report will mix sales from Jan 2006 and Jan 2007 in the same number.
Grouping Date Fields by Week
The Grouping dialog box offers the choices to group by second, minute, hour, day, month, quarter, or year. What if you need to group on a weekly or bi-weekly basis? This can be done.
The first step is to find an actual paper calendar for the year in question. Your data might start on January 3, 2006, so it is helpful to know that January 3 was a Tuesday in that year. You are going to have to decide if weeks should start on Sunday or Monday or any other day. Check the paper calendar to learn that the nearest starting Monday is January 2, 2006.
Right-click the date heading in your pivot table. Choose Group and Show Detail and then choose Group. In the Grouping dialog box, unselect all the By options and choose only the Days field. This will enable the spin button for Number of Days. To produce a report by week, increase the number of days from 1 to 7.
Finally, you have to set up the Starting At date. If you were to accept the default of starting at January 3, 2006, then all your weekly periods would run from Tuesday through Monday. By checking a calendar before you begin, you know that you want the first group to start on January 2, 2006. Change this as shown in Figure 5.39.
Figure 5.39. The key to being able to access the Number of Days spin button is to select Days and only Days from the By field.
The result will be a report showing sales by week, as shown in Figure 5.40.
Figure 5.40. You've produced a report showing sales by week.
If you choose to group by week, none of the other grouping options can be selected. You will not be able to group this or any other field by month or quarter.
Grouping Two Date Fields in One Report
When you group a date field by months and years, Excel repurposes the original date field to show months and adds a new field to show years. The new field is called Years. This is simple enough if you have only one date field in the report.
If you need to produce a report that has two date fields, and you attempt to group both date fields by months and years, Excel will arbitrarily name the first grouped field "Years" and the second grouped field "Years2." This inevitably leads to confusion. In this case, it is important to rename the fields with a meaningful name.
CASE STUDY: Order Lead-Time Report
The material schedulers at a manufacturing plant are usually concerned with the lead time from when an order arrives to when it needs to ship. The schedulers may know that it takes 60 business days to procure material, schedule production, and build the product. In a perfect world, if all their customers would order 61 or more days in advance, the manufacturing plant would not have to keep any excess raw material inventory on hand.
But, in the real world, orders always come in where the customer wants the product faster. In these cases, the manufacturing plant may purchase extra inventory of the components with the longest lead time in order to accommodate rush orders.
If your transactional data source includes a field for date shipped and another field for date ordered, you can easily produce a report showing the normal order lead time by product. This is a valuable report for the master schedulers in the manufacturing plant. Here are the steps to follow:
The resulting table is shown in Figure 5.41. Cell C12 indicates that 19.9% of the copier orders shipped in January 2006 were ordered during the month of January. Another 38.4% of those orders were received in December. This means that 58% of the copier sales from January were received within the manufacturing lead time. This fact dictates that your manufacturing facility will have to keep a whole lot of inventory on hand to meet these short lead-time orders.
Figure 5.41. The order lead-time report makes use of two fields grouped by month and year.
Grouping Numeric Fields
The Grouping dialog box for numeric fields allows you to group items into equal ranges. Specify a start number, an end number, and how many numbers should be in each group.
Grouping Text Fields
You get a call from the VP of Sales. Secretly, they are considering a massive reorganization of the sales regions. He would like to see a report showing sales for the last two years by the new proposed regions. You've been around long enough to know that the proposed regions will change several times before the reorganization happens, so you are not willing to change the Region field in your source data just yet.
First, build a report showing revenue by year and by market. The VP of Sales is proposing building a Southeast region composed of Texas, the Gulf Coast, Florida, the Carolinas, and the Shenandoah Valley. Using the Ctrl key, highlight the six markets that will make up the proposed Southeast region. Right-click one of the markets and choose to group those items. Figure 5.42 shows the pivot table before the first group is created.
Figure 5.42. Use the Ctrl key to select the noncontiguous cells that will make up the new Southeast region.
After the Group command, Excel will add a new field called MARKET2. The six selected cells will be sequenced together and belong to a MARKET2 grouping that is arbitrarily called Group1, as shown in Figure 5.43.
Figure 5.43. Excel arbitrarily calls the first grouping Group1.
Double-click the MARKET2 field and give it a name such as New Region. Click in cell A9 and type a meaningful name instead of Group1.
As you repeat these steps to build other regions, Excel will continue to assign names such as Group2, Group3, and so on. After creating each region, simply type a meaningful name over the cell containing the arbitrary group name.
You will find that the New Region field is a real field. Use the AutoSort feature to sequence it alphabetically.
By default, Excel does not add subtotals for the New Region field. You can easily access the PivotTable Field dialog box for New Region to add subtotals.
Figure 5.44 shows the report as it is ready for the VP of Sales. You can probably already predict that they will need to shuffle markets to the Northeast region in order to balance the regions.
Figure 5.44. After you group all the markets into new regions, the report is ready for review.
Grouping and Ungrouping
After you have established groups, you can hide or show detail using the Hide Detail and Show Detail menu items under Group and Show Detail on the right-click menu. Figure 5.45 shows the pivot table from Figure 5.44 with the detail hidden.
Figure 5.45. With text groups, you can hide or show detail. This view has the market detail hidden.