Bottom Line: Learn how to create a Map Chart in Excel for advanced and good-looking visualisations of geographical data.
Skill Level: Intermediate
Video Tutorial
What is an Excel Map Chart
The Excel Map Chart is a feature that is available in the latest versions of Microsoft Excel. It allows you to visualise any sort of geographical data in a stunning and innovative way.
The cool thing about the Map Chart is the automatic detection and recognition of geographic data, which means you don’t have to manually match your data points with a specific map area. Instead, Excel does everything for you, regardless of whether your data are on a country, state, country and even postal code level. In this introduction we will cover everything you need to know to create quick, meaningful and good-looking Map Chart visualisations. If you want to see that Map Chart in full action have a look at our Excel Interactive Dashboard Tutorial.
Info: Excel Map Charts are only available in the latest versions of Excel.
Creating different Excel Map Chart Types
Map Charts in Excel can be used to either compare values or show categories across geographical regions. With the latest versions of Excel, Microsoft introduced new data types like the geographic data types. You can simply input a list of geographical values and manually convert them into geographical data by going to the Data tab > Data Types > Geography. But even if you don’t convert them explicitly, Excel is able to recognise most of the geographical value if you use the Map Chart feature.
To create a Map Chart, select any cell or specific range within the data, then go to the Insert Tab > Charts > Maps > Filled Map. Based on the data you provided, Excel will automatically insert either a value or a categorical map.
Map by Value
If you have numerical data associated with your geographical values, Excel will automatically insert a value map. Numerical data is basically everything with numbers, e.g. revenues or market share. A value map will always be coloured according to a min-max-scale that should be displayed in the top-right corner of the Map Chart by default.
Map by Category
If you have categorical data associated with your geographical values, Excel will automatically insert a category map. Categorical data are data that may be divided into groups or categories. A category map will have one color per category by default which is displayed in the top-right corner of the Map Chart.
Excellent Excel Map Chart Design
Map Charts are already a beautiful way to display your data by default. But to get the most out of it, you should know how to quickly improve and adjust the design of your map chart. So let’s have a look at the most important adjustments you can make.
Background Color
The background of Excel charts in general is a pretty powerful but underestimated feature. By increasing the contrast of background and the chart itself, you can easily increase the meaning and visual comfort of your audience. To quickly change the background colour scheme, simply go to the Chart Design Tab and select one of the suggested Quick Designs.
Color Scheme of Geographical Regions
To quickly change the color of the displayed regions, you can use the Change Color option in the Chart Design Tab. Here you can select one of many suggested color schemes.
However, if you don’t like any of the suggested color schemes and want to use your own combination of colors, you can right click on one of the countries in the map chart and select ‘Format Data Series’.
In the opening ‘Format Data Series’ area you can now change the colour of all regions with no data by selecting a specific Fill in the ‘Fill & Line’ Tab.
If you want to manually change the color scheme for the regions with data, the required action depends on whether you have a value map or categorical map. For a value map, go to the ‘Series Options’ Tab and have a look at the ‘Series Colour’ section. Here you can choose whether the gradient style of the colour scheme is based on two or three colors. Subsequently, you can choose each individual color. For a categorical map, you have to select each region by clicking on it and then change the color via the ‘Fill’ setting.
Advanced Maps Options
There are three more design options available in the ‘Series Options’ Tab that are worth to be mentioned for the Map Chart: The Map Projection Option allows you to select between multiple types of map projections. The Map Area Options allows you to decide whether the whole world or only regions with data will be displayed. Eventually, the Map Labels Option allows you to either display or not display Labels for the different regions.
What Geographical Types Are Recognised
The Excel Map Chart feature is designed to plot high-level geographic details. Below, we listed all types of geographical data you can use and not use.
Data Types that work with Excel Map Charts
- Countries (e.g. USA, Canada, Mexico, etc. or abbreviations USA, CA, MX, etc.)
- State (e.g. New York, Texas, California, etc. or abbreviations NY, TX, CA, etc.)
- Country (e.g. Henry Country, Fulton, Cobb, etc. but no abbreviations)
- Postal Codes (e.g. 30288, 30297, 30236, etc.)
Data Types that do NOT work with Excel Map Charts
- Continents (e.g. North America, Asia, etc.)
- Latitude/ Longitude
- Street Addresses
If you try to use data for which there might be more than one similar location in the world, try to provide as much information as possible to allow the Map Chart to automatically differentiate between regions. For that reason, we recommend to avoid abbreviations (e.g. CA could be Canada or California) and always write the full regions name.
