US Map with Smiley Faces

usmapsmileyface
msofficeshare logo

This example use Simley Faces as the statistical indicators.   The size of each Simley Face indicates the relative performance or the strength of each US State.  The statistics can be sales amount, sales revenue, product sales, returned items, happy indices, marriage rate, divorce rate, income, etc.  You can substitute the Smiley Faces with something else like oil wells, company products, population icons, dollar notes, etc. for your reporting purposes.  The chart looks cool and informative at a glance.  It is great for dashboard displaying.

Please see the following steps how I created this report.  Knowledge of Visual Basic Module is required.

Requirement:  Visual Basic Knowledge

File to download: US Map - Smiley Face.xlsm

Related Articles: World Map with Population IndicatorsWorld Map with Bar Charts, US Thermal Map

pic1 us map with smiley faces

Step 1: Create the data.  Please note that I need the column C (State Abbreviation) because I named the smiley faces by the state abbreviations.  If you name the smiley faces icon by the full name of the State, you don’t need column C.

 

Step 2: Try to find a US 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.

 

us map
pic3 us map with smiley faces

Step 3:  Find the icon that you want to use as an indicator.  In my example, I used a Smiley Face 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.

I created a Smiley Face by going to the “Insert” tab on the ribbon menu, then choose “Shapes” and the smiley face from “Basic Shapes”.  Afterwards, I did some formatting to make it colorful. 

pic3 world map with population indicators

If you use some of downloaded images, the images you pick might contain white area and it will look a bit ugly when you place it on top of your map.  Below example shows a family icon with white area around it.  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
pic6 us map with smiley faces

Step 4: Copy the icon as many times a needed.  In our example, there are 50 US States, plus Washington DC, Puerto Rico, and Virgin Island, so you need 53 of them.

 

Step 5: Name each icon as the ones that show up on your data.  For example, name one icon as “CA”, the others as “TX”, “NY”, “AL”, 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.

pic5 us map with smiley faces

Step 6: Drag each icon to its appropriate area.  For example, put the icon that you named as “CA” on top of the state where Calfornia is located.  Repeat Step 4 and Step 5 as needed.

The re-sizing of the icon depends on the value of such state relative to the state with the highest value.  You may see that the formula I have included a square root (^ 0.5).  The explanation is listed as below by using the example of comparing US population with China’s.

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.

pic8 world map with population indicators
pic9 world map with population indicators

I created a button called “Map Data” on the “Map” worksheet to activate the VB Module.  After I created a button, I assigned the VB Module to it.  Every time I finish inputting the data, I can press the button to run the VB Module to change the size of the Smiley Face icon to reflect the data I have.

pic7 us map with smiley faces
pic10 us map with smiley faces
pic8 us map with smiley faces
pic9 us map with smiley faces

Below is the final report.

pic11 us map with smiley faces

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 state with the highest value.  So I used this code to find the country with the largest population.

 

    Set StateRange = Range("MapData")

    MaxDataValue = Application.WorksheetFunction.Max(StateRange)

Then I will create a loop to read in the data from each individual state one-by-one.

    For StateCount = 1 To 53    '50 states plus Washington DC, Puerto Rico, and Virgin Island

   …………………….       

     Sheets("Map").Select

        With ActiveSheet.Shapes(StateAbbr)

            .Height = StatePercent

            .Width = StatePercent

        End With

                  

        Sheets("Data").Select

        ActiveCell.Offset(1, 0).Select

        StateAbbr = ActiveCell.Value

        StateValue = ActiveCell.Offset(0, 1).Value

    Next StateCount

The “*50” in the below formula is to raise the number for percentage to general number.  Normally, it should be multiplied by 100(%), but I think I initially made the Smiley Face Icon too big so I shrink them all across the board.   The number (* 50) is to control the size of the icon by shrinking it in half. 

So my codes of the ratio calculation are as follow:

 

StatePercent = (StateValue / MaxDataValue) ^ 0.5 * 50 

The entire codes in the Visual Basic Module is as below:

Sub MapSize()

    Dim StateRange As Range

    Dim MaxDataValue As Double, StateValue As Double, StatePercent As Double

    Dim StateCount As Integer

    Dim StateAbbr As String

   

    Application.ScreenUpdating = False

   

    Set StateRange = Range("MapData")

    MaxDataValue = Application.WorksheetFunction.Max(StateRange)

           

    If MaxDataValue = 0 Then

        MaxDataValue = 0.01         'to avoid division-by-zero error

    End If

           

    Sheets("Data").Select

    Range("FirstData").Select

    StateAbbr = Range("FirstData").Value

    StateValue = ActiveCell.Offset(0, 1).Value

    StateCount = 1

   

    For StateCount = 1 To 53    '50 states plus Washington DC, Puerto Rico, and Virgin Island

        StatePercent = (StateValue / MaxDataValue) ^ 0.5 * 50   '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(StateAbbr)

            .Height = StatePercent

            .Width = StatePercent

        End With

           

        'Debug.Print "StateAbbr", StateAbbr, "StateValue", StateValue, "StatePercent", StatePercent, "MaxDataValue", MaxDataValue

       

        Sheets("Data").Select

        ActiveCell.Offset(1, 0).Select

        StateAbbr = ActiveCell.Value

        StateValue = ActiveCell.Offset(0, 1).Value

    Next StateCount

   

    Sheets("Map").Select

   

    Application.ScreenUpdating = True

End Sub