A Heat Map Function for Your Excel Spreadsheet
More data does not produce more insights unless you have a way to analyze the data. In fact, that’s why there are hundreds of functions build into Excel and other spreadsheet software. The functions are what make up its most important functionality. Yet, you can’t use =HEATMAP to create a visualization of geographic density in your data. But what if that was almost as easy as an Excel function?
If you’ve spent any time using Excel, you’ve likely spent what feels like a similar amount of time searching the internet for help with its functions. You probably have come across many custom functions written by other Excel enthusiasts. Though the authors mean well, these sub-routines are often hard to use and have questionable security. For this and other reasons, BatchGeo made sense as a separate web service. We make Google Maps—and heat map overlays—as easy as copy and paste.
How to Make a Google Map
In our in-depth Google Map tutorial, we show some of the technical steps you get to skip when you use BatchGeo. Here’s how to create a basic marker map, which we’ll convert to a heat map in the next section:
- In your spreadsheet, highlight and copy (Ctrl+C, or Cmd+C on Mac) all rows, including your header row.
- Go to our map making tool and paste (Ctrl+V, or Cmd+V on Mac) your data into the box.
- Click “Map Now” and follow the steps to complete your map. We make intelligent guesses of location columns, but you can override them in the Validate & Set Options menu.
You’ll see a preview of your map and, if it looks good, you can save it. Include your email address so you can make edits in the future.
Now you have a map like the one embedded above. All the other data in the map is browsable by clicking individual markers. And we even get an idea of their density by the overlapping markers.
But it’s not a heat map. Not yet.
How to Make a Heat Map From Your Excel Spreadsheet
Now that you have a Google Map, you can easily activate the heat map layer with Advanced Mode available in BatchGeo Pro (30 day money back guarantee).
While signed in and viewing your map:
- Ensure Advanced Mode is enabled by clicking your account menu in the upper right.
- Right click (Ctrl+click on Mac) over your map and select “Heat View”
The markers on your map will disappear and be replaced by a heat map view.
You can zoom and pan your heat map as you would any Google Map. Our customers have used heat maps to gain valuable insights from open data, competition research, and more. Many have made decisions that saved or made their company thousands of dollars, or more.
Some example heat map use cases include:
- Map all places in a business category to determine potential new locations that are under-served.
- Map customers or leads to choose equitable sales zones or regions.
- Map a city’s violent crime to show hot spots for a comprehensive journalistic report.
It’s unlikely that Excel will soon have a =HEATMAP function of this caliber, so we’ve created our web service to fill this gap.
