Guide · Sales territory design

Route every order to the right rep, using the ZIP code you already collect

A new order just came in. Which rep owns it? And when you move a rep's patch next quarter, how do you move every order with it? Every order already carries a postal code, usually because tax needs it. This page turns that ZIP code into a county with the ZIP Code List with Coordinates, sends the county to a team through a table you write once, and attaches households and median income from the County List with Demographics so you can re-balance. Both files are required. Neither answers the question alone.

Updated Nov 2026·Excel · Google Sheets·8 min read

The idea the page rests on: assign counties, not ZIP codes. A rep book of hundreds of ZIP codes can't be kept up. The same book is a short list of counties. Assign each county to a team once, let every ZIP inherit its county's team, and re-balancing becomes moving one row. Harris County, TX alone holds 132 ZIP codes in our file; one row in your team table routes all of them.

One thing trips people up, so it comes first. Postal codes lose their leading zero. Excel turns 07030 (Hoboken) into 7030, and the lookup then finds nothing. Step 2 repairs this, and step 4 shows you the sign that you skipped it.

What you need

Two files, in Excel or Google Sheets. You need both: the ZIP file turns a ZIP code into a county, and the county file says how many households and what income sit in that county. The ZIP file has no population, household or income columns (its methodology.md says so too).

Sheet names used throughout: Orders (your export), zips (the ZIP file, the same name the ZIP guide uses), Counties (the county file) and Teams (your own table). Column letters are those of the files as shipped.

FileColumnWhat it holds
ZIPzip_code (A)5-digit ZIP code, the key, one row per ZIP
ZIPprimary_county (D)The ZIP's main county. A ZIP can span several; this is the one with the most residents
ZIPcounty_fips (E)5-character county code, as text. Blank when there is no county
ZIPstate_code (F)Two-letter state or territory code, always present
Countycounty_fips (F)5-character county code, as text
Countyhouseholds (AN)Occupied homes, five-year average
Countymedian_household_income (AJ)Typical yearly household income, five-year average, in dollars

Step by step

  1. Import both files; don't double-click them. Excel: Data → From Text/CSV, and set zip_code and county_fips to Text. Google Sheets: File → Import, and turn off "Convert text to numbers, dates, and formulas." The full version: Open a US City List CSV in Excel or Google Sheets without losing the leading zeros.
  2. Repair the order's postal code to five characters, as text. Leading zeros are real: the first row of the ZIP file is 00601, and 2,580 of the 33,799 ZIP codes in our file start with zero, almost all in New Jersey, Massachusetts, Maine, Connecticut, Vermont, New Hampshire, Rhode Island and Puerto Rico. Add a column zip5 to Orders. If your postal code sits in column B:
    =IF(ISNUMBER(B2),TEXT(B2,"00000"),LEFT(TRIM(B2),5))

    A number gets its zeros back; text such as 07030-1234 is cut to five characters. Replace B with wherever your postal code sits.

  3. Look up the county once. From here on, Orders has the postal code in B, zip5 in C, county_fips in D, county_name in E, route_key in F and team in G. Add county_fips in D:
    =IFNA(XLOOKUP(C2,zips!$A:$A,zips!$E:$E)&"","No ZIP match")

    and county_name with zips!$D:$D in place of zips!$E:$E. Two pieces to know: IFNA(…,"No ZIP match") makes a miss visible instead of blank, as on the ZIP guide; &"" stops Excel showing a blank county as 0. The same formula works in Google Sheets.

  4. Count the misses before you do anything else. Two counts:
    =COUNTIF(D:D,"No ZIP match")
    =COUNTIFS(D:D,"",C:C,"<>")

    Fill the step 2 and step 3 formulas down only as far as your last order. An empty row that carries the formula shows "No ZIP match" and inflates the first count. The second count covers only rows that have an order, not the empty rows below; these are ZIP codes in our file that have no county, and step 5 handles them. Now the expected rate: in our file, 70 of the 33,799 ZIP codes (0.21%) have no county. If your misses are around that, you are fine. If they are near 30%, you have a formatting problem, not a data problem: go back to step 2. What is normally in the misses: typos, Canadian or other foreign postal codes, a ZIP+4 typed without its hyphen, and ZIP codes that are not in our file.

  5. Handle Washington, DC and four territories by hand. All 56 DC ZIP codes in our file have no county, because DC is not in the county file (see Build balanced sales territories from US counties, which says the same). The other 14 are in the U.S. Virgin Islands (5), Guam (5), the Northern Mariana Islands (3) and American Samoa (1). Add route_key:
    =IF(D2="",XLOOKUP(C2,zips!$A:$A,zips!$F:$F,""),D2)

    A DC order then routes on DC, and every other order on its county code. In Teams, DC is one row keyed DC.

  6. Build the team table once: one row per county. Teams has two columns to start: A route_key (the county code as text, or DC) and B team. Format column A as Text before you type any county codes, or copy them from Counties column F. Otherwise Excel turns 09110 into 9110, and every order from Alabama, Alaska, Arizona, Arkansas, California, Colorado and Connecticut comes back "Unassigned". It is the same trap as step 2, in your own table. Then in Orders, in team (G):
    =XLOOKUP(F2,Teams!$A:$A,Teams!$B:$B,"Unassigned")

    Why: to move a county to another team, change one cell in Teams, and every past and future order from every ZIP in it follows.

  7. Attach households and income to each county, for balancing. In Teams, add columns C and D:
    =XLOOKUP(A2,Counties!$F:$F,Counties!$AN:$AN,"No county row")

    for households, and Counties!$AJ:$AJ in the same formula for median_household_income. These are the county's figures, not the customer's (see "What this can and can't tell you").

  8. Re-balance on county totals. Sum households by team with =SUMIF(Teams!B:B,"Team 1",Teams!C:C); text such as "No county row" is ignored. Use the method in Build balanced sales territories from US counties to move counties between teams. If you size a business-to-business book, see its step 11, "from households to businesses"; its figures are not repeated here.

