For a city-by-city heat map in Excel, put each city and its numeric value in a table, select the values, and choose Home > Conditional Formatting > Color Scales. If you mean colored locations on a geographic map, choose Insert > Maps > Filled Map; that creates a map chart, which is different from a color-coded table and may not show every city as a separate area.
What an Excel heat map by city shows
“Heat map by city” can mean two different things. The simplest is a table in which each city’s value is shaded according to its size: darker or warmer colors might represent higher sales, temperatures, or incident counts. Excel makes this with conditional formatting. It is flexible, quick to update, and works even when Excel cannot identify a city as a geographic location.
The other meaning is a geographic visualization, with areas on a map colored by their values. Excel’s Filled Map chart can display values for recognized geographic areas, but it does not guarantee a distinct colored shape for every city. City boundaries and geographic recognition depend on the data and map coverage. Also, a filled map colors areas; it is not the same as a point-density heat map that shows clusters around individual coordinates.
Choose the table when you need every city listed and compared precisely. Try a Filled Map when seeing geographic patterns is more important than displaying every row. You can also use both: a formatted table for exact comparisons and a map for a quick regional overview.
Prepare city data before making the heat map
Start with one row for each city and a numeric measure. A simple layout might have City in column A, State in column B, Country in column C, and Sales in column D. State and country are especially useful when different places share a city name. Keep the column headings in the first row, avoid merged cells, and make sure the values you want to color are actual numbers rather than numbers stored as text.
If your source has many transactions per city, decide what the map should measure before applying colors. For example, a list of orders is not yet a city-level sales summary. Add the sales amounts by city, or create a PivotTable to summarize them. A PivotTable is also useful when you want cities down the rows and months or product categories across the columns. The resulting grid can show how a city’s measure changes across time or categories.
- Use consistent city, state, and country names; do not mix abbreviations and full names without a reason.
- Check for duplicate city rows if the intended result is one value per city.
- Choose a meaningful measure, such as total sales, average temperature, or number of visits, and label its units.
- Keep missing values blank or mark them clearly. Do not replace missing data with zero unless zero is genuinely the correct value.
Aggregation affects the story the colors tell. A city with the most total sales may simply have more customers, while average sales per customer measures something different. State the measure in a heading or chart title so readers do not mistake one for the other.
Create a color-coded city table
For a basic heat map, arrange city names in one column and the measure in the next. If you have several measures or periods, make a grid with cities in rows and periods or measures in columns. Then apply a color scale to the numeric cells, not to the city names.
- Select the numeric cells, excluding the heading. For example, if the values are in D2:D40, select that range.
- On the ribbon, choose Home > Conditional Formatting > Color Scales.
- Choose a two-color or three-color scale. Excel immediately shades cells according to their relative values.
- Review the result and adjust the rule if the default colors or breakpoints do not suit the data.
For more control, open Home > Conditional Formatting > Manage Rules, select the rule, and choose Edit Rule. A three-color scale lets you specify a minimum, midpoint, and maximum, with values in between shaded along the scale. You can use numbers, percentiles, or other available threshold types. For example, a midpoint set to a meaningful target can make values below and above that target easier to distinguish than a scale based only on the dataset’s statistical middle.
Conditional formatting updates when the underlying values change, so the visual does not have to be recolored manually. If you add rows later, check that the rule’s “Applies to” range includes them. Converting the source range into an Excel Table with Insert > Table can make expanding data easier to manage, though you should still confirm the rule range after major changes.
Choose a color scale that communicates clearly
Color is not decoration alone: readers use it to judge which cities are high, low, or near a reference point. A light-to-dark scale is often suitable when values run from low to high. A diverging scale, with a neutral midpoint and different colors on either side, can work when the central value has a real meaning, such as a target or zero change. Avoid implying that a midpoint is important if it is merely the middle of the current data range.
Excel’s default scales are based on the selected values, so adding an unusually high or low city can change the appearance of every other cell. For comparisons across months, regions, or separate worksheets, use consistent thresholds where possible. Otherwise, the same color might represent different values in different tables. Include a clear heading and, if necessary, a note describing what low, middle, and high colors mean.
Check readability in grayscale and consider readers who have difficulty distinguishing certain colors. Do not rely on color as the only way to identify an important result: show the numbers, use labels, or add another visual cue. If blank cells represent unknown data, keep them visually distinct from cells with a real value of zero.
Make a geographic map chart
To try a geographic visualization in Microsoft 365 for Windows, select a clean range containing geographic names and the numeric measure, then choose Insert > Maps > Filled Map. Excel attempts to recognize the locations and create a chart with areas shaded by value. If the location is ambiguous, include additional fields such as state or country in the selected data. A city named Springfield, for instance, needs more context than the city name alone.
Once the chart exists, select it to use the Chart Design and Format tabs. The available chart controls let you adjust the map’s presentation, while the map series formatting options let you change the colors and how much of the map is shown. The exact options can depend on the selected chart and Excel build. A Filled Map is most useful when Excel recognizes the geographic units and those units have suitable map coverage. If a city is omitted or grouped unexpectedly, the chart may be resolving the name at a broader level or may not have a usable boundary for that city.
Map charts rely on geographic information services, so internet access and organizational privacy settings can affect whether locations are identified or displayed. Excel for the web and Mac may have different availability or controls from Microsoft 365 for Windows. If you do not see Maps on the Insert tab, check your Excel version, update status, and connected experiences or administrator settings rather than assuming a chart option is hidden in a different menu.
A Filled Map should not be presented as a street-level or exact-coordinate plot. It colors geographic regions, and the area’s size can influence how prominent it looks. If the goal is to show store locations, delivery points, or intensity around coordinates, a symbol map or a dedicated mapping tool may communicate the data more faithfully.
Handle repeated cities and changing data
Repeated city names are common in transaction-level records. Before mapping or coloring, decide whether to show individual records or a city summary. For an overall sales heat map, summarize all records for each city. For a monthly comparison, summarize by both city and month. A PivotTable can organize this: insert one with Insert > PivotTable, place City in Rows, place Month in Columns if useful, and place the numeric measure in Values. Confirm that the Values field uses the intended summary, such as Sum or Average.
Do not combine same-named cities from different states or countries unless they really belong together. Include location fields in the summary, and use those fields when supplying data to a map chart. For a continuing report, refresh the PivotTable after its source data changes. Then review the conditional-formatting range and chart source to make sure the new cities and periods are included.
Common problems and practical fixes
- Every cell looks nearly the same: The values may be tightly clustered, or one extreme value may be stretching the scale. Set explicit thresholds or use a percentile-based scale, and inspect the minimum and maximum.
- Some values do not change color: Check whether they are text, blanks, or outside the rule’s “Applies to” range. Convert text-formatted numbers into real numbers and edit the rule range as needed.
- A city is missing from the map: Add state and country context, check spelling, and make sure the value is numeric. If Excel still cannot map it, use the table heat map or another mapping method.
- Different cities appear to share one result: The source may contain repeated or ambiguous names. Summarize using city plus state or country rather than city name alone.
- The map changes scale unexpectedly: A new high or low value may have altered the automatic color range. Use fixed thresholds when readers need to compare separate reporting periods.
When troubleshooting, first check the source data, then the rule or chart settings. Reapplying colors without checking the values can conceal the real issue, such as a number stored as text or an accidental duplicate.
When to use another approach
A color-coded table is usually the best Excel option when the reader needs exact city values, a long list, or a comparison across several months. A Filled Map is a good companion when the geographic pattern matters and Excel recognizes the locations. For dense point data, custom boundaries, interactive filtering, or more precise geographic analysis, consider a mapping or business-intelligence tool designed for those tasks. The right choice depends on whether the key question is “Which city has the higher value?” or “Where are the high-value locations?”
You can also use a PivotChart or a regular column chart if ranking matters more than geography. A sorted bar chart makes small differences between cities easier to compare than a map, where area size and color can distract from the numbers. Excel does not need to force every geographic question into a map: pairing a map with a ranked table or chart often gives readers both location and precision.
Frequently Asked Questions
Can Excel make a heat map by city?
Yes. Use Home > Conditional Formatting > Color Scales to shade city-level values in a table. Excel can also create a Filled Map chart for recognized geographic areas, but city-level map coverage is not guaranteed.
How do I make a heat map for cities in different states?
Include separate city, state, and country fields in your source. Summarize repeated records at the city-and-state level, then use the numeric result for conditional formatting or supply the location context to a map chart.
Is an Excel Filled Map the same as a heat map?
No. A conditional-formatting heat map shades cells in a table. A Filled Map colors geographic areas. Neither automatically creates a point-density heat map from latitude and longitude coordinates.
Why does Excel not recognize my city?
City names can be ambiguous, and not every city has a supported boundary in the map data. Add state or country information, check spelling and data types, and use a table heat map if the location still cannot be identified.
Can I compare city heat maps from two different years?
Yes, but use the same measure and preferably the same color thresholds for both years. If each year uses its own automatic scale, identical colors may represent different values, making the comparison misleading.
Related
- Excel Consolidate Multiple Sheets: Steps and Options
- Excel Consolidate Multiple Sheets Into One: 4 Methods
- Excel: How to Consolidate Multiple Rows Into One Cell
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Day and Date Formats: Codes, Examples, and FixesIn Excel for Microsoft 365 on Windows, a date’s day and date format controls how its value appears,…
- Box Plot in Excel: Create, Format, and Read Box PlotsTo create a box plot in Excel for Microsoft 365 on Windows, arrange each group’s numeric observations in…
- Personal Distribution Lists in Outlook: Create and ManageTo create a personal distribution list in Outlook, open People or Contacts, choose the option to make a…
- Excel Web Shortcut Keys: Essential Commands and TipsExcel for the web supports keyboard shortcuts for editing cells, moving around a worksheet, selecting data, and formatting…
- How to Add Audio to a PowerPoint PresentationTo add audio in PowerPoint for Microsoft 365 on Windows, open the slide, choose Insert > Audio >…
- Excel Slicer Drop-Down: Options and Alternatives ExplainedNo—Excel for Microsoft 365 on Windows does not have a built-in setting to turn a slicer into a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.