Heat Map in Excel for Crash Data: A Practical Guide

Errors & fixes · Updated November 21, 2026

Yes—you can make a heat map in Excel for crash data, but the right method depends on what you mean by heat map. Use conditional formatting to color a table or grid of crash counts; for a map that follows real streets and locations, use Excel’s 3D Maps where available or a dedicated GIS tool.

What a crash-data heat map shows

A heat map uses color to make concentrations or patterns easier to spot. In a spreadsheet, that might be a calendar grid showing crashes by hour and day of week, or a matrix of counts for geographic grid cells. Darker or warmer colors usually represent larger values, but the legend must make that meaning clear.

A geographic heat map is different: it places locations on a real map and shows where records cluster. Coloring cells in a worksheet does not, by itself, put those cells in their correct geographic positions. This distinction matters when someone asks for a crash heat map and expects a street-level map rather than a colored report.

Prepare the crash data first

Use one row per crash event if your goal is to count crashes. A useful table might include a unique crash ID, crash date and time, latitude, longitude, severity, and any fields needed to group or filter the results. Select the data and use Insert > Table so that headings and filters are available as the dataset grows. Keep one clear header row and avoid merged cells and subtotal rows inside the source data.

Before mapping, check that locations and records are suitable for analysis:

  • Remove or flag duplicate crash IDs rather than counting duplicate rows as separate crashes.
  • Check that latitude and longitude are numeric, in the correct columns, and within the expected area. A swapped coordinate pair can place records far from the study region.
  • Identify missing coordinates, invalid dates, and inconsistent severity labels. Decide whether to exclude, correct, or report these records separately.
  • Be explicit about what one row represents. A record per vehicle or person can inflate crash counts unless you deduplicate to crash events.

Keep an untouched copy of the source data, record any cleaning decisions, and note the date range and geographic area. A heat map is only as trustworthy as the data and definitions behind it.

Option 1: Make a geographic heat map with 3D Maps

In Excel for Microsoft 365 on Windows, 3D Maps can display geographic data on a map and includes a heat-map visualization. Arrange the crash records as a table, with latitude and longitude in separate fields, then choose Insert > 3D Map > Open 3D Maps. Add the location fields to the map layer and select Heat Map as the visualization type. Confirm that Excel has interpreted the coordinates as latitude and longitude rather than as unrelated numeric values.

This is the closest built-in option when the request is specifically for a map of geographic concentration. Review the result against known locations: automatic location interpretation or incomplete coordinate data can create misleading placements. The map is a visualization, not a substitute for checking the underlying records or validating geocoding. Excel 3D Maps is a Windows desktop feature; it is not available in Excel for the web or Excel for Mac, so users on those platforms need another approach.

For comparisons, use consistent date filters, map extent, and color interpretation. A view showing all crashes over several years cannot be fairly compared with a view showing one month if the color scale changes automatically. If your version or data does not offer the needed heat-map controls, use a grid-based summary or export the data to a GIS application.

Option 2: Build a geographic grid heat map

A grid-based heat map counts crashes within equal-area cells and colors the counts in a table. It is useful for a repeatable summary, although the resulting spreadsheet is not automatically a street map. For meaningful geographic cells, use projected coordinates measured in a suitable linear unit, such as meters, rather than treating latitude and longitude degrees as equal distances. Longitude degrees shrink in east-west distance as latitude changes, so degree-based bins can distort cell size.

For example, suppose each crash has projected X and Y coordinates in columns E and F, starting in row 2. Put the chosen grid origin’s X coordinate in H1, the origin’s Y coordinate in H2, and the grid width in meters in H3. With an illustrative origin of X=500000 and Y=4500000 and a 250-meter cell width, enter this in I2 to calculate the east-west bin:

=INT((E2-$H$1)/$H$3)

With those same assumptions and projected Y coordinates in F2:F, enter this in J2 to calculate the north-south bin:

=INT((F2-$H$2)/$H$3)

Copy both formulas down. For a crash at X=500620 and Y=4500370, the results are bin 2 and bin 1: 620 divided by 250 rounds down to 2, and 370 divided by 250 rounds down to 1. These are grid indexes, not latitude and longitude. Make sure the projection and origin are appropriate for the area; for large regions, seek advice on suitable spatial data handling rather than assuming one local grid works everywhere.

Create a PivotTable with the X and Y bin fields as column and row labels and the crash ID as a value summarized by Count. Then select the count cells and use Home > Conditional Formatting > Color Scales. Choose a scale whose light and dark ends are easy to distinguish, and include a legend or a clear note describing what the colors mean. If the PivotTable omits empty bin combinations, remember that an empty cell can mean no crashes were recorded there, while a blank caused by missing data means something different. If you need the grid aligned over roads or boundaries, make the map in GIS rather than treating the worksheet grid as a geographic basemap.

