Create a US Geographical State Map in Excel

Create geographical state maps in Excel for the US in seconds! Just put your data, and the editable template will visualize your data automatically!

In our previous article, we showed how to use conditional formatting if you want to create a simple heat map in Excel.

Data visualization is a rather popular subject, and Excel provides endless possibilities for collecting, organizing, and displaying continuously growing data. If you work with the newer versions of Excel, than you probably know PowerMaps and 3D Maps applications.

To run these, you must have at least Excel version 2013. They can create excellent geographical state map! They support the country, county, and city-level analysis.

In today’s example, we show a modern and easy to use method! With the use of Custom Excel shapes and formulas, we can create our own state maps in Excel in a short time. Some knowledge of programming is required, but you don’t have to worry about this. We provide you complete source code in the article, and the state map template is free to download. As a precursory take a look at the map above for the end result. It looks good, isn’t it?

What about Excel 2010 and earlier versions? Fortunately, there are solutions for the users of these versions also. They don’t have to demit the user enjoyments offered by the geographical state maps!

How to map data in Excel?

In this article, we will go over every single step. Our goal is for you to be able to create your own country’s geographical state map later.

The development of this tool consists of 4 main parts:

  1. Preparing Shapes in Excel
  2. Create the Data Table and Textboxes
  3. Create the Legend and Color Scale
  4. Write a short VBA code
excel us states map final work

Preparing Shapes in Excel for state Map

In this example, we’ll create a special US state map chart. For the first step, we’ll import into Excel a simple vector map that contains all of the states of the US.

This is not too complicated to get, but we’ll spare you the search. You can download from HERE the detailed maps of all the countries of the world is ready to use format.

Their common characteristic is that they are all free-form editable maps, so you don’t have to draw the states manually. The characteristics of the shapes that they are grouped, so we have to separate them with the use of the “Ungroup” function.

ungrouping raw excel map

So we’ll be able to color every single state individually. After the ungrouping, we’ll have a map just like this one below.

select unique state on the map

There are other solutions if we’d like to create some specific state maps. With the help of Inkscape (free software), we can create unique shapes. Finally you can use Excel to make special state maps. Use the following: Insert > Shapes > Freeform > Shape.

Create the Data Table and Textboxes

Create a similar database as you can see on the figure below:

how to map data in excel

The column “State” contains the names of the states. In the second column, you can find the codes; these will be very important later. Each and every shape gets a unique name, and by this, we will be able to identify them automatically when making the geographical state map.

In the third column, there are the values by which the VBA decides what color each state will be.

Our next task is to assign to each object the proper identification. First, highlight the shape and use the name box. We should name the highlighted area by the state codes in the data table.

hihghlight shape and assign the map data

We’ll display the names of the states for better understanding for everyone. All we have to do is to insert a textbox and position it over the state. For consistency, we should aspire to use the data table’s proper codes.

geographical state map add label

The next step is the most time consuming, but it is worth it because the end result will be remarkable. Name all 50 states using the Name box and also make the labels using the textbox.

Build the Legend and define Color Scales

Let’s define the color scales and values with formulas that our macro will use to update the colors and the texts of our geographical state map.

The data range definition, in this case, is rather simple. The minimum and maximum values can be simply readout of the data table “Values” column. We will break up this range into groups with the use of a statistical formula.

In our example, the interval between the minimum and maximum values we’ll divide into 16 parts for the most detailed visualization. In other words, we’ll be able to create such a map chart that will show the difference between the values in 16 separate colors.

Try for the most realistic display! Are we would work with only a 5 color scale we would excessively simplify the end result, and it wouldn’t be the best.

With the help of the PERCENTILE formula, we can set between the minimum and maximum values an arbitrary interval:

 =ROUND(PERCENTILE(scale_values,0.06),0)

The first parameter of the formula is the “scale_values” range; we’ll define the lowest and highest values that we’ll divide. In our example, the second parameter of the formula means that we create the categories in every 6%, altogether 16 of them.

Because 100/16=6,25, with the ROUND formula we round it up to an integer, this is how we get the 0,06 value which equals 6%.

In the first column, we displayed the received intervals; these change dynamically depending on the actual values. The second column shows the given interval lowest value. And in the third column, we displayed the color belonging to the given range.

apply colors on shapes for maps

How to automate the geographical state map using VBA?

We are done with our data tables; nothing left to do only to implement to color and refresh all shapes with regard to all relevant values.

Create a dynamic map

With three short macros in Excel, we make the state map dynamic. Let’s see the first one! Here we determine every shape’s characteristics, every single state’s characteristics of the state map chart. After this, we determine how thick should be the line dividing the shapes.

