Excel Power Map 1

(this was originally published in the old MapPoint section of the Mapping-Tools.com HowTo pages)

The Microsoft Excel Power Map is a new mapping extension for Excel 2013 and Office 365, and is a part of the Excel PowerBI (“Power Business Intelligence”) extensions. These have now been released as a part of the Excel 2013 Service Pack 1. Note that you must have Excel 2013 installed and have a subscription to Office 365.

Population heat map produced with the Power Map Preview.

Population heat map created with the Power Map Preview. Population is mapped by counties which results in a relatively granular plot. (Click for larger image)

Excel Power Map is an extension that lets you plot your Excel-based geospatial data on a map. Bing Maps is used for the base map which is typically drawn in 3d. The base map can be drawn as a sphere or a plane. Power Map plots data value(s) and with the exception of pushpin datasets, has basic functionality comparable to most of the MapPoint Dataset Types.  Four map types are supported:

  • Column. A simple vertical bar proportional to the data value. Multiple variables can be stacked one on top of another.
  • Bubble. Equivalent to a MapPoint sized pie chart. Single values are plotted as sized circles. Multiple values are plotted as a sized pie chart.
  • HeatMap. A much requested map type for MapPoint.
  • Region. Choropleths, or shaded area maps in MapPoint terminology.  Often mistaken for “heat maps” by some MapPoint users.

The column, bubble, and region types do a good job of interpreting multiple variables. For example, columns will stack the data vertically, and bubbles will insert “pie slices”. The region type will add a color dimension for each variable, with the highest value variable choosing the color. E.g. variable A might be shades of red, and B might be shades of blue. The final color (red or blue) will be determined by which variable has the greatest value (see the shaded area election map as an example).

Shaded area map of 2012 election results produced with Excel Power Map Preview

Region (shaded area) map showing Texas and Louisiana in the 2012 presidential election. Created with Excel Power Map Preview. (click for larger image)

Power Map is also very good at geocoding. The developers claim “one click map plotting”. This is an exaggeration, but in my experience (with admittedly good, formatted data), the geocoding has been a “one click” experience.

Sized pie chart map produced with the Excel Power Map Preview

Bubble (sized pie chart) county map of the 2012 US Presidential Election produced using Excel Power Map Preview. (click for larger image)

Parameters tend to be mixed. There are some very flexible parameters, but there are also some obvious gaps. This may be due to the product still being in development. For example, column and bubble shapes can be varied (square, circle, star, etc) but you can only control the data scale with a simple multiplier. A logarithmic scale is sorely missing. Similarly, there are a lot more colors than MapPoint provides, but any one data parameter is limited to shades of that one color. There are no multi-chroma color schemes. The best way around this is to split your data into different variables according to your required chroma. This works well for election data (see the attached screenshot) but will not be possible for some data types.

Additional functions include layers (another much-requested feature for MapPoint) and support for different Bing Maps themes.

Annotation and additional overlays are limited to legends, 2d charts (e.g. graphs plotting min/max summary data), and text boxes. Power Map does not support shape overlays that could be used for territories and other annotation.

Overall, Power Map makes it easy for Excel users to quickly produce attractive maps for their geospatial data. The requirement for an Internet connection and an Office 365 subscription will definitely be seen as disadvantages by many users. However for MapPoint users who only need to plot geospatial data variables, Power Map for Excel is going to be a significant option.

A powerful offline alternative would be Caliper Maptitude. Although it is not as closely integrated to Excel, Maptitude supports all of MapPoint and Powermap’s data plotting options out-of-the-box. It can plot Column Maps (Prism Maps), Bubble Maps (pie charts), Heatmaps, and Regions (area layers); and does not require a subscription or an Internet connection.

One comment on “Excel Power Map

  1. Reply Drew B Dec 13,2018 10:10 am

    Thanks for this rundown.

    I’m seeking a solution to make a Region visualization that acts as a heat map. That is, plot the location data as regions and shade on a multi color scale to show value range. If I choose “Range,” I’m only given the option for a single color layer filter which isn’t showing the data well. Do you know a way to get the Heat Map visualization within the Region?

    Thanks in advance!

Leave a Reply