(The video for this module is available below.  I’m writing the accompanying text and I’ll make it available pretty soon.)

Transcription

There are some creative ways to add a simple maps to your excel dashboard. In the ExcelCharts blog search for a tutorial on how to make a map using shapes.

The map we are going to add is nothing more than a scatter plot with a background image. The techniques we applied when making the Dependencies scatter plot will be repeated here. The difference between both scatterplots is that we are now going to use actual coordinates in the x and y axis.

Let’s start by searching for a good background image. The Wiki Commons is a good starting point. Please note that there are many projection systems. We will not discuss them here, but the idea is to find a projection that flattens the surface of the Earth. We don’t need much precision here, but we don’t want point to a capital city in the middle of the ocean.

Mercator is a well-known projection that should serve our purposes.

Here is a good image that we can use (don’t forget to check its licencing rights). Save the full resolution version to your local disk.

Open the image in an image editor of your choice. Remove the South pole and the ocean. Save the file.

Back to Excel. Add a new sheet and name it “Map”. I have prepared a list of countries, their capital cities and coordinates. Download the dashboard in the Resorces module and copy that table to your workbook. The coordinates need to be flattened, so that they can correctly display the data points in the scatterplot. That’s why you have two coordinates and one corrected coordinate for each data point (only latitude needs to be corrected).

Now add a scatterplot from the menu Insert / Scatterplot. Right-click the chart, choose Select Data and use Long in cell F as the source for the x-axis and CorrLat in cell E as the source for the u-axis.

 

And the second video: