How to make a killer map using Excel in under 5 minutes with PowerMap plugin

22

Power Map 12 - Geoawesomeness

Many times it happens that you need to quickly visualize your data on a map but you don’t have a time to play for hours with ArcGIS or QGIS to make it look good. In this tutorial I’ll show you how make an awesome looking map in Excel in under 5 minutes with a free plugin called PowerMap (formerly GeoFlow).

1. Download and install the plugin.

Of course the first step would be to download and install the plugin from Microsoft website. You should download it and install without any issues. Power Map 1 - Geoawesomeness Initially the plugin was restricted only for MS Office 365 subscribers and owners of an expensive MS Office Professional Plus suite but recently it has been released for all MS Excel 2013 users. Both 32bit and 64bit Windows version are supported but it will work only with Excel 2013 (no previous Excel version is supported). It won’t work on any other operating system though. If you successfully install the plugin, you should be able to see it in your Excel under the “INSERT” tab. Power Map 2a - Geoawesomeness If you don’t see it you might need to activate it in Add-Ins menu. To do that go to File -> Option -> Add-Ins. You should the following window: Power Map 3 - Geoawesomeness In the dropdown list on the bottom of the window select COM Add-ins and click GO. Now select Microsoft Power Map and click on OK button to activate it. Power Map 4 - GeoawesomenessNow everything should work fine. The most difficult part is behind us:). Let’s start mapping.

2. Prepare your data.

Now its time to prepare your data. Power Map gives a couple of options to visualise it. The most obvious is to use coordinates. In this method you need to have two separates columns for latitudes and longitudes. There are a couple of cool things you can do with that and we will cover it in the future #GeoawesomeHoTo. Today lets focus on another great option of the plugin – geocoding.

Geocoding is a process which allows you to pinpoint a geographic location based on a description e.g. address, city name, postal code, country name etc. For the purpose of this tutorial I took a list of the largest agglomerations by population from Wikipedia. Selected the table in a web browser and copied it directly to Excel. Than I pressed “Text to Columns” button in the DATA tab and I came up with the following table.

Power Map 5 - Geoawesomeness

Geocoding will allow me to show a phenomena on a map based on a city or a country name. It will automatically connect the record to a centroid of a city or a country without a need to give a geographic coordinates.

This is it. Now we can start mapping:).

3. Select you data.

Now you just need to select the data you want to map, in my case it would be the whole table, go to INSERT tab, and click on a “Map” icon, which we’ve added to the menu at the beginning of the tutorial. Click on “Launch Power Map” and here we go.

Power Map 6 - Geoawesomeness

4. Create a map.

The Power Map window will pop-up on your screen. Below you can see that plugin automatically detected two parameters from our table “City” and “Country”. Based on these two columns Power Map will join the name to a centroid of a Country or a City on our list. In that case we are interested in visualizing the data for cities, so we should select only this option.

Power Map 7a - Geoawesomeness

After choosing “the geography” click on the NEXT button in the bottom-right side of the page. You will be moved to the settings menu. Now you need to select which data you want to visualize. In our case we’ve got only the Population column, so we should select it. Below that there is a menu to select how you want to visualize the data. There are 5 options:

  • Stacked Columns
  • Clustered Columns
  • Bubble
  • Heat Map
  • Region

As you can see on the example below I’ve selected the Bubbles.

Power Map 8 - Geoawesomeness

Now it’s time to play with the visualization and Power Map gives plenty of cool features to do that. You can Flat the map, you can select one of the predefined map themes, add text description (via Text Box) and so one. You can also play with the data visualization. Under settings menu on the right side you can change the opacity, size and thickness of your data points. You can also select a color. The size of the bubbles will change with the map scale so you can select “Lock current scale” if you want to keep make them look the same when you zoom in or zoom out.

Power Map 9 - Geoawesomeness

5. Save the map

Our map is ready! We can present it directly in Power Map to maintain the interactive features or we can save it as a picture. To do that we just need to press the button “Capture Screen” in the main menu and paste (Ctrl + V) the picture to any graphical editor like Paint or a Photoshop.

