World Map with Population Indicators

pic1 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 ChartsUS Map with Smiley FacesUS Thermal Map

msofficeshare logo

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.

world map
pic3 world map with population indicators

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.

pic4 world map with population indicators
pic5 world map with population indicators

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.

pic6 world map with population indicators
pic7 world map with population indicators

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.

pic8 world map with population indicators
pic9 world map with population indicators
worldmappopulation
pic10 world map with population indicators

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