Finally, we fill the shapes with color.

To insert macros, open the VBA window (Alt+F11) and insert a module and copy/paste the following codes into the module.

Sub ColorShape(szShape As String, rgbColor As Long)
    Dim linewidth As Double, transparency As Double
    ActiveSheet.Shapes(szShape).Select
    ActiveSheet.Shapes(szShape).Fill.Solid
    ActiveSheet.Shapes(szShape).Fill.ForeColor.RGB = rgbColor
    ActiveSheet.Shapes(szShape).Fill.transparency = transparency
    ActiveSheet.Shapes(szShape).Fill.Visible = msoTrue
    linewidth = 1
End Sub

Assign the colors to the geographical state map

The ‘ColorArea’ macro works really simply. We store the start points of the intervals in the range named ‘scales’. With the help of Next loop, the macro reads all the intervals and the colors assigned to them; this will be drawn out on the geographical state map.

Sub ColorArea(areaname As String, dValue As Double)
    Dim i As Integer, rgbColor As Long, wksname As String, wksOptions, scales As Variant
    scales = Range("scales").Value
    For i = UBound(scales) To 1 Step -1
        If (dValue >= scales(i, 1) Or i = 1) Then
            rgbColor = Range("color" & i).Interior.color
            i = -1
        End If
    Next i
    ColorShape "S_" & areaname, rgbColor
End Sub

Map Data using Shapes

The ‘ColorMap’ will do the dirty work. There are two information can be found in the “data” range: the identifier (area) of the given shape and the actual value (aval). With these, the macro defines the color code of the given state and colors the state map.

Sub ColorMap()
    Dim Data As Variant, i As Integer, area As String, aval As Double
    Data = Range("data").Value
    Sheets("Map").Select
    For i = 1 To UBound(Data)
        area = Data(i, 1)
        aval = Data(i, 2)
        ColorArea area, aval
    Next i
    DoEvents
    For i = 1 To 16
        Range("scale" & i).Interior.color = Range("color" & i).Interior.color
    Next i
End Sub

In order to start the macro easily, we’ll insert a command button. (In the Developer tab, go to Insert > Button). If the developer tab is not visible, we have to authorize that manually. Display the Developer tab on the ribbon if it is not shown!

map control using macros

After we inserted the button give it a name so everyone can understand its function. Finally, assign the code responsible for the coloring of the state map.

how to build an excel map

We are all done. From now on, you can freely change the values in the data collector table. All we have to do is to click on the command button placed on the map sheet. The state map will automatically refresh based on the given settings.

Let’s see a little supplementation: We have created 6 color schemes so everyone can find the most suitable for them. You can change between them by the radio buttons.

Best Practices for designing Geographical State Maps in Excel

Let’s take a look at four important rules that are best to keep:

  1. Always choose simple borderlines. There’s no need to over-complicate it, or the focus will shift from the essence. You should choose the color scale very carefully. The goal is not to create a rainbow-colored map. Or to choose a scale that is too contrasting. It is best to use one or maximum of two colors and their shades.
  2. If you choose to use patterns (we don’t recommend them at all) you have to make them carefully, so it is the same on all the elements of the map.
  3. If there are several different patterns on one map that could be troublesome. So just be frugal with the colors.
  4. Choose the data range carefully, build it up the intervals according to distribution.

Questions and Answers regarding Excel maps

Is there any disadvantages to shape based Excel maps?

To our best knowledge, there is only one notable. One of the backsides of conditional formatting is that is considerable can increase the size of the workbook. We recommend that if you make a geographical state map in Excel, prefer the method based on shapes.

How long does it take to create a more complex geographical state map?

As we mentioned before the maps of every country can be downloaded from HERE. Because you get the codes ready, it takes about 30 minutes to create the data visualization.

Is there any limitations regarding the usable objects? Can I only use the Excel built-in objects to make a geo map?

Probably not, virtually any object can be shaped to the needed from. For example, take a look at the resource section, there we introduce you a special map visualization.

I see that the VBA project contains open source codes. I would like to use them for private purposes. Can I do that?

Above all, the article was created so every reader can get interesting experiences regarding the geo maps. Source codes can be used freely for private and commercial purposes also.

Conclusion

As you can see with the help of maps, we can give solutions to manifold business and economy problems using data visualization. Some people think that this procedure only means the coloring of cells.

We hope that applying the today represented examples proved that virtually in any situation this tool is an effective solution.

Download the free US map template!

Additional resources