World Map with Population Indicators
If your company is a multinational corporation, you may be requested to generate reports to show the international performance. It comes in handy to have a world map and icons to show the relative performance on your dashboard.
Please see the following steps how I created this report. Knowledge of Visual Basic Module is required.
Step 1: Create the data
Requirement: Visual Basic Knowledge
File to download: World Map – Population.xlsm
Related Articles: World Map with Bar Charts, US Map with Smiley Faces, US Thermal Map
Step 3: Find the icon that you want to use as an indicator. In my example, I used a family icon for illustration. For other reporting purpose, you can use a dollar bill, a product, a toy, a company logo, an oil rig, etc. as indicators.
Step 2: Try to find a world map. Google Image or Microsoft Clipart is a good start. You may want to avoid copyright issue by downloading images from government websites or websites that offer free and non-copyrighted images.
The image you pick might contain white area and it will look a bit ugly when you place it on top of your world map. You can modify your image by setting those white areas as transparent color. In Microsoft Office Suite, you can double click the image until you see the image format menu. On the left hand side of the “Format” menu, there is command called “Recolor”. Click on it and choose “Set Transparent Color”. Your pointer will then be changed to a dropper. Now move the dropper to the white area of the icon and click the white area. Now the white area of the image will be gone and become transparent.
Step 5: Name each icon as the ones that show up on your data. For example, name one icon as “China”, the others as “Russia”, “India”, “United States”, and so on. Right click your mouse on the image. Then go to the upper left part of Excel and type in the name in the naming box. Please remember to press “Enter” key when you finish typing in the name.
Step 6: Create the Visual Basic Module to automate the re-sizing of the icons.
My logic is to have all icons related in size to the country with the largest population (China). So I used this code to find the country with the largest population.
Step 4: Copy the icon as many times a needed. For example, if you need it for the top 10 countries, you need 10 of such icons. If you need that for 50 US States, you need 50 of them.
The re-sizing of the icon depends on the population of a country relative to China. For example, the population of China is 1,355,692,576 as of 2014 and the population of the United States is 318,892,103 for the same year. The ratio of the US relative to China is about 24% and the icon of the US should be about ¼ of the size of the China icon.
However, there is one key point that we should not forget. If it is a one-dimension graphic (e.g. a line), the graphic will show up as ¼ of the length of the China icon. However, our chosen one is a two-dimension graphic. If each side of the graphic is ¼ of China’s size, the size of the US icon will be only 1/16 of the China’s icon (¼ * ¼). Please look at below illustration for explanation.
The correct proportion of US relative to China’s population should be about ¼ like below chart. However, when apply ¼ of each side of the square to the graphic, the US icon becomes only 1/16 of the size of China icon. Apparently, it’s not correct. To reverse that, I raise the ratio by a power of ½ to make it a square root. This will offset the effect of squaring the proportion.
Set CountryRange = Range(“MapData”)
MaxDataValue = Application.WorksheetFunction.Max(CountryRange)
Then I will create a loop to read in the data from each individual country one-by-one.
For CountryCount = 1 To 10 ‘Data of Top 10 most populated nations
…………………….
Sheets(“Data”).Select
ActiveCell.Offset(1, 0).Select
CountryName = ActiveCell.Value
CountryValue = ActiveCell.Offset(0, 1).Value
Next CountryCount
The “*100” in the below formula is to raise the number for percentage to general number. You can multiply the formula with different numbers to control the size of the icon.
So my codes of the ratio calculation are as follow:
CountryPercent = (CountryValue / MaxDataValue) ^ 0.5 * 100 ‘Calculate the relative size of the Country Values
‘Raise to the power of 0.5 to cancel out the effect of squaring the icon
Sheets(“Map”).Select
With ActiveSheet.Shapes(CountryName)
.Height = CountryPercent
.Width = CountryPercent
End With
The entire codes in the Visual Basic Module is as below:
To activate the VB Module after you finish inputting the data, please press the button on the “Map Data” worksheet.
Below is the final report.
Sub MapPopulation()
Dim CountryRange As Range
Dim MaxDataValue As Double, CountryValue As Double, CountryPercent As Double
Dim CountryCount As Integer
Dim CountryName As String
Application.ScreenUpdating = False
Set CountryRange = Range(“MapData”)
MaxDataValue = Application.WorksheetFunction.Max(CountryRange)
If MaxDataValue = 0 Then
MaxDataValue = 0.01 ‘to avoid division by zero error
End If
Sheets(“Data”).Select
Range(“FirstData”).Select
CountryName = Range(“FirstData”).Value
CountryValue = ActiveCell.Offset(0, 1).Value
CountryCount = 1
For CountryCount = 1 To 10 ‘Data of Top 10 most populated nations
CountryPercent = (CountryValue / MaxDataValue) ^ 0.5 * 100 ‘Calculate the relative strength of the State Values
‘Raise to the power of 0.5 to cancel out the effect of squaring the icon
Sheets(“Map”).Select
With ActiveSheet.Shapes(CountryName)
.Height = CountryPercent
.Width = CountryPercent
End With
‘Debug.Print “CountryName”, CountryName, “CountryValue”, CountryValue, “CountryPercent”, CountryPercent, “MaxDataValue”, MaxDataValue
Sheets(“Data”).Select
ActiveCell.Offset(1, 0).Select
CountryName = ActiveCell.Value
CountryValue = ActiveCell.Offset(0, 1).Value
Next CountryCount
Sheets(“Map”).Select
Application.ScreenUpdating = True
End Sub
