To make a U.S. state heat map in Excel for Microsoft 365 on Windows, put each state and its numeric value in separate columns, select the data, then choose Insert > Maps > Filled Map. Excel shades each state according to its value; this is a map chart, not the worksheet’s usual cell-based conditional-formatting heat map.
What a State Heat Map Shows
A state heat map is usually a choropleth: geographic regions are filled with different colors to represent a measure. In Excel, a Filled Map chart can shade U.S. states according to values such as sales, population, survey results, or service requests. A darker shade might mean a higher value, but the legend and chosen color scale determine what the colors actually mean.
The chart is useful when the geographic pattern matters more than precise comparisons between individual numbers. It can quickly reveal clusters, regional gaps, or unusually high and low values. It is less suitable for showing exact locations within a state, routes, or several unrelated measures at once. A state map colors whole states; it does not place points at cities or addresses.
Excel needs to recognize the labels as geography and connect each number to the correct region. For that reason, tidy source data and clear state names are more important than adding elaborate chart styling. A map that looks attractive but assigns a value to the wrong state is misleading.
Prepare State Data Before Inserting the Map
Use a simple table with one row per state, a header for the state field, and a header for the measure. For example, a sales table might have State and Sales as its column headings, followed by rows such as Oregon and 125000, Texas and 310000, and Vermont and 42000. Those figures are illustrative, not typical sales benchmarks.
- Use full state names when possible. Two-letter postal abbreviations may be interpreted inconsistently, especially without a country or region field.
- Add a Country column containing United States if the data could be ambiguous. This can help distinguish a state name from a place with the same name elsewhere.
- Keep the value column numeric. Text such as $125,000 copied into a text-formatted cell may not behave like a number.
- Use one record per state. If you have monthly sales by state, summarize the months into one state total or make a separate map for each period.
- Remove blank rows, merged cells, and explanatory notes from the selected range. Keep headings in the first row.
Names such as Georgia and Washington can refer to places outside the United States, and some locations share names. Including a country field and using full names reduces ambiguity. If a state is absent because it has no activity, decide whether that means zero or missing data before charting. Zero is a real measured value; a blank usually means the value is unknown or not supplied. Those should not be treated as equivalent.
Create a Filled Map Chart in Excel
- Arrange the data in adjacent columns with clear headings, such as State and Revenue.
- Select the headings and all state rows. If the range is an Excel table, clicking within it may be enough, but check that the chart includes the intended columns.
- On the ribbon, choose Insert > Maps > Filled Map. Excel creates a map chart and attempts to match the geographic labels.
- Check the map against the source data. If the chart has an unexpected location, revise the labels or add a Country column rather than assuming the match is correct.
- Use the chart controls or the Chart Design tab to choose a layout and colors. Add a descriptive title, such as Revenue by State, so readers know what the values represent.
Ribbon wording or placement can vary slightly with Microsoft 365 updates. If Maps is unavailable, check that you are using a supported, updated Excel desktop version and that the workbook is not open in a restricted mode. Excel for the web and Excel for Mac can have different feature availability or ribbon layouts; the Windows path above is for Microsoft 365 desktop on Windows.
Excel map charts rely on online geographic mapping services to identify and display regions, so an internet connection may be required. If the chart cannot resolve locations while offline, reconnect and try again. Avoid using sensitive information in a map chart unless your organization’s policies permit use of online connected features.
Format the Colors, Labels, and Map View
Color is the main encoding in a filled map, so use a scale that makes comparisons understandable. A single-hue progression, from a pale shade for low values to a dark shade for high values, is often easier to read than several unrelated colors. On the ribbon, Chart Design > Change Colors offers chart color choices. The available options can vary with the chart and Excel version.
For more control, select the map and open the Format Data Series pane, then review the series color and map options available for that chart. A two-color or three-color scale can emphasize low, middle, and high values. If you use a midpoint, choose one that has meaning for the data, such as a target or a meaningful threshold, rather than selecting a dramatic center just for appearance. Keep the legend visible when readers need to interpret the shades.
Map labels can help identify states, but too many labels may crowd the chart. Use the map’s label options when available, and consider showing only the labels Excel can fit clearly. A concise title and a visible legend often provide more value than labels on every state. If the chart is intended for a report, check that small states and light fills remain visible at the final display size.
Be cautious when comparing two maps. If Excel automatically scales each map independently, the darkest state on one map may represent a different value from the darkest state on another. To compare periods, keep the same scale limits and color meaning wherever the chart options allow. Otherwise, a visual change may reflect rescaling rather than a real change in the underlying measurements.
Read the Map Without Overstating the Pattern
Suppose an illustrative dataset gives California 180,000, Texas 150,000, and Delaware 12,000 in annual orders. A map can show that the first two states are among the larger totals, while Delaware is lower. It does not show why the totals differ, whether the states have similar populations, or whether the figures are high relative to the number of customers in each state.
Raw totals often favor states with larger populations, more stores, or more customers. If the question is about performance per person or per location, calculate an appropriate rate in the source data and chart that measure instead. For instance, orders per 1,000 customers can support a different comparison than total orders. State values should use comparable definitions, time periods, and units before they are mapped.
Also remember that a state fill summarizes an entire state. It can hide major differences between urban and rural areas. A state-level pattern is not evidence that every county or resident in that state has the same outcome. Use the map as an overview, then consult a more detailed geographic analysis if the decision depends on local variation.
Fix Common State Map Problems
- A state is missing: Check for a typo, blank value, or a label Excel did not recognize. Replace abbreviations with the full state name and add United States in a Country column. Confirm that the state has a numeric value if the chart is expected to color it.
- Excel maps a name to the wrong place: Add a country or region field and use a more explicit geographic label. Do not rely on a short or ambiguous name when the correct location matters.
- Every state has the same color: Confirm that the values are numeric and that there is meaningful variation. If the numbers are stored as text, convert them to numbers. Also inspect the legend and series color settings.
- The chart shows a broad area rather than individual states: Check that the data contains state-level geography rather than only a country name. Mixed geographic levels can confuse the intended display.
- Duplicate state rows create an unclear result: Summarize repeated rows to one value per state before mapping. For example, combine monthly or store-level records into the intended state total or rate.
- The map will not load: Check the internet connection and Excel’s supported connected experiences or privacy settings. Organization-managed settings may disable online features.
When values must be combined, use a PivotTable or another explicit summary method so the aggregation is visible and reproducible. Decide whether the correct result is a sum, average, rate, or another statistic; choosing the wrong aggregation can produce a polished but incorrect map.
Map Chart Versus Conditional Formatting
Excel uses the phrase heat map for two different visual approaches. A Filled Map chart colors geographic regions such as states. Conditional formatting colors worksheet cells, often with a color scale. A conditional-formatting heat map is useful for comparing a grid of values, but it does not know that a row labeled Ohio corresponds to Ohio’s geographic boundary.
For a cell-based heat map, select the numeric cells and choose Home > Conditional Formatting > Color Scales. This applies colors to the cells themselves. It is handy for a state-by-month matrix, where rows might be states and columns might be months. A map chart is the better choice when the reader needs to see where values occur geographically.
Other alternatives may fit specialized needs better. A PivotChart or ordinary bar chart can make precise rankings easier to compare, especially when many states have similar values. A dedicated geographic information system can provide more detailed boundaries, spatial analysis, and local-level maps. Excel’s Filled Map is a convenient overview, not a replacement for every mapping or statistical task.
Frequently Asked Questions
Can Excel make a heat map of U.S. states?
Yes. In supported Excel desktop versions, select state names and numeric values, then choose Insert > Maps > Filled Map. Excel uses the values to color the recognized state regions.
Why is my state heat map blank in Excel?
Common causes include unrecognized place names, text-formatted values, missing data, or a disabled connection to the online mapping service. Try full state names, add a United States country field, verify numeric values, and check your connection.
Can I use state abbreviations in an Excel map?
Sometimes, but abbreviations can be ambiguous or fail to match. Full state names are generally a safer starting point. Add a Country column when the geographic context is not obvious.
Can I make a state heat map without a map chart?
Yes, but it will not be a geographic map. Home > Conditional Formatting > Color Scales colors selected cells and works well for a table or matrix. Use a Filled Map when the state boundaries themselves need to be visible.
Why do two state maps use different shades for the same value?
The charts may be using different color-scale limits or automatic scaling. For fair comparisons, use consistent scale settings and the same measure, units, and time period on both maps.
Related
- Repeat Print Area in Excel: Repeat Rows on Every Page
- Remove Text to Columns in Excel: Undo or Reverse It
- Remove Flash Fill in Excel: Clear Results or Turn It Off
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add Two Different Page Numbers in WordTo use two page-numbering styles in one Word document—for example, Roman numerals for a contents section and regular…
- Excel Insert Page Break Based on Cell Value?Excel has no built-in setting or worksheet formula that automatically inserts a page break when a cell contains…
- Creating Rules in Outlook: Setup and TroubleshootingTo create a rule in Outlook, open the Rules controls, choose a condition such as a sender or…
- Create a Distribution List in Outlook 365In Outlook 365, create a personal distribution list by opening People, choosing New contact list, naming the list,…
- Excel Remove Duplicates From Multiple Columns: StepsTo remove duplicate records by comparing multiple columns in Excel for Microsoft 365 on Windows, select the full…
- Data Validation Shortcut Key in Excel: Windows and MacIn Excel for Microsoft 365 on Windows, press Alt, then A, then V, then V to open the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.