MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Easy Territories in Excel Pivot Tables

August 22, 2014 - by Bill Jelen

Your pivot table has a list of cities, but no Territory field:

Easy Territories in Excel Pivot Tables
Easy Territories in Excel Pivot Tables

There is an easy way to create a virtual Territory field:

  1. Select the cities in the first territory (Click on first city, Ctrl + Click on others)
  2. In the Pivot Table Tools ribbon, click Group Selection.
  3. By default, they call the territory “Group1”, but you can click in the cell and type a meaningful name, like “South Florida”. Also, click on the heading cell and type a new name such as Territory.
  4. To create the next territory, select those cities and repeat steps 2 & 3.

When you are done, select the Territory heading, click Field Settings, and change the Subototals from None to Automatic.

This is one of the tips in Learn Excel 2007-2010 from MrExcel – 512 Excel Mysteries Solved.

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.