Skip to content
June 21, 2013 / Erik Svensen

Using Bing Map App for Excel 2013 to plot break-ins in Denmark

Slowly we see more and more Apps for Office and one of the highest rated is the Bing Map App.

Here is a link to it – link.

The app lets you select values in Excel and plot them on Bing Map.

Let me just step through how you can do this.

First find some data to – in this case I went to http://www.politistatistik.dk/parameter.aspx?id=27 to extract information about crime reported in 2012 about Break in’s in private houses.

This will give a table like this

But in order to plot it we have to transpose this to a table format. So after a little bit of Copy, Paste Special – Transpose – we can get to this table

And we are ready to map the information – but first we need to activate the Bing Map App for Office – this is done via the Ribbon – Insert and Apps for Office.

If the app isn’t listed under My Apps – you can use the “Find more apps at the Office Store” and search for it.

When you insert you might get this warning.

And when you click Start – you will be welcomed by the App.

Select the table of data and click the Icon – this will plot the selection on the map (please notice that it has a max of 100 points to plot)

Then you will see the municipalities plotted –

But our data has two problem – apparently Denmark has municipalities in USA and Italy.

This is because Bing Maps needs a little bit more information to plot Ringsted – Denmark instead of Ringsted, IA – luckily it’s very easy to fix.

If you modify the label to plot – from Ringsted to Ringsted, Denmark or Ringsted, Danmark – Then we will help Bing Maps to plot correctly.

And voila – all plotted correct in Denmark – and then you can start to play around with the settings and filters.

If you add more columns with data you can plot pie charts on the map – really cool.

Two more tips –

If you have the latitude and longitude – you can use that to plot by

Advertisements

2 Comments

Leave a Comment
  1. ipeefreelyagain / Apr 4 2014 5:06 am

    How can you edit the label / name of a lat/long point? And can you set the default focus of a plotted item to show the details as if you clicked it?

    • Erik Svensen / Apr 7 2014 12:03 pm

      Hi,

      It is not possible to edit the label – it takes the first column and the headers for the value fields after the location field.

      And your second question – You cannot set the default focus.

      A possible workaround could be to make a list with one item and make lookup formulas to find the item you want to plot in the list.

      Example file here –

      BR
      Erik

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: