The Pop, Soda or Coke Visualization with Microsoft Excel
The previous post discussed Choropleth Maps in general, briefly described how to implement them with Microsoft Excel and provided a couple of templates for free download.
The post was focused on the classic version of a Choropleth Map, visualizing the measurements of a statistical variable in different geographical regions on a map by color intensity: the greater the measurement, the darker the color.
The classic version is limited to one variable. This follow-up post describes how to enhance a Choropleth Map in order to visualize more than one variable by using several colors and includes the visualization of “The Great Pop vs Soda Controversy” as an example for free download.
What is a multicolored Choropleth Map?
You may ask:
What exactly is meant by “multicolored”? Isn’t it the character of a Choropleth Map to have different colors?
Sure, a classic Choropleth Map visualizes the proportion of the data per region by different colors. But usually it is one color in different intensities (e.g. different shades of grey). And it is limited to one variable (e.g. population).
If you want to display more than one variable at the same time, i.e. add another dimension, you need more than one color.
What is it good for?
A very good example for a Choropleth Map using different colors is the visualization of election results. Most of the US presidential election maps I have seen, did not use different shades of the colors. They simply used blue or red to indicated who has won the state. However, if you want to have a visualization also indicating how many votes (in %) the winner of the state received, you need a multicolored map. The color (blue or red) represents the party and the color intensity represents the percentage of votes of the winner in each state (the darker, the higher).
In order to have more than two different colors in the example of this post, I used another data set: The results of a poll asking US citizens “do you say pop, soda or coke?”. The map visualizing the survey results here is county based and shows all the details. However, it would be a lot of laborious work to do this with Excel (more than 3,000 counties) and I do not have an editable map with US counties available. Thus, I simply consolidated the data and created a Choropleth Map by state:
Agreed, by far less detailed than the original, but the map still shows the overall results as well: pop in the north, coke in the south, soda in the south west, Florida, New England, etc.
Here is the template for free download:
Download Pop Soda Coke Choropleth Map (Microsoft Excel 2003, zipped 167.6K)
Using the drop down list on top of the map allows you to decide whether you want to see the total results (i.e. the winners, see the screenshot above) or the results per generic word:
How to?
The technique to implement a multicolored Choropleth Map with Microsoft Excel is pretty much the same as the one used and described in the previous post.
The following changes are necessary for a multicolored Choropleth Map:
- Add another column to the table that assigns names to shapes (cell range name “myMapNameToShape”). This new third column calculates the “winners”, i.e. the generic word with the maximum of the votes for each state. More precisely a combination of MAX and MATCH returns the position of the maximum (1: pop, 2: soda, 3: coke, 4: other).
- While looping through all states in the VBA Sub “UpdateMap”, one additional line of code writes the maximum position for the actual state to the cell range named “myActMaxPosition”.
- Add another cell (K12 on worksheet “Control”) to calculate the color range to be used: If the user selected a specific generic word from the combo box, the formula in K12 returns the user input (1: pop, 2: soda, 3: coke, 4: other). If the user selected “All”, the formula in K12 fetches the value of “myActMaxPosition” (see the previous bullet point).
- Finally the RGB color scale in “myMapValueToColor” is determined according to the result of cell K12. If the user selected “all”, a state where most people voted for “pop” is colored using the blue scale, a state where “soda” received the most votes is using the brown scale, etc.
That’s it.
Leave a Reply