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:
- Create a pivot table with City and Sales.
- Even if you love the Compact Form layout, temporarily change to Tabular Form using the Layout dropdown on the Design tab.
- 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.
- Click Group Selection from the Options tab.
- 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.
- After you group the first products, chaos results.
- 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.
- Change the name of the grouped field & add subtotals.
- Back in the pivot table, select the cell called Group1. Type a new name for this group right in the cell. Perhaps South Fla.
- Repeat steps 3, 4, and 6 for each additional territory.
Results: You've added territories on the fly in the pivot table.
- 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.
- 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.
- 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.
- Territory balancing is an iterative process.