I’ve been fascinated by maps since I was a little boy… back then, because I was reading history books or playing at risk. But even today I like to look at information on a map.
That’s why in this post I show how to prepare data geographically in Excel as a “heat map” (also called a choropleth map, area cartogram, or area value level map. See: Wikipedia).
Excel has offered the Power Map feature since version 2013.
Before that, doing this in Excel (up to version 2010) was quite a hassle!
Because this method is still relevant today, because not every geographic area that you want to show can be represented by power maps by default, I also show the old proven method!
If you want to see the final result for yourself, you can do so with the file:
Excel offers since version 2013 the Power Map function. Depending on the version of Office you have you have to install Power Maps as an add-in.
In the 365 version of Office it is integrated directly into Excel. Here is the link to Microsoft if you still need to download it: Power Maps from Microsoft.
The menu for Power Maps can be found in the “Insert” menu>Maps:
If you want to graphically analyze data like the data in this table, simply select the entire data set and click on Create Map.
It is important that the data columns are clearly labeled. Excel uses this information to determine the geographic locations!
Power Maps has some potential for frustration, because it doesn’t always recognize the areas you want!
Here are the most common errors and how to fix them:
Power Map only recognizes geographic values displayed at the levels of country/region, state/canton, administrative district or postal code!
Solution: Divide data into these categories where possible or then use the older solution I describe below for Excel 2010.
What does this mean anyway? What does Excel want to tell us that the map was drawn “With high confidence”?
Quite simply: The AI (Artificial Intelligence) algorithm at Microsoft was unsure whether we really meant the displayed lengths. In the above example, I have mixed countries, as well as federal states and Swiss cantons.
Power Map indicates that there are two possible localities and it does not know which one to have. Example from Switzerland: the localities Rohr (Solothurn) and Rohr (Aargau)
Here also Microsoft’s own clarification about possible errors with Power Maps: Microsoft article
The map is, as far as the above mentioned problems are solved, perfectly created and graphically well presented:
One thing you have to be aware of: if you use the Power Map function, you send your data, no matter what it is, to a Microsoft server.
which then calculates what information is involved and how it should be displayed on the map. A small hint that this is the case has Microsoft itself attached to the bottom right of each map!
If you look closely, it says the following:
This may not matter for most companies, but for industries like banking or others that have to handle customer data sensitively, it can lead to a violation of applicable laws or internal rules!
Again, the final result can be viewed directly in the example file (download above).
Summarized in one sentence: The map consists of individual drawn “shapes” that get a corresponding background color via VBA code (based on the assigned value).
Here’s a more detailed explanation
Basically, this method creates a shape for each geographic area (via Insert Menu>Shapes>Freehand: Sketch)
As mentioned at the beginning, you can also create your own areas that are not selectable in Power Maps… e.g. Northwest Switzerland, or South Germany, etc.
Of course there is the possibility to import the geographic areas from another file. But first you have to find a file with complete cartographic information. Wikipedia is not a bad place to start as the data is always free to use and often in a useful format!
From my experience I can say that SVG files work well. However, even here you have to count with a lot of manual effort until the maps are really usable!
elements The shape elements should be named
The best way to do this is to use meaningful names, so that it is later easy to navigate to a form element correctly.
An example of how this was done in my file can be seen here:
Note: Creating the map material is already a hurdle that should not be underestimated and can lead to considerable work!
preparation Finally, the data preparation is just a list! So nothing very complicated.
The only important point is that the forms created above are each listed with the form name. E.g. obj_AG
This name, must be noted in the list, so that we can connect in step 3 the forms (geographic elements) with the respective correct data point!
So that the colors are now assigned to the appropriate value we need a little VBA code.
The code is simply inserted in a new module.
Here is the code I have in my example folder:
And here as text to copy:
Sub RecolorMap() Dim i As Long For i = 2 To 29 With wsValues ChangeObjectColor .Cells(i, 1), .Cells(i, 3) End With Next i wsMap.Range(“A1”).Select End Sub ChangeObjectColor(objName As String, col As String) Dim r As String, g As String, b As String r = Split(col, “,”)(0) g = Split(col, “,”)(1) b = Split(col, “,”)(2) Dim canton Set canton = wsMap.Shapes.Range(Array(objName)) canton.Select ‘wsMap.Shapes.Range(Array(objName)).Select With Selection.ShapeRange.Fill .Visible = msoTrue .ForeColor.RGB = RGB(r, g, b) .Solid End With End Sub
Continue reading: https://excelnova.org/wie-man-eine-heat-map-flachenkartogramm-in-excel-erstellt/