Interactive Maps in Excel 2013/2016

By David H. Ringstrom, CPA

You’re as little as three mouse clicks away from creating an interactive map in certain versions of Excel. As you’ll see, the free Bing Maps app in Excel 2013 and Excel 2016 for Windows and Excel for iPad makes mapping customers, project locations, and other address-based information a snap. Unfortunately this feature is not available in Excel 2010 and earlier or on any version of Excel for Mac.

Figure 1 shows a typical address list in columns A through E. However, Bing Maps require addresses to be combined into a single column, which I was able to do with concatenation. Excel does offer a CONCATENATE worksheet function, but you’ll only ever hear passing mention of it in my CPE Link webcasts. Instead I use the ampersand to combine cells and text together, as illustrated by the formula in cell F2 of Figure 1.

Figure 1: Addresses you wish to map must appear in a single column that starts with a heading.

The formula uses the ampersand to combine cells together, much like we use the + sign to add numbers. I needed to include spaces and commas, which I enclosed in double-quotes. The TEXT function ensures that leading zeros aren’t inadvertently dropped from my ZIP codes. To create the map itself:

1. Select the addresses (in this case cells F1:F6—make sure the first row is a column heading and not an address).
2. Choose Bing Maps in the Apps section of the Insert menu in Excel 2013 and later.
3. Click the first icon in the top right-hand corner of the map to display your points.

Figure 2: The free Bing Maps app allows you to create interactive maps within Excel.

You can now click on points to view the name and address, as well as zoom in and out of the map. Controls along the top allow you to zoom in and out as well as filter the map itself. Maps you create in Excel 2013 and later can be viewed in earlier versions of Excel, but will appear as static pictures without any interactivity.