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 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, you probably know PowerMaps and 3D Maps applications.
To run these maps, you must have at least Excel version 2010. Geographical Excel maps support the country, county, and city-level analysis. In today’s example, we show a modern and easy-to-use method! We can create your state maps in Excel quickly with custom Excel shapes and formulas.
Some programming knowledge is required, but you don’t have to worry about this. We provide maps with the source code in the article. In addition, you can download the free map templates. Take a closer look at the map above!
It looks good!
What about Excel 2010 and earlier versions? Fortunately, there are solutions for the users of these versions also.
Free Excel Maps – Under the hood
The question arises: what is under the hood?
Fortunately, with a bit of modification, you can personalize your map! By using the link at the bottom of the page, you can obtain the templates quickly. We recommend you play with the data and try to make the best of it. It is worth starting because you only need a little practice, and the results (success) will not fall behind.
Using mapped data for detailed analysis will succeed a perfect understanding of the 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 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 US state map chart. For the first step, we’ll import a simple vector map that contains all of the states of the US.
Their common characteristic is that they are all free-form editable maps, so you don’t have to draw the states manually. Unfortunately, the shape-based maps are grouped by default, so we must separate them using the “Ungroup” function.
So we’ll be able to color every single state individually. Then, after the ungrouping, we’ll have a map just like this one below.
If we’d like to create some specific state maps, there are other solutions. 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. You can find the codes; these will be very important later. 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. Next, 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 of everyone. Then, all we have to do is insert a textbox and position it over the state. We should aspire to use the data table’s proper codes for consistency.
The next step is the most time-consuming, but it is worth it because the result will be remarkable. Name all 50 states using the Name box and 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 relatively simple. The column “Values” contains the minimum and the maximum values. Next, we will split this range into groups using a statistical formula.
In the example, we’ll divide the interval between the minimum and maximum values 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! If we worked with only a five-color scale, we would excessively simplify the result, which 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, we round it up to an integer; this is how we get the 0,06 value, which equals 6%.
We displayed the received intervals; these change dynamically depending on the actual values. The second column shows the given interval’s lowest value. And in the third column, we displayed the color belonging to the given range.
How to automate the Excel maps using VBA?
Our data table is ready. There is nothing left to do, only to color and refresh all shapes about 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 attributes of the state map chart. After this, we determine how thick the line should divide the shapes.
Finally, we fill the shapes with color. Then, open the VBA window (Alt+F11) to insert a module and copy/paste the following codes into the module to insert macros.
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 simply. First, we store the start points of the intervals in the range named ‘scales.’ Then, with the help of the Next loop, the macro reads all the gaps 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 that can find 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
To start the macro quickly, we’ll insert a command button. (In the Developer tab, go to Insert > Button). If the developer tab is not visible, we must authorize that manually. Display the Developer tab on the ribbon if it is not available.
After we inserted the button, give it a name so everyone can understand its function. Finally, assign the code responsible for coloring the state map.
We are all done. You can freely change the values in the data collector table from now on. All we have to do is click on the command button placed on the map sheet. Then, the state map will automatically refresh based on the given settings.
Let’s see a little supplementation: We have created six 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 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 you make a state map in Excel and prefer the method based on shapes.
Because you get the codes ready, it takes about 30 minutes to create the data visualization.
Probably not. Virtually you can display any objects using shapes. For example, please look at the resource section; we introduce you to a map visualization.
You can use the source codes freely for private and commercial purposes also.
Excel Mapping – Online or Offline?
The question is not simple. Both solutions – online and offline – has their 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 copy the map to your notebook, 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 issues.
The base vector map and VBA engine use Excel only, 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 presentations, or data visualization. Therefore, it is worth paying 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 economic problems using data visualization. However, some people think that this procedure only means coloring cells. We hope that applying today’s represented examples proves that this tool is an effective solution in any situation.