How to map ZIP codes from a Google Sheets list

Pick the kind of map first

“Map my ZIP codes” can mean three different maps, and they use different tools:

All three start from the same small table: each ZIP code once, how many rows it has, and its coordinates. The next three sections build that table, with formulas only.

One thing the built-in Google Sheets geo chart can’t do is ZIP codes. It shades countries, and states or provinces within a country, so it’s fine for a by-state map, but it has no ZIP-level option.

Clean the ZIP codes

ZIP codes in real lists are messy: 2108 instead of 02108 because the column was a number, ZIP+4 codes such as 02108-1234, and stray spaces. If your ZIP codes are in column A of a tab called Customers, put this in B2 and fill down:

=IF(A2="", "", LEFT(TEXT(A2, "00000"), 5))

It turns 2108 into 02108, cuts 02108-1234 to 02108, and returns text, so the leading zero survives. Call column B ZIP5. If some ZIP codes have spaces around them, use TRIM(A2) in place of A2 in the TEXT part.

If you only have full addresses, pull the ZIP out first with the formula in our ZIP code from address guide.

Count rows per ZIP

Make a tab called Counts and put this in A1. It lists every ZIP code once, with how many rows have it, busiest first:

=QUERY(Customers!B2:B, "select B, count(B) where B <> '' group by B order by count(B) desc label B 'ZIP', count(B) 'Count'", 0)

The result looks like this:

QUERY updates by itself when rows are added to Customers. To count a value instead of rows, such as sales per ZIP, use sum(C) with that column, for example "select B, sum(C) where B <> '' group by B label B 'ZIP', sum(C) 'Sales'" on Customers!B2:C.

Free coordinates for every ZIP

To put a ZIP on a map you need a point for it. The U.S. Census Bureau publishes a free file with the centre point of every ZIP Code Tabulation Area (ZCTA), the Census’ version of ZIP code areas. No API, no key, no limits.

1. Import the 2025 ZCTA Gazetteer file into a tab called ZCTA. The exact steps, including the setting that keeps the leading zeros, are in our distance between ZIP codes guide. You end up with ZIP codes in column A, latitude in G and longitude in H, rows 2 to 33792.
2. On the Counts tab, add the headers Latitude, Longitude, Band and Location in C1:F1.
3. Put these in row 2 and fill down further than your list, so new ZIP codes are covered:

We checked these against the 2025 file. They return:

10118 is a ZIP code used by a single building (the Empire State Building), and ZCTAs only cover ZIP codes where people live, so PO box and single-organisation ZIP codes have no point in this file. For a handful of those, look the ZIP up in Google Maps and type the coordinates in. Change the band limits (10 and 5) to suit your numbers; three to five bands read best on a map.

Note that the point is the centre of the ZIP area. That’s right for a map of counts by ZIP, and it’s also a privacy bonus: you can share the map without showing anyone’s street address.

Map 1: coloured pins in Google My Maps

Google My Maps is free, makes an interactive map you can share or embed, and imports straight from a Google Sheet.

1. Copy the Counts tab and paste it as values into a new tab called Map (Edit → Paste special → Values only), and delete rows that say “ZIP not found”. My Maps reads a snapshot, and plain values import faster and more predictably than formulas.
2. Go to google.com/maps/d, click Create a new map, and in the first layer click Import, then Google Drive, and choose the spreadsheet and the Map tab.
3. For the columns to position your placemarks, tick Latitude and Longitude. For the title, choose ZIP.
4. In the layer, click Uniform style and set Group places by to Style by data column: Band. Give each band a colour, for example light to dark, or pick a larger icon for 10+.
5. Click any pin to see its ZIP, count and band. Use Share to send the link, or Embed on my site once the map is public.

My Maps takes up to 2,000 rows per layer, and since each row is now one ZIP rather than one customer, even a very large list usually fits in a single layer. It doesn’t update itself: when the counts change, paste the values again and use the layer’s Reimport and merge option. Our guide to mapping addresses covers My Maps in more depth.

Map 2: shaded ZIP areas in Data Studio

To shade the ZIP areas themselves, darker where the count is higher, use Google’s free Data Studio (called Looker Studio until 2026). Its Google Maps chart knows the shape of every US ZIP code, so it needs only the ZIP and the count, not coordinates.

