Excel provides some very useful visualization tools. Among them, the geographic heat map is an interesting one. It is widely popular too for visualizing datasets consisting of different areas. In this tutorial, I will show you how to make a geographic heat map in Excel.
There are two major methods you can use to make heatmaps. One is to use the built-in tool Excel has to create charts using maps. And another one is to use external add-ins. You can use either one for your purpose, both have a wide range of flexibility to customize and modify the heat maps. Here, I am using both methods to make a heat map for different states in the US and another one on the world map for different countries.
1. Using Built-in Maps Chart to Make Geographic Heat Map in Excel
If you want to create a heat map of different cities, states, or countries without the help of any external tools, Excel has one built-in tool to create charts. In the event that you want to use it for maps with datasets containing different areas, you can get a heatmap from it. I have used two examples below so you can see how you can apply this method for both states on a country map and countries on the world map.
1.1 Creating Heat Map of States
For a demonstration of the heat map of states, I am using the following dataset.
It contains nominal GDP per capita for different states in the US. Now, we will see how we can visualize the comparison of GDP by using a heat map.
Steps:
- At first, select the dataset you want to make a heat map from.
- Now, go to the Insert tab in your ribbon.
- Then, from the Charts group, click on Maps.
- From the drop-down list, select the Filled Map Icon.
- Consequently, a heat map will appear.
- Now you can modify this chart to your liking from the Chart Elements and Chart Styles button that appears beside once you select the chart.
- For better visualization, I have selected Style 3 from the Chart Styles.
- And selected data labels to show country names by selecting Data Labels from Chart Elements and then selecting More Data Label Options. Then I selected Category Name.
This way you will have the geographic heat map made for visualization in Excel.
1.2 Creating Heat Map of Countries
You can use the method described above to make a geographic heat map in Excel too. To do that on a world map colorizing different countries follow these steps.
I have used the following dataset containing a dataset of countries.
Steps:
- At first, select the dataset you are visualizing heatmap from.
- Now go to the Insert tab from your ribbon.
- Then from the Charts group, select Maps.
- From the drop-down list, select the Filled Map Icon.
- At this point, Excel will automatically put in the world map as country names were used in the dataset.
- Now you can tweak the modification options for better visualization of your heat map. I have selected Style 3 from Chart Styles.
Now the geographic heat map will look something like this.
This is how you make a geographic heat map for countries in Excel.
Read More: How to Make a Heatmap in Excel
2. Applying Add-ins to Make Geographic Heat Map in Excel
You can also make a geographic heat map in Excel for both states and countries by using add-in tools. To make a geographic heat map in this method, you first need to add an external add-in tool. Follow these steps to add the add-in tool.
Steps:
- First, go to the Insert tab in your ribbon.
- Second, select Get Add-ins from the Add-ins group.
- Now, in the Office Add-ins box that appeared, select the STOREÂ tab.
- Then in the search box, type geographic heat map.
- After that, click on Add from beside the Geographic Heat Map add-in.
You are done adding the Geographic Heat Map add-in to make one in Excel.
Follow the sub-sections below for your desired heat map areas.
2.1 Making Heat Map of States
To make a geographic heat map with the add-in we have just added, follow these steps below.
I have used the following dataset for demonstration.
Steps:
- At first, go to the Insert tab in your ribbon.
- Then from the Add-ins group, select the downward-facing arrow beside My Add-ins.
- Here, you will find the add-in you have just added. Select Geographic Heat Map from it.
- Consequently, an object box will appear for the add-in. Select Get Started in it.
- Now in the Choose map field select the USA, as our dataset consists of the states from here.
- Now to use the dataset, select Select beside Choose data.
- Then select the dataset from your spreadsheet.
- Now click on OK on the Select Data dialog box.
- Make sure the Regions column and Values column are properly selected.
- For a better display, I have selected the legends to appear on the right.
- Now click on Save.
You will now have a geographic heatmap in your spreadsheet.
2.2 Making Heat Map of Countries
This subsection will focus on how you can make a geographic heat map in Excel for countries using add-ins. Make sure you have added the Geographic Heat Map tool from the add-ins as described previously. Then follow the steps used in this method to get a heat map for the countries.
To show this method, we need a dataset containing country columns. So I have used the following dataset for demonstration.
Steps:
- At first, go to the Insert tab on your ribbon.
- Then from the Add-ins group, select My Add-ins.
- From the drop-down list, select Geographic Heat Map.
- Eventually, an object box will appear, select Get Started from it.
- Now select World in the Choose map field.
- After that, select Select in the Choose data field.
- Now select the data from your spreadsheet.
- After that, click on Ok in the Select Data dialog box.
- Now make sure the Regions column and Values column are selected properly.
- For a better presentation, I selected the color theme from Green to Red and kept the legend at the bottom.
- Once you are done, click on Save.
As a result, you will have the geographic heat map of the world in your Excel spreadsheet.
Download Practice Workbook
You can download the workbook used for the demonstration below. It contains all the datasets and heatmaps in different spreadsheets for a better understanding.
Conclusion
These were all the methods you can use to make a geographic heat map in Excel. Hope you have found this guide helpful and easy to understand. If you have any questions or suggestions, let us know below.
Related Articles
<< Go Back to Heatmap in Excel |Â Data Visualisation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
