How to split a customer list by location in Google Sheets using =TERRITORY()

To split a customer list by location in Google Sheets, geocode the addresses, then use =TERRITORY() to assign accounts. You can pass a revenue column into the function to balance the workload across your reps so physical distance and total account value remain equitable.

This approach is for sales operations and service managers who need to divide 300 customer accounts across 5 specific reps without manual mapping. The end state is a spreadsheet with assigned rep names and a live URL tracking every account's boundary.

The problem with manual customer splits

Operations teams often try to split a customer list by location using a simple spreadsheet sort. If you have a column of postcodes in column B and sort A to Z, you get a geographically grouped list. Dividing 2,000 rows into four batches of 500 feels balanced on the screen.

The reality on the ground is different. Postcode density varies wildly. A single postcode in a dense urban centre might contain 400 high-density flats, while a rural postcode covers a 50-mile radius with only 12 scattered farmhouses. If you split strictly by row count, Crew A finishes in two hours while Crew D spends its entire day driving.

Revenue imbalance is the second failure point. If your highest-paying clients happen to cluster in affluent postcodes, an alphabetical or numerical sort hands all the high-value accounts to a single team. You end up with skewed commission payouts and uneven workloads.

Manual sorting also ignores the physical road network. Two addresses might sit in adjacent postcodes but be separated by a river or a motorway with no crossing for miles. True operational fairness requires geographic clustering based on actual coordinates and drive times, not text strings.

Instead of manually dragging rows, you can use coordinate data to group accounts geographically. By converting addresses to latitude and longitude pairs using =GEOCODE(A2:A50), you map the exact physical location of every stop. From there, you can draw data-driven boundaries that respect actual driving distances and distribute account weights evenly across your teams.

Step-by-step: Assigning balanced territories using =TERRITORY()

To split your customer list by location without the postcode trap, you need to define geographic centre points and assign stops based on proximity and weight. Here is the exact method to automate this in Google Sheets using InstaMaps.

1. 1. Geocode your starting data: Ensure your customer addresses are standardised. If you have addresses in column A (A2:A201), generate coordinates with =GEOCODE(A2:A201). This outputs precise latitude and longitude pairs, which the clustering formula requires to calculate distances.

2. 2. Define your territory centre points: Create a separate table listing your operational hubs or crew starting points. If you have five regional depots, list their addresses in cells G2:G6. Geocode these depot addresses using =GEOCODE(G2:G6) to establish your fixed centre points.

3. 3. Implement the =TERRITORY() formula: To assign customers to a depot, write =TERRITORY(). Assuming your customer coordinates are in column B (B2:B201) and your depot coordinates are in H2:H6, the formula assigns territories based on the closest physical hub.

4. 4. Add a weight column for revenue: Geographic proximity alone does not guarantee fair workloads. To prevent one depot from receiving all the high-revenue or labour-intensive accounts, include a weight reference. If your annual revenue or estimated service hours for each customer is in column D (D2:D201), you configure the formula to balance the total value of column D across the defined centre points.

5. 5. Map the final output: Instead of splitting 200 rows evenly into 40-row chunks, the formula evaluates the coordinates. It assigns customers to Depot 1, Depot 2, or Depot 3, adjusting the boundaries so the sum of column D remains equal across all five territories. Once =TERRITORY() assigns a depot name to each customer in column E, generate a visual check. Use =INSTAMAP(B2:B201, E2:E201) to create a live, hosted map URL that updates as sheet rows change.

Worked example: Balancing a 200-home service farm across 5 crews

Consider an installation company with a farm of 200 existing customers. They need to split this list by location across five crews for routine maintenance. A simple row split gives each crew 40 homes. However, the top 20 highest-paying clients all reside in the northern suburbs, meaning Crew 1 receives all the profitable, easy work.

Your customer addresses sit in cells A2:A201. You run =GEOCODE(A2:A201), which populates column B with coordinate pairs. Your annual maintenance revenue for these accounts sits in column C (C2:C201). Total revenue across the 200 homes is £240,000, meaning the goal for each of the five crews is a balanced territory worth £48,000.

In cells F2:F6, list the five crew leads' home addresses (the starting depots). Run =GEOCODE(F2:F6) to output their coordinates into G2:G6.

Now, apply the formula. You write =TERRITORY(B2:B201, G2:G6, C2:C201) into cell D2.

Instead of drawing rigid straight lines across a map, the formula assigns a crew number (1 through 5) to each row in column D based on the coordinates in column B. Because you included column C as the weight argument, the algorithm adjusts the geographic clusters to balance the revenue.

Crew 1 might get 28 homes that generate £47,500 in revenue because those homes are clustered tightly together in a dense urban centre with short drive times. Crew 5 might get 52 homes generating £48,200 because they are spread across a rural region with lower individual account values but longer travel distances.

