Build A United States Dashboard Map Chart In Excel
Map Charts In Excel Are Limited…
Let’s face it, Excel’s built-in Map Chart feature seemingly was thrown together overnight. Its functionality is as basic as can be and leaves a lot to be desired.
This typically leaves us spreadsheet users with the challenge of creatively working within the Excel environment to build our own alternative solutions to get the desired look.
Whether you are looking to build a cool infographic, customized map chart, or Excel dashboard of the 50 states within America, this guide will give you a tremendous headstart (and there are some downloadable goodies along the way)!
50 US States Shape Icons [Excel Download]
Before you begin, you’re likely going to need a map of the United States that you can work with inside Microsoft Excel. I’ve painstakingly gone through and designed an Excel shape icon of each state with the United States that you can manipulate until your heart’s content.
Since the state icons will all be Excel Shapes, you can manipulate them any way you’d like! This means you can recolor them, add a border, implement a gradient, or even tweak the transparency. These state shapes will function as if they were part of Excel’s native insert shapes feature.
If you would like to directly download an Excel file with all these shapes included, you can click the below button to instantly get it. No strings attached!
State Icons Shape Setup
Before you begin working with these shapes, you’ll want to understand how I formatted them.
- Group Name – I’ve grouped the shapes with the group name set to “UnitedStates”.
- Individual Name – I’ve also named each shape with the following nomenclature: “State_” + the two-letter state abbreviation.
- Border – Each shape has a white-colored border with a weight of 0.25 EXCEPT FOR Hawaii and Alaska
The best thing about having all the state icons formatted as individual Excel shapes, is you can use them in so many different ways. You can isolate one for a particular use case or even grab a cluster/region to use in your spreadsheet.
Further on in this article, you’ll see some demonstrations of how you can use these graphics to build dashboards, infographics, and highlighted map charts.
United States Regional Graphic [Excel Download]
Many companies like to divide up the United States into regions. This can make the United States appear more simplistic if there is little to no value in viewing all 50 states. I’ve had a few projects where I needed to split the United States up into four regions:
- Eastern
- Central
- Mountain
- Western
I created custom regional shapes for these projects to accommodate a more summarized image of the United States. The finished design looked like this:
WESTERN REGION
- California
- Nevada
- Oregon
- Washington
MOUNTAIN REGION
- Arizona
- Colorado
- Idaho
- Montana
- New Mexico
- Utah
- Wyoming
CENTRAL REGION
- Arkansas
- Illinois
- Indiana
- Iowa
- Kansas
- Louisiana
- Minnesota
- Mississippi
- Missouri
- Nebraska
- North Dakota
- Oklahoma
- South Dakota
- Texas
- Wisconsin
EASTERN REGION
- Alabama
- Connecticut
- Delaware
- Florida
- Georgia
- Kentucky
- Maine
- Maryland
- Massachusetts
- Michigan
- New Hampshire
- New Jersey
- New York
- North Carolina
- Ohio
- Pennsylvania
- Rhode Island
- South Carolina
- Tennessee
- Vermont
- Virginia
- West Virginia
If you would like to directly download an Excel file with these regional shapes, you can click the below button to instantly get it. No strings attached!
Build United States Infographic Charts
When you have the ability to fully manipulate the individual shapes in Excel, you can make some really neat infographics with the United States country as the backdrop.
Here is an example I put together using the regional Excel shapes mentioned earlier in this article. I actually built something similar for a client a few years back and they absolutely loved having it built directly in Microsoft Excel because the data for the infographic could be linked to cells throughout their spreadsheet.
How I Built This Excel Infographic
There were really just two components/techniques I utilizes to build the above infographic once I had created the regional shapes:
- Customized Lines (the properties in the Shape Outline are as follows)
- Weight = 2.25 pt
- Dash = Square Dots
- Circular End Point
- Navigate to Arrows >> More Arrows….
- Begin Arrow Type = Oval Arrow
- Begin Arrow Size = Size 9
- Ranges pasted as Linked Pictures
- Select and copy the Excel Range
- Select the destination cell location
- Click the Paste Menu Drop down and select Linked Picture
- Move your Linked Picture to your desired location
United States Dashboard Ideas
With the help of a little VBA coding, you can truly build unique Excel Dashboard solutions. Some functionalities you might build could be:
- Hiding certain states
- Copy/Pasting an individual state into your dashboard when the state name is selected
- Highlighting specific states with a unique color
I’ve used many of these ideas in actual dashboards and they always seem to amaze the end user and give them great visual representations of what they are looking at.
Automate Highlighting States With VBA Code
Let’s look at a simple example where I used some VBA code to automatically highlight or unhighlight an individual state based on a “Yes” value flag in an Excel Table.
Because all the state shapes have a standardized naming convention, it was very easy to build a table that listed out all the Shape Names. I just needed to grab a list of the US States and their abbreviations, then paste those two lists into Excel.
I utilized a simple Data Validation list to allow the user to select “Yes” or they could delete the value to remove the highlight color.
The highlight color is stored visually as the fill color of a square Excel Shape. I find just pulling the fill color from a shape or cell is much easier to manage than dealing with color codes.
Let’s now take a look at the VBA code I used.
Sub UpdateStateGraphic()
'PURPOSE: Update Highlighting Within United States Map Chart
'SOURCE: www.thespreadsheetguru.com
Dim shpUnitedStates As Shape
Dim shpHighlight As Shape
Dim tblStates As ListObject
Dim cell As Range
Dim HighlightColor As Long
'Determine Highlight Color
Set shpHighlight = ActiveSheet.Shapes("HighlightColor")
HighlightColor = shpHighlight.Fill.ForeColor.RGB
'Reset All States
Set shpUnitedStates = ActiveSheet.Shapes("UnitedStates")
shpUnitedStates.Fill.ForeColor.RGB = RGB(208, 206, 206)
'Loop through each row in table and color states that are flagged
Set tblStates = ActiveSheet.ListObjects("Table_States")
For Each cell In tblStates.DataBodyRange.Columns(4).Cells
If cell.Value = "Yes" Then
ActiveSheet.Shapes(cell.Offset(0, -1).Value).Fill.ForeColor.RGB = HighlightColor
End If
Next cell
End SubThere are essentially three parts to this code:
- Retrieve the highlight color form the square box on the spreadsheet (named “HighlightColor”)
- Turn all 50 states grey (or whatever color your want) to reset them
- Loop through each row in the Excel table and change the color of states that are flagged “Yes”
If you want to get a little fancy, you can even set up an event trigger by running the VBA macro whenever there is a change on the spreadsheet. If you paste the following code into into the specific worksheet’s code page, this event will be setup.
Private Sub Worksheet_Change(ByVal Target As Range)
'PURPOSE: Run macro code whenever a value changes on this spreadsheet
Call UpdateStateGraphic
End Sub
If you would like to download this exact example file, you can do so by clicking the button below. Please note, since this file uses VBA and will be downloaded from the internet, Microsoft Excel may block the code from running.
Make sure you follow the steps to unblock the file before you try opening it in Excel.
I Hope This Helped!
Hopefully, I was able to explain how you can use the 50 US State Excel Shapes I’ve built in a variety of ways to build your own infographics, dashboards, and chart maps. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
Also, PLEASE share screenshots of your creations in the comments section! I love seeing all the creative ways you have been able to use these state shapes in Excel.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.