# Make an Impressive Interactive Map Chart in Excel

Share

Today, let’s learn how to make an impressive map chart with Excel. Something like this:

## Step 1: Set up your Data for the Map Chart

You need geographical fields (district / county/ state or country), few numeric values to create the map chart in Excel. Here is the layout of the data I’ve used.

I will explain the logic for “Calc. Column” in Step 3.

## Step 2: Create “selection mechanism” with Data Validation

Select a blank cell to set up “drop-down” selection mechanism. This is where we will select one of the products to show on the map.

Go to Data ribbon & click on “Data Validation”. Change the rule type to “List” and point to the list of products. You can set up this list elsewhere in the spreadsheet or directly point to the table headers.

## Step 3: Calculate the values for “selected” product

I recommend using either XLOOKUP or INDEX MATCH to calculate the values of “selected” product for our table.

Here is the XLOOKUP formula I’ve used. This assumes the drop-down list selection is in cell G6.

``````=XLOOKUP(\$G\$6,
Table2[@[Mint Chip Choco]:[After Nines]])``````

The above formula looks up the G6 (selection values) against table headers and returns the corresponding product sales values to the calculated column.

## Step 4: Make the Map chart

Select the geographical fields & the calculated columns alone, then go to Insert Ribbon & click on the Filled Map chart option (see below).

Customize the map chart colors & title if you want.

Place chart next to the selection cell and you will have a dynamic map chart in Excel, like below:

If you need a hand with this technique, check out the example workbook.

## Video Tutorial – How to make map charts in Excel?

I made a video explaining this process with a few more examples & tips. Check out the video below or my YouTube channel.

## Known issues & problems with Maps in Excel

Excel map charts are not perfect. Here are some known issues with it.

• Not all geographical data is supported: In many countries, you can’t show data by district or city or regions. For example, if you try to make the same chart with districts of an Indian state, the map will not work.
• No support for LAT / LONG or street addresses: You can’t create map charts with specific point information (like addresses or coordinates).
• Incorrect maps: When a geographical boundary is disputed (due to wars or whatever), the map chart doesn’t show correct outlines or altogether omits the regions.
• Unable to zoom: You can’t zoom in to a specific region with the map charts.

### Possible Alternatives:

You can use Power BI or Tableau other data visualization tools to make the maps. They allow working with more granular data or can let you control the zoom for your maps.

## Got a question?

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### CP05: Interview with MrExcel – Bill Jelen (on his incredible work ethic)

Bill Jelen is one of my most favorite people on earth. That is why I wanted to have him as my first guest when I restarted the podcast. Even though I recorded this few weeks ago, only now I got around to publishing it. Please enjoy the conversation with Bill.

## Related Tips

Charts and Graphs

### How to Create a Dynamic Excel Dashboard in Just 5 Steps

Charts and Graphs

### How to create a fully interactive Project Dashboard with Excel – Tutorial

Charts and Graphs

### 9 Box grid for talent mapping – HR for Excel – Template & Explanation

Charts and Graphs

### 6 Must Know Line Chart variations for Data Analysis

Charts and Graphs

### How to make stream graphs in Excel?

Charts and Graphs

### One Response to “Make an Impressive Interactive Map Chart in Excel”

1. Gary J Keramidas says:

what i do, because i have multiple product codes is. table1 holds all of the data, multiple rows for each state and code
make a list of unique codes to use as a dropdown
=UNIQUE(Table1[Comm_Code]) in AB1
use this formula in the data validation list:
=\$AB\$1#. this is in cell F2 and selects all of the unique items if the row counts change
then i use a filter formula for the chart to plot the data in H2
=FILTER(Table1,Table1[Comm_Code]=F2)
highlight the from H2:H51 and (state) J2:J51 the tonnage.
select the code and i get a map
haven't spent any time trying to make it a dynamic range, i just set it up. excel ignores the blank rows, for now.

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.