Finally, you build the daily routes. Since a crew cannot visit 52 homes in one day, they filter column D for their assigned number (e.g., 'Crew 5'). To sequence their daily stops from their depot, they can use =SORT_BY_DISTANCE() on their specific subset of coordinates. To map their assigned farm for the week, they run =INSTAMAP() to generate a colour-coded map URL, clearly showing the balanced geographic boundaries.

Mapping the split results for field crews

Pass the newly assigned territory data into a visual layer using =INSTAMAP().

If an operations manager cancels a 47-home installation job in row 42 and replaces it with a new lead, the map updates automatically. Delete the old coordinates in F42 and G42, input the new ones, and refresh the hosted map URL. The marker moves without requiring a map rebuild or manual export.

To map individual territories for specific field crews, use standard Sheets filtering to pass exact ranges. For Crew C, isolate their assigned rows using cell references: =INSTAMAP(FILTER(F2:F500, B2:B500="Crew C"), FILTER(G2:G500, B2:B500="Crew C")). This provides each team with a specific, uncluttered URL containing only their assigned stops.

If a coordinate returns an #N/A error due to a poorly formatted address in row 114, run =CLEAN_ADDRESS(A114) before feeding it back into the geocoder. The INSTAMAP function ignores blank cells, but fixing errors prevents gaps in your field crew's route.

1. 1. Ensure your sheet contains valid coordinates. If you only have addresses, generate coordinates first in columns F and G: =GEOCODE(A2:A500).

2. 2. Deploy the live map formula in cell H2: =INSTAMAP(F2:F500, G2:G500, B2:B500). Here, columns F and G hold latitudes and longitudes, while column B holds the newly calculated territory assignments.

3. 3. Click the resulting URL. The add-on generates a live hosted shareable map URL that updates when the sheet changes, with markers colour-coded by your assigned territories.

Who this workflow is for (and who should avoid it)

This specific workflow-using formulas to split a customer list by location based on a revenue or workload column-is built for operations managers balancing finite resources. By setting your territory count parameter (e.g., two territories for two drivers), the function calculates the mathematical midpoint based on your chosen weight.

1. Field service routing: A plumbing business with 5 crews and a 200-home farm. Assigning stops by balancing the revenue column ensures no single crew gets all the low-paying maintenance calls while another is stuck with time-consuming installations.

2. Franchise mapping: A development lead dividing a 5,000-row postcode list into three new exclusive territories. Balancing by historical sales volume prevents franchisee disputes over high-density postcodes.

3. Delivery management: A local farm box delivery service balancing 150 drops by produce weight (column E) across two drivers, ensuring neither vehicle is overloaded.

4. Simple mail merges: If you are sending a one-off marketing email to 1,000 local residents and do not need to balance workload, skip =TERRITORY(). Splitting by territory is overkill for static lists. Use =WITHIN_RADIUS() to find who lives near your shop instead.

5. Single-driver dispatch: If you only have one sales representative visiting 20 stops, use =VISIT_ORDER() to optimise the sequence of the route. =TERRITORY() will just group them together without optimising the driving sequence.

6. Sub-20 row lists: If you have 15 customers, you can eyeball the list. Writing a territory formula for fewer than 20 rows wastes more setup time than it saves.

Limits and honest alternatives

The InstaMaps add-on is free, but it operates under strict mathematical and quota limits. Knowing these prevents broken formulas mid-route.

The free tier allows exactly 100 lookups per day (1,000 lookups per day with an email unlock). If your sheet has 850 addresses in column A, =GEOCODE(A2:A851) will process the first 100 and return #LIMIT! errors for the remaining 750. For sheets exceeding 1,001 rows, you must process the data over two days or split the formula across multiple Google accounts to process 1,000 rows each.

Google Sheets restricts custom function calculation times. Passing 50,000 rows into =TERRITORY() simultaneously will cause the sheet to time out. We recommend chunking territory calculations into batches of 5,000 rows to avoid execution errors.

The geocoder requires clean data. If =GEOCODE(A2) returns #N/A, the address likely contains missing postcode data. Run the problematic cell through =CLEAN_ADDRESS(A2) first, or extract the postcode using =POSTCODE(A2) and geocode just that.

1. Under 20 rows: If you have 18 customers to split between two technicians, manual sorting is faster. Sort your sheet by postcode, draw a line down the middle, and assign the top half to Tech 1 and the bottom half to Tech 2. Do not waste daily lookup quotas on datasets you can manage manually.

2. Complex turn-by-turn routing: If you need navigation and traffic avoidance for exactly 11 stops, use =ROUTE_LINK(). This pushes the data to the official Google Maps interface rather than keeping it strictly in the sheet.

Common Questions