Excel: Group Text Fields to Build Territories Instead of Using Calculated Items

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

  1. 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.

  2. After you group the first products, chaos results.
    1. 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.

  3. Change the name of the grouped field & add subtotals.
    1. Back in the pivot table, select the cell called Group1. Type a new name for this group right in the cell. Perhaps South Fla.
    2. Repeat steps 3, 4, and 6 for each additional territory.

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

  4. 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.

  5. 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.

  6. 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.

  7. Territory balancing is an iterative process.

For more resources for Microsoft Excel