How to Avoid the Limitations of Creating Maps in Excel

The ability to create maps in Excel is one of the useful features of the signature Microsoft Office spreadsheet software. However, there are notable limits to what you can do with a map chart in Microsoft Excel. After all, it's a data analysis tool primarily focused on the simple but effective spreadsheet format – it was never meant to be a mapping method.

What you need instead is a comprehensive tool for sales territory mapping like eSpatial. In this post, we'll cover the mapping functions – and limitations – in Microsoft Excel. We'll also go over how you can better leverage your Excel data for geographic analysis by using eSpatial's heat mapping features.

Can you create a map in Excel?

The best answer to this question is "yes and no." What you end up making is a "map chart", which is an insert in the middle of an Excel sheet adjacent to one of the tables on it.

The feature, found in the Insert menu of Excel, culls data from a selected cluster of cells. One of the columns must be geographical data points, like names of countries or cities. The other column will usually be metrics relevant to the locations listed, like population, tax revenue, gross domestic product, unemployment rate and so on. When you select Maps and then Filled Maps from the Insert menu of Excel, the data will illustrate as a small map. You can adjust formatting in some ways, including map projections, area, labeling and color-coding.

What are the major limitations of Excel map charts?

The first, most obvious limit of Excel's mapping ability is that the feature simply isn't available in more than a few early versions of Excel. It's there if you have Office 365, but if your organization doesn't use Office regularly (opting instead for Google Suite, as just one example), you might only have an older version of Office. The 2007 version is more common than you'd think in 2021, and it lacks the mapping function.

Even in the most up-to-date versions of Excel – the 365 module, or Excel 2019 (which is soon to be replaced by Excel 2022 later this year) – a number of significant shortcomings are still present. Perhaps above all, Excel can't use datasets like street addresses and latitude/longitude coordinates to create map charts. It's limited only to geographic locations, nationwide data points (like unemployment rate) and other high-level data.

Sales reps and their managers need to be able to use a much wider range of variables than that. By contrast, in a platform like eSpatial, two datasets are the bare minimum needed to create a map of territories or other areas. You're not only able to use more data points for your sales maps when using eSpatial, you're essentially encouraged to do so. Make them as detailed as necessary!

Creating superior heat maps using eSpatial instead of Excel

You can use eSpatial to generate a number of highly detailed maps to improve your territory management, route planning and campaign creation.

A heat map is one of the best examples of what you can do in eSpatial with your data sets. This may be a standard heat map, representing areas of density or surges in other values, or a regional heat map, which summarizes such data within a predefined area such as a country, state, county or ZIP code.

To make a heat map, follow these steps:

• Sign up for eSpatial (either the full service or as a trial) and log into your account.
• Upload your datasets from Excel and/or other tables you're using.
• Click one of the sets as shown within the Control Panel, select Style & Color and choose the Heat Map option.
• Customize map features, including heat map range, transparency and radius distance.
• Use the Overlay Pins option if you want to see the original mapped data as pins atop the heat map.
• Save the map to finish.

For a regional heat map, the process is slightly different:

• Log in to your account and upload data.
• Under the Control Panel, once you select the data set, click Analyze (instead of Style).
• Select the Regional Heat Map option.
• You'll then be asked to indicate your points dataset (the one you just uploaded) and the regional data for comparison. (A drop-down menu will show which regions are available for mapping; find the list of available regional data sets here.)
• After finding and selecting the right region, click Complete, and you're all set.