Here we are. Awesome looking map of the population of 75 largest cities in the world in under 5 minutes! Have fun!

Power Map 12 - Geoawesomeness

Aleks Buczkowski

I’m a professional always thinking outside the box and a self-confessed gadget addict. As a son of a professor of cartography I was surrounded by maps all my life and as a result spatial way of thinking and seeing reality is naturally embedded in who I am.

  • great tool! one question: can you export this map as a webmap too?

    • Aleks Buczkowski

      No unfortunately there is no way to do it. It’s a simple tool, but it allows for creating maps very quickly. Hopefully Microsoft will keep working on it.

    • scott ball

      Hey Riccardo – if you want to publish as a webmap you should check out Esri Maps for Office. It does most of the same things PowerMap does – and more!

      • Aleks Buczkowski

        Hey Scott. There is one problem. ArcGIS for Office is not free. You need to be a paid subscriber (or trial) of ArcGIS online.

        • scott ball

          In that case, PowerMap is not free either – you have to buy Microsoft Office to gain access to it! But it’s true, you need to have a connection to ArcGIS Online (or ArcGIS server through Portal for ArcGIS) to use Maps for Office. At any rate, Maps for Office will do what Riccardo was asking about.

          • Michael

            BaseMaps.com allows for free maps and then you can pay if you want to embed.

  • Jonathan Kleine

    awesome gadget! question: can i scale the Legend and where can i change the color of the map?

    • Aleks Buczkowski

      Hi Jonathan, Yes you can scale the legend using the round elements on the legend window (similar to the one on the Text window on attached image). You can also change the Theme of the map in the main top menu under “Themes”. You can also change the color of symbols on the map. Check out the last picture under the point #4 of the tutorial. There is an option on the side menu to do that.

  • Cody Bizrown

    Fantastic Find!

  • Pingback: Digital Geography()

  • Pingback: How to make a killer map using Excel in under 5 minutes with PowerMap plugin | Geo-How-To News()

  • Jonathan Kleine

    is it possible too draw lines between two cities for example flight routes?

    • Aleks Buczkowski

      Not really. You could try to make the lines in GIS software, export a line to points (e.g. with ET tools in ArcGIS) and display the lines as densed “cloud of points”. I don’t see any other option.

  • Pingback: Recommended Reads #51 | Small Pond Science()

  • John F. Kelly

    Looks nice!

    • Aleks Buczkowski

      Thanks John:)

  • Ric

    Hi, Alex, impressive tool and thanks for explaining. I downloaded it (have Excel 2013 standard) but in the add-in list it didn’t show Microsoft Office Power Pivot for Excel, instead, only Microsoft Power Map. I added it and strangely enough only the former is now active, not the maps! I’m really dying to get this done for an ongoing project. Is the Bing Map add-in interfering (besides, i would have used this one, but it won’t work properly). Could you please help? Thanks!

    • The Swede

      Same here. Excel says it’s activated, but nope. I have Professional.

      • The Swede

        Think I found it. “Note that you must have Excel 2013 installed and have a subscription to Office 365.”

  • Visha Vj

    Hi Aleks,
    Thank you for that tutorial! I used it to map my online store’s customer data on a map by how much revenue we got from each city in America. Super cool data visualization!

  • Andrew M Wigmore

    My company, Visual Crossing Corporation, has released a new Excel add-in for creating rich, interactive maps directly inside Microsoft Excel. It provides integration with Google Maps, Bing Maps and Open Street Maps as well deep geospatial analytics for the more advanced user. For more information please see http://www.visualcrossing.com/excel

    Thanks
    Andrew
    Visual Crossing Corporation
    http://www.visualcrossingcom/excel

  • Eric s

    I just found this blog and love this post. I’m with a few other….If I don’t have Professional Plus 2013 and just have Professional 2013 then can’t do this right? I have the add-in for Power Map but not for PowerPivot.

    I’m in a natural resources and work large landowners and would to have a tool like this but it seems I’m out of luck without Professional Plus?