All formulas work unchanged in Excel 365 and Google Sheets. Put each in its own cell in the column named above.

Check your work against our numbers

Put these ten postal codes in a test Orders sheet, typed exactly as shown. Two are broken on purpose.

Typedzip5county_fipscounty_nameroute_keyCounty householdsCounty income
7030 (number, zero lost)0703034017Hudson34017295,552$90,032
021080210825025Suffolk25025322,184$92,859
061030610309110Capitol09110389,086$91,541
770027700248201Harris482011,728,103$73,104
77479-12347747948157Fort Bend48157281,259$113,409
100011000136061New York36061775,376$104,553
2000120001(blank)(blank)DC——
890108901032009Esmeralda320094930 or blank
006010060172001Adjuntas720010 or blank0 or blank
M5V 3L9M5V 3No ZIP matchNo ZIP matchNo ZIP match——

A few notes on the table:

  • Hartford comes back as "Capitol". Connecticut appears in the county file as nine planning regions, not its old eight counties. That is correct, and How-to 1 says the same.
  • Esmeralda and Adjuntas join, but carry no figure. Of the 33,729 ZIP codes that join to a county, 135 land on a county with no income figure: 130 in Puerto Rico, plus Esmeralda County, NV and Kenedy County, TX. Puerto Rico's municipios carry no household figure either. The order still routes. Only the balancing figure is missing. Your sheet may show these as 0 or as blank, and either one means the county file has no figure for that county. It does not mean zero households or zero income.
  • If your rows match, the sheet is working.

Two things that break this

The lost leading zero. Step 2, said again as a failure: if zip5 is skipped, every order from the New England states, New Jersey and Puerto Rico misses, and the miss count in step 4 jumps. That jump is the sign.

The city file's zip_codes column looks like the answer, and isn't. If you own the City List with Demographics, you will find a zip_codes column and be tempted to route on it. Don't. It is blank for 28,146 of 48,490 places (58%), and where present it can hold several ZIP codes in one cell (35007 35114), which a lookup cannot match. Routing on cities would also drop about a quarter of ZIP codes, because roughly one in four has no matching city. Use the ZIP file: one row per ZIP, and a county for 99.79% of them.

What this can and can't tell you

This procedure is correct only if county-level figures are a good enough guide for the territory decision you are making. It routes an order to a team; it does not tell you what that customer is worth.

Where it bites:

  • Every ZIP inside a county gets that county's figures. In a large metro county this can't tell one neighbourhood from another: a wealthy ZIP and a poor ZIP in Harris County look identical. Use it to route between territories, not to score customers.
  • A ZIP can cross county lines; the file gives its main county. A few orders will route to the county next door. For routing that is usually fine. If a team boundary runs along a county line, check the edge ZIP codes by hand.
  • The coverage figure is about the ZIP codes in our file. 99.79% of the 33,799 ZIP codes in our file have a county. The 0.21% without one are Washington, DC (56) and four island territories (14), and step 5 covers them.

Each download includes column-definition.md and methodology.md. Read those for what each column means.

Get the files

Two files, as a pair. The ZIP file turns an order's ZIP code into a county; the county file says how many households and what income sit in that county.

ZIP Code List with Coordinates — $29 one-time. 33,799 ZIP codes. See the list, or start with the free 100-row sample.

County List with Demographics — $199 one-time. 3,221 counties and county-equivalents (the 50 states and Puerto Rico), 122 columns. See the list, or start with the free 100-row sample.