The Map chart has been available in Excel since version 2016 from the menu Insert > Cards > Choropleth Cards.
To be able to correctly represent your data on a map, it is essential to respect the following rules.
Join the data
The first thing to respect in order to display data on a map is to always put the geographic data and the data to be displayed in side-by-side columns.
So, a data presentation like this, Country / Year / Population will not display population by country.
To avoid this problem, it is imperative that the geographic data (here the countries) is the column which precedes the data to display (the population).
Enrich geographic data
When you want to represent regions or departments, it is essential to add at least the name of the country as geographical data.
For example, in this document we have represented the Swiss population by canton. As you see, we have precedes the cantons column with a column with the name of the country.
In this way, Bing Map (the tool that is called to carry out the display work), recognizes the belonging of cantons or regions to a specific country. Without this precision, the graph is necessarily imprecise.
And why doesn't it work for the French regions? ?
Now, in the case of France, the subdivisions are not called cantons but regions. And the term “Region” poses a problem for Bing Map.
In fact, for Bing Map, a region is like a country (it may seem strange but that's how it is). So even if you correctly create the data with the country name in the first column, the map will not display.
Now, instead of Region, if we write Province, the graph is perfect.
Department or Departments?
Last specificity, the use of the plural. In fact, we must take the point of view of the United States (Microsoft headquarters), we always speak of States in the plural.
It may seem anecdotal but this difference alone has an impact on the display of the map.
For example, to map the French departments, you should not write the header in the singular as here only one department is represented (because of the singular?).
Now, the same data but with the header in the plural, the graph is perfectly constructed.
You will find additional information on creating a map chart on the Microsoft site.
Video
You will find all these explanations with other tips in this tutorial video.
22/12/2024 @ 19:27 p.m.
Hello,
I have excel 2016 but I don't have insert then Map
can you help me
23/12/2024 @ 17:11 p.m.
Because this type of card does not exist with this version. Only Excel 365, 2019, 2021 and 2024 versions have it
09/12/2024 @ 20:14 p.m.
Hello Frédéric and thank you for these very useful demos. I would like to make different cutouts, Countries (France), Specific customer cutouts (West/East/Aura, etc.) and Provinces, is this possible in your opinion? Thank you in advance and I hope wherever you are, you are well 🙂 Céline
10/12/2024 @ 07:57 p.m.
Hello Celine, it is not possible in Excel but in Power BI. But you have to create your map yourself in Json (and it is not easy)
28/09/2024 @ 11:04 p.m.
Hello Frederick,
Thank you for sharing the excellence of your work. In your example concerning the Swiss population, I see that all the cantons are mentioned. I created a similar file but for turnover and unfortunately it does not work for the canton of Vaud. Any idea or solution? Thank you in advance. Kind regards.
28/09/2024 @ 13:19 p.m.
Thank you for your feedback.
Unfortunately, I don't know why the canton of Vaud does not appear on your map. No idea.
26/08/2024 @ 10:53 p.m.
Hello, how to do for Colmar which does not exist in geographical data
26/08/2024 @ 11:17 p.m.
This is normal because this type of chart is based on geographic shapes: countries, regions, departments, postal code. For cities, it's easier to use postal codes. You can download them for free in the store area of the site.
30/05/2024 @ 15:40 p.m.
put the name of the country in an Excel column (column for example) because for Bing the word Brittany is too vague. We will therefore have France (A1) Brittany (B1) the desired information figure (C3)
15/02/2024 @ 12:21 p.m.
Thank you for this very interesting tutorial. on my map of France province it does not show Brittany?? I rewrote it. I don't understand ? an idea !
15/02/2024 @ 13:26 p.m.
This is a problem that I reported to Microsoft. On my version, it works now. Otherwise, put "Brittany" or "Breizh" and it will work
14/12/2022 @ 16:32 p.m.
Hello, although I follow the tutorial I cannot obtain department maps. I tried everything with department headers. there is indeed the Country column.
14/12/2022 @ 16:52 p.m.
Honestly, no idea. Have you tried with the name County or Counties?
14/12/2022 @ 17:25 p.m.
Thank you for your return. Yes I tried, still KO.
14/12/2022 @ 17:38 p.m.
Have you tried with the region names. Ok this is not your need but it is to test if it already works
07/11/2022 @ 11:45 p.m.
Hello,
Many thanks for your article! Can’t wait to get started.
I have the 2016 version of Excel but I cannot find the "Cards" option and therefore the choropleth cards in my ribbon. However, my "3D card" is activated and present.
Could you help me how can I make the "map" option appear alone in order to be able to create my choropleth map of the French departments?
07/11/2022 @ 12:00 p.m.
For choropleth maps, it is necessary imperatively Excel 365 (even Excel Online) does not offer this option.
14/12/2022 @ 17:42 p.m.
Regions ok. With Provinces in mind
06/09/2022 @ 13:37 p.m.
Hello,
Thank you very much for this tutorial...I have a bug on the regions, the title Bougogne Franche-Comté is well recognized but the figure data does not have it.
Do you have a solution?
Thank you in advance
06/09/2022 @ 15:32 p.m.
No idea, sorry
12/08/2022 @ 16:17 p.m.
Hello
Many thanks for all these explanations, but despite all my attempts it doesn't work, I'm still stuck.
Impossible to display my data by department and even less by postal code.
05/08/2022 @ 09:35 p.m.
Hello and thank you for your explanations.
I would like to produce a map but at the municipal level. Is there a trick or this level is not taken into account for these cards? (Obviously, the term communes is not interpreted as a geographical indicator.)
09/08/2022 @ 14:38 p.m.
No, this vector type is not supported for maps