Making Maps With Excel

There is a huge amount of untapped geographic information stored within Microsoft Excel spreadsheets. With over  one billion users of the popular spreadsheet software, that is a lot of data waiting to be mapped out.   Customer rosters, delivery routes, and store locations are just a few examples of the types of geographic information residing with Excel spreadsheets that could benefit from visualization through maps.

[accordion]
[toggle title=”Disclosure Statement”]GIS Lounge is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to amazon.com. Amazon, the Amazon logo, AmazonSupply, and the AmazonSupply logo are trademarks of Amazon.com, Inc. or its affiliates.[/toggle]
[/accordion]
Unfortunately, Microsoft removed the capabilities of visualizing spreadsheet data using maps via its native map function starting with Office 2000 (for step by step instructions for making a map with older versions of Excel, read the Creating Simple Maps with Microsoft Excel article).  Mapping functionality for Microsoft Office product is now only available by purchasing MapPoint, Microsoft’s mapping solution.  Given the price of MapPoint, users often opt for the solution of exporting the data and using an external GIS or mapping program to map out the geographic data.

That said, the capability of mapping out that geographic data doesn’t necessarily need to be done through an external desktop GIS software application.  There are a few solutions on the market that provide both free and cost-based mapping solutions that operate directly within Excel.  The benefit of retaining and mapping the data directly within Excel means that any changes to the spreadsheet can be immediately reflected in an updated map.

MapCite provides a free version of its Excel add-in (the company’s offerings were profiled in an article written in March of 2012 entitled MapCite: Web Mapping and Excel Location Intelligence Applications).  The extension will geocode and map geographic data directly within Excel.  The base map and geocoding engine are provided via Bing so an Internet connection is required for mapping data (MapCite is a Microsoft Business Partner).  Mapped data stays linked with the spreadsheet data so data highlighted on the map highlights the mapped location and vice versa.  Multiple spreadsheets can be mapped and the data can be easily filtered to map out a subset from each spreadsheet.  Heat maps can easily be generated.  Spatial selection (e.g. selecting data based on a highlighted section on that map) is also one of the available functions.  The plugin works for Excel versions 2007 and later.   Over 1,000 users representing a mix of GIS and non-GIS customers are using the MapCite Excel mapping addin.  A commercial version is available for those customers with high load mapping needs within Excel.

Esri recently launched Esri Maps for Office allows for the mapping of geographic data within Microsoft Excel in correspondence with its ArcGIS Online service.  Point locations can be geocoded and custom areas can also be mapped by streaming data uploaded to ArcGIS Online.  Heat mapping, filtering subsets of data, and sharing both static and interactive maps with other Office products make up part of the functionality of this add-in.  A thirty day trial period is offered for users to try out ArcGIS Online which includes a download to for the Excel add-in.  Read the Excel Mapping with ArcGIS Online article for more detail.

BeGraphics also offers a free “lite” and commercial version of its Excel and PowerPoint mapping addin.  The free version has a data size limit of 1oo “shapes” and a further comparison of the limitations of the free version with the purchased version can be viewed on the pricing and order page.  Both download versions come with 10,000 maps from the BeGraphics library.  The examples form BeGraphics site indicate the addin is more for thematic mapping and mapping is done by relating the geographic data to a place such as a state or a country.  There doesn’t appear to be a geocoding engine so mapping data at the street is not an option.  Maps, however, can be customized, which allows users the ability to “build your own geographical data visualization (custom map, sales territory, housing scheme, theater seats booking, plane statistics occupancy, warehouse usage, building services and maintenance, shipyard’s assembly planning…).  This would be useful for instances where the spatial visualization need doesn’t call for a standard map.

If you have straight forward state or country based data that you are looking to map, Mapland offers a straightforward mapping addin that works with Excel versions 2007 and 2010.  The program is backwards compatible and  works with any version of Excel dating back to Excel 97.  The basic version (current priced under $60 with the currently offered discount) comes with geographic workbooks covering the states of the United States and the countries of the world by continent.  More detailed geography such as the counties of the U.S. or ZIP Codes need to be purchased separately.  The professional version of Mapland includes all available geographic workbooks.  Demo versions are available for downloading.

If you do have a single spreadsheet that needs a one time mapping and don’t want to invest the time in downloading and learning a new Excel addin for mapping that data, try one of the free web based spreadsheet mapping services that are available.  Batchgeo is a popular option that allows users to copy and paste spreadsheet data from Excel and map out geographic data onto a Google Maps base.  Up to 3,000 records can be mapped using the free version and a KML file can be downloaded from the finished results.  Additionally, an online map of the results is made available which expires after 60 days of non-use.

Share this article