Free Map Templates

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!

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:

  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

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.

preparing shapes for maps in Excel

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

free map templates in excel

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:

collect states data

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.

add a name for states

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.

add labels for states

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:

=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.

map chart colors for template

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!

insert map control

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.

assign macro to free map templates

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:

  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 a 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

Are there any disadvantages to shape-based 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.

How long does it take to create a more complex geographical excel 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.

Are 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. For example, take a look at the resource section; there, we introduce you to 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.

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!

  1. Australia State Map
  2. US Power Maps (4 templates)
  3. UK Map
  4. Canada Map – Gauge and POI version
  5. Germany Map Template
  6. Netherlands Map
  7. France Map
  8. 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.

Additional resources

How to create a KPI Dashboard?

Free Excel Dashboard Templates