Excel Heat Map by ZIP Code: Create and Troubleshoot

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, put ZIP codes and numeric values in separate columns, select the data, then choose Insert > Maps > Filled Map. Excel can shade recognized postal regions by value, but results depend on correctly formatted ZIP codes and the mapping service’s ability to resolve them.

What an Excel ZIP code heat map shows

A Filled Map is a shaded geographic chart, often called a choropleth map. Each location is colored according to its associated number: for example, darker shades might indicate more customers, higher sales, or a greater rate of service requests. It is different from a worksheet heat map made with conditional formatting, which colors cells but does not place them on a geographic map.

A simple source table might have a ZIP code column and a Customer count column. Excel attempts to interpret the location values and plot them as geographic areas, then uses the measure to shade those areas. It does not plot each customer’s address or calculate a radius around a ZIP code. If you need address-level points, routes, or distance analysis, use a different mapping approach.

ZIP codes are US Postal Service delivery identifiers, not a guarantee of a neat, continuous geographic boundary. Some are associated with particular organizations or delivery routes, and their shapes can change. Census ZIP Code Tabulation Areas (ZCTAs) are geographic approximations, not exact equivalents to every USPS ZIP code. That distinction matters when a map is being used for analysis rather than a quick visual summary.

Prepare ZIP codes and values before mapping

Good input data is the most important part of a useful map. Use a clear header row and one record per location, with a numeric measure for the shading. Keep the ZIP code separate from city, state, and metric fields. For US data, a five-digit ZIP is usually the clearest starting point; include a state or country field when it helps distinguish locations or when your data includes other countries.

  • Preserve leading zeros. ZIP codes such as 02108 must remain five-character text values. If Excel turns one into 2108, the original code is no longer intact.
  • Keep formatting consistent. Decide whether you will use five-digit ZIP codes or ZIP+4 codes, and do not mix formats casually. A mapping service may not resolve ZIP+4 values the same way it resolves five-digit values.
  • Remove stray characters. Check for spaces, labels such as “ZIP:”, and inconsistent punctuation. A clean location column is easier to match.
  • Use numbers for the measure. Counts and rates should be numeric, not text with currency signs or explanatory words embedded in the cell.
  • Check the geographic scope. A postal code without a country can be ambiguous in international data. Include country information when relevant.

For a new import, use Data > From Text/CSV and set the ZIP column to Text in Power Query before loading the data. For a small manually entered list, format the destination cells as Text before typing or pasting codes. If a known five-digit ZIP has already lost its leading zero and is stored as a number, a helper formula can restore the expected display as text. With the damaged numeric ZIP in A2, enter =TEXT(A2,"00000") in a helper cell; if A2 contains 1234, the result is the text value 01234. Use that only when the source is known to contain five-digit US ZIP codes—do not use it to guess missing digits in ZIP+4 or international codes.

Create the Filled Map in Excel

  1. Arrange the source data in columns with headers, such as ZIP code and Orders. Make sure the measure column contains numbers.
  2. Select the headers and data rows you want to chart. If the data is in an Excel table, selecting a cell in the table can make later additions easier to manage.
  3. On the Windows ribbon, choose Insert > Maps > Filled Map. Excel may take a moment to resolve the locations through its mapping service.
  4. Review the plotted areas. If locations are missing or placed unexpectedly, check the source values and geographic context before changing the chart’s appearance.
  5. Add a descriptive chart title, such as “Orders by ZIP code,” and use Chart Design > Change Colors to select a readable color scheme.

The exact chart options can vary by Excel build and platform. On supported desktop versions for Mac, look for the Maps command on the Insert tab; if it is absent, check that Excel is updated and that the feature is available in that version. Excel for the web may not provide the same chart creation and editing options as desktop Excel. The steps above are for Microsoft 365 on Windows.

Choose a measure and color scale that make sense

A map is only as meaningful as the value being shaded. Raw counts can answer “Where did we receive the most orders?” but they do not answer “Where is the order rate highest?” A populous ZIP code may have more customers simply because more people live there. For comparisons of demand, risk, or service access, consider a suitable denominator—such as population or eligible households—and map a rate rather than a count. Make the denominator and time period clear in the chart title or a nearby note.

Use a single-hue light-to-dark palette when values run from low to high. A diverging palette can be useful when values have a meaningful midpoint, such as above and below a target, but it can confuse readers if the midpoint has no practical meaning. Avoid relying only on color differences that are difficult to distinguish; a clear legend, sensible contrast, and direct labels where practical help readers interpret the map.

Remember that a shaded map gives larger areas more visual prominence. A large, sparsely populated ZIP region can occupy more screen space than a small, dense one. The color represents the selected value, not the geographic area’s size or the number of people per square mile unless that is the metric you calculated.

Handle duplicate ZIP codes and multiple records

Source data often has multiple rows for the same ZIP code—for example, one row per order, customer, or month. A map should generally receive one summarized value per ZIP code for the chosen period. Otherwise, the chart may not communicate how repeated locations were combined, and duplicate locations can make the result confusing.