1. Open datastudio.google.com and create a Blank report. Add data with the Google Sheets connector, choose your spreadsheet and the Counts tab (or the Map tab), and keep Use first row as headers ticked.
2. Before adding a chart, set the field type: in the data source, find the ZIP field, open its Type menu and choose Geo → Postal Code. If it shows as a number, choose Postal Code anyway; if leading zeros are lost, make sure the Sheet column is text, as the formula above returns.
3. Add a chart → Google Maps → Filled map. Set Location to ZIP and the Color metric to Count.
4. In the Style panel, choose the colours for the maximum, middle and minimum, and the colour for areas with no data.
5. Share the report or embed it. Unlike My Maps, it refreshes from the sheet, so new rows show up without re-importing.

Limits worth knowing: ZIP areas on a filled map are US only (for other countries’ postcodes, use a bubble map instead), and the map draws up to 3,500 areas. Filled maps don’t accept latitude and longitude or street addresses; for those, use a bubble map.

Map 3: a heat map

A heat map shows concentration as a glow rather than borders, which is easier to read when ZIP areas are small and dense, as in cities.

1. In the same Data Studio data source, set the Location field (the “lat,lng” column) to type Geo → Latitude, Longitude.
2. Add a chart → Google Maps → Heatmap. Set Location to the Location field and Weight to Count, so a ZIP with 12 customers glows more than a ZIP with one.
3. Adjust the radius and opacity in Style until the hot spots stand out without merging into one blob.

Coordinate fields can draw far more points than postal codes, which matters if you later map individual addresses instead of ZIP centres. To put the coordinates on the customer rows themselves, geocode the addresses; see our geocoding guide.

ZIP code boundaries

If you only need to see the outline of one or two ZIP codes, type the ZIP code into Google Maps: it draws the boundary of that area. For many outlines on your own map:

• Data Studio’s filled map, above, is the simplest way to show many ZIP boundaries, shaded by your numbers.
• The Census Bureau’s cartographic boundary files include ZCTA shapes for the whole country. They’re free but large, made for GIS software, and need converting (for example to KML) before My Maps can use them. My Maps accepts KML files up to 5 MB, which fits a city or county’s worth of ZIP areas, not a whole state.

ZCTAs and the Postal Service’s ZIP codes are close but not identical: the Postal Service defines ZIP codes as delivery routes, and the Census Bureau builds areas from them. For mapping customers or members, the difference rarely matters.

Coordinates and a map image in the sheet: MapRows

The ZCTA file covers US ZIP codes where people live. For postcodes from other countries, single-building ZIP codes, or full addresses, you need a geocoder. MapRows does that from a sidebar in Google Sheets.

1. Install MapRows and open it from Extensions → MapRows.
2. On the Geocode tab, pick the column to geocode as the address. A bare five-digit number can match a postcode in another country, so geocode a helper column such as =A2&", USA" (giving “02108, USA”) instead. MapRows writes latitude, longitude and the matched place into new columns, with a status per row. Each ZIP is one lookup.
3. On the Map tab, pick the latitude and longitude columns and insert a map image of the points on its own sheet, with the point count and date. It’s a picture for a report or a slide, not an interactive map; use My Maps or Data Studio for that.

Geocode the Counts list rather than every customer row, and 2,000 customers in 150 ZIP codes cost 150 lookups instead of 2,000.

Common problems

• Every New England ZIP says “ZIP not found”. The leading zero was lost on one side. Check that the ZIP5 column shows 02108 (text), and that the ZCTA tab was imported with conversion unticked.
• QUERY returns only a header, or misses some ZIPs. The column mixes numbers and text, and QUERY uses the majority type. Point QUERY at the cleaned ZIP5 column, which is all text.
• Pins land in the wrong country in My Maps. You let My Maps place pins from the ZIP column instead of Latitude and Longitude. Re-import and tick the coordinate columns.
• Data Studio shows the ZIP as a number and the map is empty. The field type is still Number. Change it to Geo → Postal Code in the data source.
• Some areas are grey on the filled map. Those ZIP codes have no rows. That’s the “no data” colour, which you can change in Style.
• The heat map is one big blob. Lower the radius in Style, or zoom in. National data at a low zoom always merges.

Frequently asked questions

Related

• The best Google Sheets add-ons for maps, geocoding and distances
• How to map addresses from Google Sheets
• How to calculate the distance between ZIP codes in Google Sheets
• How to get the ZIP code from an address in Google Sheets
• MapRows documentation
• All guides

Disclosure: we make MapRows, the Google Sheets add-on mentioned in this guide. Every formula and the script here work without it.

Google Sheets, Google Maps, Google My Maps and Data Studio are trademarks of Google LLC. ZIP Code is a trademark of the United States Postal Service. MapRows and Cellbrook are not affiliated with or endorsed by Google, the U.S. Census Bureau or the Postal Service.