US Map with Smiley Faces
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 Indicators, World Map with Bar Charts, US Thermal Map
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.
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.
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.
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.
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.
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.
Below is the final report.
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