First decide what the value should mean. Add order amounts if the map is meant to show sales, count records if it should show order volume, or calculate a rate if you are comparing populations of different sizes. A PivotTable can summarize records by ZIP code; use ZIP code in Rows and the appropriate field in Values, then chart a clean summary range. Power Query’s Group By feature is another option for producing one row per ZIP. If you need results for separate months, create a summary for the selected month or make the time dimension explicit rather than silently combining all periods.

Also check for conflicting location records. The same ZIP may appear once as text and once as a number, or with different punctuation, causing what should be one group to split into multiple entries. Standardize the location field before summarizing. Keep an untouched copy of the raw data so that cleanup and aggregation choices can be reviewed later.

Know the limits of ZIP code mapping

Excel’s map chart relies on geographic recognition and map data; it is not a ZIP boundary editor. A location may be missing, generalized, or interpreted differently than expected. Postal geography is especially imperfect for statistical comparisons: USPS ZIP codes can describe delivery routes, while Census ZCTAs approximate areas for statistical use. A chart that looks precise is not proof that its boundaries match a particular official dataset.

For a report that must use a defined boundary vintage or meet an audit requirement, compare Excel’s output with an authoritative geographic source and document which geography and year you used. If you have official ZIP-like polygons, Excel’s standard Filled Map may not let you import and control those custom boundaries directly. A GIS application or a mapping platform that supports custom boundary files may be more appropriate.

Protect sensitive information, too. A map of totals by ZIP can still reveal patterns when counts are small or locations are sparsely populated. Aggregate or suppress values when appropriate for your organization’s privacy rules, and avoid publishing identifiable customer-level information. A geographic chart should communicate summary data, not expose individual records.

Troubleshoot blank, misplaced, or misleading regions

  • A ZIP code is blank on the map: confirm it is a valid, consistently formatted location; check for leading-zero loss, spaces, or ZIP+4 values; and add state or country context if needed.
  • Excel recognizes the wrong place: verify that the value is a postal code rather than an unrelated number. Add separate state and country columns to help provide context, then recreate or refresh the chart.
  • The map has no useful shading: check that the selected measure is numeric and that the source range includes the metric column. Text-formatted amounts may not behave like numbers.
  • Values look unexpectedly high: check for duplicate rows and confirm that the aggregation matches the question. Repeated transaction rows can make totals much larger than counts of unique customers.
  • The chart is too crowded: filter to the relevant region, period, or metric before charting. A map with every available record is not automatically more informative.

When uncertain, test a few ZIP codes whose locations you can verify independently, then compare the chart’s coverage with the source list. Do not treat a missing shape as a zero value: it may mean that Excel could not resolve the location, not that the measured quantity was zero.

When to use another mapping method

Use Excel’s Filled Map for a fast, shareable view of summarized values across recognized regions. It is convenient when the data is already in a worksheet and the built-in geography is adequate. A conditional-formatting heat map is a better fit if you only want to highlight high and low values in a table, without a map.

For point locations such as store addresses, coordinates, or individual events, a point map is usually more suitable than shading ZIP areas. Excel’s 3D Maps feature on Windows can visualize geographic data as points or other supported layers, but it is not the same as a Filled Map of postal boundaries. For custom ZIP/ZCTA polygons, strict boundary control, or more advanced spatial analysis, consider a GIS tool or a mapping service that accepts geographic boundary files. Choose based on the question and the required accuracy, not just on which chart looks most polished.

Frequently Asked Questions

Can Excel make a heat map from ZIP codes?

Yes. Use a Filled Map chart with a location column and a numeric value column. Excel must be able to recognize the postal locations, so check formatting and coverage if some areas do not appear.

Why does Excel remove the zero at the start of a ZIP code?

Excel may interpret a ZIP code as a number, and numbers do not retain leading zeros. Store ZIP codes as text when importing or entering them. If a known five-digit code was converted to a number, restore the zero only when you know the original format.

Can I map ZIP codes with sales or customer totals?

Yes, provided each ZIP has a meaningful numeric value. If the source has many rows per ZIP, summarize them first. Consider whether a count, total, or per-person rate answers your actual question.

Why are some ZIP codes missing from my Excel map?

Excel may not recognize the code, the value may be malformed or ambiguous, or the mapping data may not provide a corresponding region. Check the code, add state or country context, and verify against a trusted geographic source.

Is a ZIP code the same thing as a Census ZCTA?

No. A ZIP code is a postal delivery identifier; a ZCTA is a Census geographic approximation used for statistical mapping. They can differ, so use the geography required by your analysis or reporting standard.

Can I use ZIP codes in Excel for the web?

Web support for creating and editing map charts can differ from desktop Excel. If the Maps command or needed editing controls are unavailable, use the Microsoft 365 desktop app or a mapping alternative that supports your data and boundary requirements.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.