Create free map templates in Excel! 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 simple state maps 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 2010. They can create an 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 you can download the free map templates. 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 excel state maps!
Table of Contents
- 1 Free Excel Maps – Under the hood
- 2 How to map data in Excel?
- 3 How to automate the Excel maps using VBA?
- 4 Best Practices for designing Free Maps in Excel
- 5 Excel Mapping – Online or Offline?
- 6 Download Free Excel Map Templates
Free Excel Maps – Under the hood
The question arises: what is under the hood? Fortunately, this is the subject you don’t have to deal with. With a little modification you can personalize your map! By using the link on the bottom of the page you can obtain the templates in a minute. We recommend you to play with the data and try to make the best of it. It is worth to start because you only need a little practice and the results (success) will not fall behind.
Using mapped data for detailed and special analysis will succeed a perfect understanding of an industry’s details, which is too unspecified without map visualization. In our work we endeavored to use the utmost resolution vector objects and we accomplished to get a sharp picture even when using arbitrary enlargement.
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 in 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 US 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 a 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 for Maps
Let’s define the color scales and values with formulas that our macro will use to update the colors and the texts of our US 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 Excel maps 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 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 the Next loop, the macro reads all the intervals and the colors assigned to them; this will be drawn out on the Excel 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 in Excel using Shapes
The ‘ColorMap’ will do the dirty work. There are two pieces of 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 Free 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 a 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. One of the backsides of conditional formatting is that it is considerable can increase the size of the workbook. We recommend that if you make a 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. For example, take a look at the resource section; there, we introduce you to 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.
Excel Mapping – Online or Offline?
The question is not simple. Both solutions – online and offline – has its advantages and disadvantages. In our example, we focused on portable solutions. In some cases, it can be useful if we don’t force online connection at all casts.
You can simply copy the map on to your notebook or tablet, and you can take it with you anywhere. Who didn’t face the problem that is the most critical situation, just lost internet connection? When you use these maps, you will not face such problems.
The base vector map and VBA engine are provided via Excel, so an Internet connection is NOT required for mapping data. Our mapping solution makes it easy for all Excel users to create interactive maps, sales presentation, or data visualization. It is worth to pay attention to these three words: quick, interactive and efficient.
Download Free Excel Map Templates
In this section below you can download versatile free excel maps. Don’t forget: We made a world heat map for you!
- Australia State Map
- US Power Maps (4 templates)
- UK Map
- Canada Map – Gauge and POI version
- Germany Map Template
- Netherlands Map
- France Map
- World Heat Map Template
Conclusion: Create your own Map in minutes!
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 today represented examples proved that virtually in any situation, this tool is an effective solution.