In Excel 2016, MS introduced a new chart type, the Map chart. This chart type visualizes data on the world map. This helps us create easy to understand dashboard that shows regional data. The end-user does not need to read each country, region, state's name to understand the data. They can understand the data by just looking at the graphical image of the map (with geographical knowledge).
In this article, we will learn how to create and use the map chart in excel.
This is the first and most important step for creating an Excel MAP chart. The excel allows Region, country, states and city names in map charts. These names should not be ambiguous or incorrect as Excel uses Geonames for recognizing the area.
Let's say if you want to create an Excel chart, country-wise then each name of the country should be correct in spelling. The two charts below are trying to show the same data. The first chart uses the correct names but the second chart the data that has an incorrect country name "Caneda". The Geo Name failed to identify such a country.
So, arrange your data properly.
The yellow corner on the map chart shows the confidence warning. If your data is not consistent and Excel still shows a chart, it will come with a confidence warning. It will tell how much of the data shown on the map from the source. Try not to have this yellow corner on the Excel Map Chart.
Use Proper Heading for Map Chart Data
The map charts are sensitive to the headings and fields. So, define the headings properly. For example, write Country for the column containing country names, state for the column containing states, region for the column containing regions.
Don't leave any ambiguity in data
Make sure that your data does not contain ambiguous names. For example, the UK is a country and UK is also a state in India. It is best to have a supergroup column. If you are going to use state names, then define the country in the column before the state name.
Example 1: Let's say you have the data of the site visit % by country.
To insert a map chart for the above data follow the below steps.
1. Select the data from the heading to the bottom.
2. Now go to the insert tab. Locate the map chart. And just click on it. And it is done.
Example 2: Map Chart by States
To create a map chart make sure you have their country name in the column before state names. It is not necessary but highly recommended.
Let's say we want to create a map chart of voting percentage by state in the USA. The data arrangement looks like something this.
Now just select the entire range, from heading to the last used row. Repeat the steps of example 1 and you will have the map chart ready. It will look like something this.
The interpretation of the map chart is easy. The darker color area is of higher value and lighter areas contain the smallest values. It is represented using a gradient bar on the chart. However, you can change the colors of the gradient from the formatting of the Map Chart.
Adding data labels to the Map chart: Like any other chart, click on the map chart, a Plus sign will occur. Click on it and the 3 options will appear. In these options find "show data labels". Check it and all the labels will be shown on the chart.
Change Color Scheme of MAP Chart
There are two types of color schemes for the excel map chart. Sequential (2-color) and Diverging (3-color) scheme.
The Sequential (2-color) scheme
In the above examples, we are using the sequential or two-color scheme to depict the values. Usually, we use lighter colors for smaller values and darker colors for the higher value. But you can have any colors.
To change colors of the map, right-click on the map area and click on the format data series. By default, it is sequential. In the series color segment, you can set the color for minimum and maximum values.
The lowest value, the color is what defined. It changes casually to the color of maximum value as the value increases.
You can see in the above image the color for the lowest value is bright Red and for the greatest value, it is bright Green. The red color gets darker as the value increases, to the darker green for mid-value. The green color gets brighter as the value increases.
The Divergent (3-color) scheme
In the above example, the mid-value is the darkest color. It may get confusing to some. You may want a different color for the mid-value. For example, if the value is smallest, you may want it to be Red as above. As the value increases, you may want to make it yellow. This will indicate the value is approaching the middle value. And then make it green for the top-most value on the map. This can be done using the Divergent color scheme of the map chart.
Follow these steps to make a 3 color divergent map chart.
To change the color scheme of the map chart, right-click on the map area and click on the format data series.
By default, it is sequential. From the drop-down, select Divergent.
In the series color segment, you can set the color for minimum, midpoint, and maximum values.
In the above image, the lowest value is represented as a bright red color. As the value approaches midpoint, the color changes to the bright yellow. When the value approaches maximum value, the color slowly changes to bright green.
There are other formattings to make the chart look good. You can explore them easily from the formatting option.
Note: The MAP chart is only available to the Excel 365 subscribers.
I explained the map chart in excel because there is not much on the internet about this chart type. I hope it was helpful. If you have any doubts or special requirements, let me know in the comments section below.
Pareto Chart and Analysis | The Pareto analysis states that your 80% problem is caused by 20% of factors. Or we may say that by working on only 20% of cases you can solve your 80% of problems. Fascinating, right? The method is also famous as the 80/20 rule
Waterfall Chart |This chart is also known as the flying bricks chart or as the bridge chart. It’s used for understanding how an initial value is affected by a series of intermediate positive or negative values.
Excel Sparklines: The Tiny Charts in Cell | The Sparklines are the small charts that reside in a single cell. Sparklines are used to show trends, improvement, and win-loss over the period. The sparklines are charts but they have limited functionalities as compared to regular charts.
Creative Column Chart that Includes Totals | To include the total of the clustered column in the chart and compare them with another group of the columns on the chart is not easy. Here, I have explained, how to smartly include totals in the clustered column chart.
4 Creative Target Vs Achievement Charts in Excel |Target vs Achievement charts is very basic requirement of any excel dashboard. In monthly and yearly reports, Target Vs Achievement charts are first charts the management refers too and a good target vs Achievement chart will surely grab attention of management
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.