Excel Group Text Fields to Build TerritoriesInstead of Using Calculated Items
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Group Text Fields to Build Territories Instead of Using Calculated Items
Problem: As shown in the previous topic, adding calculated items causes the totals to be wrong. I want to test grouping offices into territories. How can I do it?
Strategy: In “Build a Better Top 5 Using Groups," you learned how to group text in a pivot table. Building territories works in a similar fashion. Follow these steps:
1. Create a pivot table with City and Sales.
2. Even if you love the Compact Form layout, temporarily change to Tabular Form using the Layout dropdown on the Design tab.
3. Select the cities for your first territory. If the items are not in a contiguous range, hold down the Ctrl key while you select the cells.
4. Click Group Selection from the Options tab.
Figure 904 Group the selected cells.
The result appears to be chaos. You will be able to fix this problem, but let’s take a look at what happened below. There is a new virtual field called Office2 in the pivot table. Three cities belong to a value called Group1. Every other office in the pivot table is assigned to an Office2 equal to the office name. Note that the grand total of 2927 did not change.
Figure 905 After you group the first products, chaos results.
5. Select the word Group1 in A4. Click the Field Settings dialog. Change the field name from Office2 to Territory. Change the subtotals from None to Automatic.
Figure 906 Change the name of the grouped field & add subtotals.
6. Back in the pivot table, select the cell called Group1. Type a new name for this group right in the cell. Perhaps South Fla.
7. Repeat steps 3, 4, and 6 for each additional territory.
Results: You’ve added territories on the fly in the pivot table.
Figure 907 Territory was added after creating the pivot table.
If you choose a cell in the Territory column and click Collapse Entire Field, you will see only territory totals.
Figure 908 Collapse to see territory totals.
Additional Details: If your VP of Sales is like my VP of sales, he will decide to re-balance the territories (several times, right?). This process is fairly easy. First, click Expand Entire Field. Then, choose the offices in column B that should be re-grouped. Below, he asked you to add Orlando to the Gulf Coast group.
Figure 909 Creative, and geographically challenged.
Click Group Selection. Those four cities will be grouped with the name of Group 1. Jacksonville will be left alone in a territory that will be renamed Jacksonville.
Hint: after the tenth iteration, try adding some formatting to the pivot table. Maybe he will think this one looks better.
Figure 910 Territory balancing is an iterative process.