Make a time-of-day heat map

Crash records can also reveal temporal patterns. A PivotTable with weekday in rows and hour of day in columns can show whether crashes cluster at particular times. Add a count of crash IDs as the value, then apply a color scale to the result. This is often easier to interpret than a map when the question concerns commuting periods, weekends, or seasonal changes.

Keep time definitions consistent. Decide whether to group by the local time at the crash location, whether daylight-saving changes need attention, and how to handle records with missing times. Use the same date range when comparing locations or periods. Conditional formatting is effective here because the table cells already correspond to meaningful hour-and-day combinations; it does not require geographic coordinates.

Counts are not the same as risk

A location with many recorded crashes may have more traffic, more road length, or a longer observation period than a location with fewer crashes. Raw counts identify where events were recorded; on their own, they do not establish that a road or intersection is more dangerous. To compare risk, analysts may need an exposure measure such as traffic volume, vehicle miles traveled, or another appropriate denominator, along with a defensible method and reliable source data.

For example, a busy corridor could have more crashes simply because many more vehicles use it. A smaller road with fewer crashes could still have a higher rate relative to its traffic. Similarly, a hotspot may reflect a long study period, a temporary construction change, or better reporting in one area. Label a count map as counts, state its date range, and avoid describing it as a risk map unless the analysis actually accounts for exposure and other relevant factors.

Choose colors and filters carefully

Use a readable scale and make the high-value direction obvious. A sequential light-to-dark scale often works for counts, while a diverging scale is more suitable when values are compared with a meaningful midpoint, such as a difference from an average. Avoid relying on red and green alone because some readers have color-vision deficiencies. Add numbers, labels, or a legend so readers can interpret the colors without guessing.

Check how outliers affect the display. One unusually high cell can make most other cells look nearly identical under an automatic scale. You can use a fixed scale for comparisons across periods, but document the chosen limits and do not hide important values just to make a chart look smoother. Excel’s color scales show relative values in the selected range; they do not automatically explain whether a difference is practically important.

Common problems and how to avoid them

  • Wrong locations: Confirm coordinate order, coordinate system, and units. Do not assume every column labeled X or Y is longitude or latitude.
  • Misleading cell size: Degree-based bins are not uniform ground distances. Use an appropriate projected coordinate system for a local grid.
  • Inflated totals: Count unique crash events, not vehicle or person records, unless that is the stated measure.
  • Unequal comparisons: Use matching time periods and color scales when comparing maps or tables.
  • Unclear zeros: Distinguish a true zero-crash cell from missing or unavailable records.
  • Overclaiming: A cluster in a map is a pattern to investigate, not proof of a cause or a statistically significant safety problem.

Also consider privacy. Public crash datasets may include exact times and locations, and detailed records can sometimes reveal sensitive information when combined with other data. Follow the data provider’s conditions and organizational policies; aggregate or suppress details where needed before sharing a workbook.

When to use GIS instead of Excel

Excel works well for cleaning tabular data, making PivotTables, and producing quick summaries. A GIS tool is usually better when the analysis depends on road networks, intersection boundaries, spatial joins, distance calculations, map projections, or polished maps with a reliable basemap. GIS can also make it easier to define regular geographic cells and show them in their real locations.

For a small, clearly defined exploratory task, Excel may be enough. For a public safety report or a decision that could affect road design or enforcement, validate the data and methods with a qualified transportation or GIS analyst. A map can guide attention, but it should not be the only evidence used to make a safety decision.

Frequently Asked Questions

Can Excel make a crash heat map automatically?
Not from an ordinary list of crash records with a single click. Excel can color summary tables with conditional formatting, and 3D Maps on Windows can show geographic heat-map visualizations when the data and version support it. You still need to prepare and verify the location data.

Do I need latitude and longitude?
You need usable location information for a geographic map. Latitude and longitude are common inputs; a suitable projected X/Y coordinate pair can be useful for grid analysis. A date, street name, or postal code alone may require geocoding before precise mapping.

Can conditional formatting create a street map?
No. Conditional formatting colors worksheet cells based on their values. It can make a useful geographic grid summary if the grid is constructed appropriately, but it does not supply streets or place cells on a real-world basemap.

How can I compare crash hotspots fairly?
Start with comparable areas and time periods, keep color scales consistent, and state whether you are showing counts or rates. When the question is about relative safety, consider exposure data and appropriate analysis rather than relying on raw counts alone.

Is Excel better than a GIS program for crash mapping?
Excel is convenient for tables, filtering, and quick summaries. GIS is generally better for spatial operations, accurate map layouts, and analysis involving roads, distances, or geographic boundaries. Choose based on the decision and level of spatial accuracy required.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.