Group Text Fields to Build Territories

January 27, 2023 - by Bill Jelen

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 Analyze tab.

A pivot table with Office location in column A. Choose the cells that say Fort Lauderdale, Hialeah, and Miami. In the Pivot Table Tools Analyze tab, click Group Selection. — Figure 973. 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.

A new field called Office2 appears to the left of the Office. At the top, cell A4 says Group1. Cells B4:B6 say Fort Lauderdale, Hialeah, and Miami. The remaining cities are repeated, with Jacksonville being the Office location in B7 and a new group called Jacksonville appearing in A7. In row 8, columns A & B both say Orlando. Row 9 has two Pembooke Pines, and so on. — Figure 974. 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.
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.

Open the Field Settings for the new Office2 field. Change the Custom Name to Territory. Leave the Subtotals at Automatic. — Figure 975. Change the name of the grouped field & add subtotals.

Results: You’ve added territories on the fly in the pivot table.

Click in the Group1 cell and type a new name of South Florida. Repeat the steps to group Jacksonville and Orlando into a territory called East Central. Repeat again to group Saint Pete, Tallahassee and Tampa into Gulf Coast. — Figure 976. 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.

From the Terrtory field in column A, click Collapse Entire Field. You now have one row per territory and a Grand Total. — Figure 977. 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.

Click on B13:B15 and B11 and Group Selection. This is grouping the three offices for Gulf Coast as well as Orlando from East Central. — Figure 978. 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.

Orlando is now moved from East Central to Gulf Coast. The territory names can be renamed to South, Super Central, and Gulf Coast New. — Figure 979. Territory balancing is an iterative process.

This article is an excerpt from Power Excel With MrExcel

Title photo by Bob Canning on Unsplash