Create geographical state maps in Excel for the US in seconds! Just put your data, and the editable template will visualize your data automatically!
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:
- Preparing Shapes in Excel
- Create the Data Table and Textboxes
- Create the Legend and Color Scale
- Write a short VBA code
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.
So we’ll be able to color every single state individually. After the ungrouping, we’ll have a map just like this one below.
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:
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.
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.
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:
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.
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!
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.
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:
- 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.
- 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.
- If there are several different patterns on one map that could be troublesome. So just be frugal with the colors.
- Choose the data range carefully, build it up the intervals according to distribution.
Questions and Answers regarding 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.
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.
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.
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.
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.