Maps are a great visual tools that have been around for a long time. Excel has an extremely easy set up for choropleth maps, and with few additional steps we can make those maps interactive.

Creating a bubble map is doable in Excel, but does require some manual workarounds and has less flexibility. Once you make a few version of it you can create a reusable template. Id recommend using bubble maps in PowerBi or Tableau where it is a built in functionality.

On these posts I’ve highlighted key points about choropleth maps, and Bubble Maps.

I included the workbook I used for this tutorial here:

Touching on the Basics: Creating a Choropleth Map in Excel

The Excel built in map function will add a geography and a metric into a Choropleth map. The geography and values need to be set up in columns to fit the way the functionality works. Unfortunately, excel maps only work with high-level detail, so they wont plot street addresses or latitude and longitude data.

Creating the map is easy. You click the button below under charts. Also, this chart doesn’t works as a pivot chart.

Additional Basic Formatting Tips

After setting up the chart, the map will look like the map below. There are formatting options you can tweak for a different design.

First, pull up the formatting pane by double clicking on the map.

Map Coloring

One piece to consider is the coloring. The options are a 2 or 3 color blend (3 color diverging is the default). However, a 2 color blend could be useful to show a more dramatic change.

In this option, you can adjust the starting and ending colors as well. One good example, is going from a green to a red if one end of the values is more favorable than the other. For example, if your looking at profitability data and some markets are highly unprofitable they should be red.

All of the coloring options are located in ‘Format Data Series’, under ‘Series Color’

Additionally, we can adjust the scaling the color is based off. The default scales the color from lowest to highest. It can be set as a percentages or a manually defined coloring points.

For an interactive map, I would suggest the automatic options in case the values will change in the interactive map. With automatic, one thing to keep in mind is that outliers skew the coloring. In other words, the largest values will define the coloring thresholds.

Map Options

We can make some map changes in the ‘Series Option’ above. I do like removing the states without a data so there is no confusion on what values that are included. When there colored grey it could cause confusion.

Geography Data Types in Excel

For this data set, I ended up using the geography data types. I never used them before but excel has geography data metrics that are already in the program.

It ended up having some good state data and it gives you metrics like household income, Population, median rent and a bunch of others. Id recommended it as a good practice data set.

Interactive Maps using Drop Down

When visualizing geographic data its helpful to have more than one metric. Unfortunately, the Excel map only takes in one metric at a time. To compare across them will require numerous maps.

This post walks through a work around where we can quickly swap out the metrics just like the example below.

There’s lots of benefits to having this set up, like spotting trends easier, its space efficient, and you can share this with an audience to explore the data set.

Most importantly, Excel drop down lists make the dynamic functionality work.

Setting up the Data

We need to have two data setups; a data table with all of the metrics we are looking for, and a two column dataset to feed the map chart.

Below is what I created for my data table. The metric names should be updated to what you want to see in your visualization.

Next, we will set up the data validation list drop down. In the data validation options, we will click on allow list, and then reference the column headers in the Source section. I set this up right on top of my map.

Creating the Chart Source Data

The data source needs to follow our layout that we describe earlier; geography attribute, and metric to visualize. To allow this chart to be dynamic the metric to visualize needs to update according to the metric we have selected.

The first step is easy, we hard code the state column with all of the geography detail. It can be a copy paste from the data table. The values have to be an exact match to the data table because we will be using a lookup.

Secondly, we will have to write a formula that brings in the selected metric. Specifically an index, match match, formula because we need a match to the columns and rows.

Writing the Index Match Match Formula

Here is what the formula will look like. It could look completely different if its not set up as an excel table. I recommend setting it up as a table in case you add data which will be picked up automatically if its a table.

Formula Syntax– what it actually looks like in Excel

=INDEX(Table1[#All],MATCH(L6,Table1[[#All],[State]],0),MATCH($P$4,Table1[#Headers],0))

Detailed Syntax- adding in notes on what each piece represents:

=INDEX(“Locked Data Table Reference “,MATCH(“State Value Not Locked Chart Data”,” State Range In Data Table Locked”,0),MATCH(“Drop Down List Cell Locked”,“Metric Names Range Locked Data Table”,0))

Formula Breakdown:

• INDEX(Table1[#All]: The full data set we are referencing and want to include in the formula.
• match(L6,: For this part of the formula we want to reference the right row (states are the rows) from the data table. So this references the cell to the left which is the state. This cell shouldn’t be locked.
• Table1[[#All],[State]],0: This references the state column that will be used to lookup L6. The ,0 is saying that we want an exact match.
• ,MATCH($P$4,: This is referencing the drop down list cell. Its a locked reference because we don’t want that cell to move at all. This part of the formula is choosing which column (metric) to bring in. When the drop down is changes the value of P4 will change to a different metric causing the lookup to pick up a different column.
• Table1[#Headers],0)): The last reference is for the table headers that will match to $P$4 (the drop down list cell). This one also end in a ,0 to indicate we want an exact match.

This is what the formula would look like if you didn’t use an excel table. Instead of referencing table attributes you will be referencing the cell ranges for each part of the formula.

=INDEX($B$5:$H$40,MATCH(L6,$B$5:$B$40,0),MATCH($P$4,$B$5:$H$5,0))

Final Steps

Now we have a dynamic chart that changes with the drop down. For formatting, I added in a title using a rectangular shape. I used a shape because you can use cell references with shape so the title could also be dynamic.

I have the title referencing a hidden row where I create the text for the title.

The formula for the hidden row is =”US States by “&P4. P4 is the drop down cell. The title will always read: Us States by the metric that is selected.

Conclusion

In conclusion, I’ve found Interactive charts to be very useful. They can be a good piece to incorporate into an excel dashboard or even a standalone tool with a good data set.

The Microsoft documentation is good resource to use for additional information.