Pozycjonowanie na rynkach zagranicznych - projektowanie graficzne piotrków trybunalski - Global SEO gwarancją widoczności w wyszukiwarce
odkomarzanie warszawa bijkomara Zwalczanie dorosłych komarow i innych owadów odbywa się najczęściej za pomocą metody zwanej zamgławianiem. Budzimy się kompleksowym odkomarzaniem. Zlecenia robimy na placu Warszawy i okolic – sprawdź BijKomara.pl.
biuro rachunkowe belchatow 1 maja 1 dotacje belchatow uslugi ksiegowe belchatow 1 maja 1     
l'odibidon   l'odibidon   łańcuszek męski   abaå¼ur   hala stalowa 13x30   moulinex serwis   pawilon ogrodowy 3x6   lightsaber dildo   schowek na balkon   https://shopme-online.org/pl/page/14/   kasy fiskalne Elzab

How to create a heat map (area cartogram) in Excel

How to create a heat map (area cartogram) in Excel

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).

Two methods

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:

Step 1: Data basis for Power 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 Map Error

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:

Error 1: Region not recognized

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.

Error 2: Data drawn with high confidence level

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.

Error 3: Places or areas with the same name

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

Step 2: The Power Map

The map is, as far as the above mentioned problems are solved, perfectly created and graphically well presented:

Data Security (Attention: Data will be sent to Microsoft for evaluation).

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).

How to create the heat map in Excel 2010

How does it work?

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

Step 1:


the Geographic Shapes


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.

Import Maps

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!

Name the shape

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!

Step 2: Data

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!

Step 3: VBA Code

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/

Leave a Reply

Your email address will not be published. Required fields are marked *