Excel: Create Stunning Map Charts with Geography Data Types

Here is a question that has been particularly popular recently. How do you create a map charting figures by county, city or zip code in Excel? There are a variety of ways to tackle this question, but today I want to show you how to use data types in conjunction with the map chart type to create a chart by county in Excel.

County populations displayed on a map

This Byte is inspired by two different faculty members who emailed me with this question… you know who you are, and thank you for the inspiration!

Starting Place: The List

Your starting place should include clearly named locations and associated figures. In my case, I picked a few Kansas counties (sorry if I left out yours!), and their populations.  The numbers could have been any figure you are tracking, of course.

List of counties and populations

Step One: Convert to Geography Data Type

The first step is to make sure Excel can identify the locations listed in your document. This can be an optional step, but skipping it may mean that Excel can’t identify one or more of your locations and the map therefore won’t cooperate, so best practice is to start here.

1. Select the data to be identified (counties, in this case).

2. Go to the Data tab, Data Types group and select Geography.

Insert tab, data types group, Geography

3. Excel will attempt to identify the locations. If all goes well, a little map icon will appear to the left of the county name.

Locations identified, signified by a map icon on the left of county names

By the way, notice the little box at the upper right of your selection. Click on this to extract other pertinent information about your location. This is not part of your chart, but a cool trick in Excel worth mentioning.

Additional information about counties can be extracted at upper right

Here are a few extracted fields, so you can see how they look: image, largest city, area. Notice how Excel creates a new column for each one.

Information extracted, as described above

Here is a bit of inspiration: imagine that you have a list of zip codes and figures and you need to create a map by county. Simply convert the zip codes to geography data types, extract county, and off you go. Pretty handy, right?

Step 2: Create a Chart & Customize

Now that Excel has identified our data, we are ready to create a chart.

  1. Select the data to be charted. In this case, county and population columns.
  2. Go to the Insert tab, Charts Group
  3. Select the Map dropdown, Filled Map option.

Insert tab, charts group, Maps dropdown, filled map option

4. Excel will create a map with your data. If you don’t care for the default colors and appearance, don’t forget you can customize all your charts with the contextual Chart Design tab in the ribbon.

Contextual chart design tab

Shameless Training Plugs:  If you would like to learn more about the Chart Design contextual tab, please attend one of my Excel Pivot Tables, Charts and Pictures sessions (now offered remotely). Also, don’t forget you can easily change your theme colors on the Page Layout Tab in the ribbon. We cover this one in Excel Essentials if you are interested in learning more.

It may take a few tries to get a map you are happy with. Don’t be discouraged! The results are fantastic.

Kansas county by population map chart

One More Word on Geography Data Types

In the example above we mapped by county, but you may find yourself needing to map by zip code, state, country… the Geography data types can recognize all of these and more. Experiment with this powerful tool and you may be surprised what you find.

Thoughts?

What do you think, do you have any geographic data that is calling out for a better visualization? I can’t wait to hear how you use this feature!

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

  • Debbie Neill
  • Kelsey Unruh