Yesterday is Blog Action day and tons of bloggers posted about single topic – poverty. It is a topic very close to my heart for various reasons. It is a very sad thing not to have food or shelter or healthy living conditions. But man has thrived in all those situations just because he learned how to become better. In my opinion lack of knowledge is much more serious than lack of food. That is one reason why even the poorest parents in the world would love to see their kids going to school, so that they can learn and become better. I want to sensitize my readers about this issue: the new poor – people without information.. Internet penetration can be considered as one parameter to measure information hunger in the world. So I spent sometime yesterday gathering the data about this and created a heat map.
Mapping Internet Penetration Rates in World Countries
I took the data about internet penetration from Internet World Stats and used this in the World heat map excel that my friend Robert (of Excel Dashboards fame) once shared with me to create a map like this:
Download the excel file with the map of world internet penetration rates
Darker countries have less internet penetration. Sadly this map correlates highly with a world poverty map. I guess attacking the information access issues can eventually solve the poverty.
Also checkout Nathan’s visualization on poverty rates in US
18 Responses to “The New Poor – Internet Penetration Rates by Country”
Chandoo, interesting post, as always. Dedicated reader here. One thing though: I thought that Scandinavia looked somewhat funny and had a closer look. The colour range is so wide that the countries with the highest internet penetration are assigned such a light colour that they don't show on the map, for example Norway and the Netherlands are invisible. (Not that most Americans would notice )
A few les grades with the lightest colour being distinguishable from white would be better.
Keep up the good work
Teylyn
@teyln .. thanks. When I first prepared the countries with higher penetration rates were dark and thus drawing focus. Since I wanted to shift the focus to the ones with lower penetration rates I shifted the RGB colors by subtracting them from 255. I guess few countries have managed to get the 255,255,255 value.
Btw, if you have downloaded the excel, you can adjust the color grade from the control sheet, just set a value closer to 5 to see darker shades..
teylyn, Chandoo,
as Chandoo pointed, he shifted the RGB colors 'upside down' to make it a 'the darker the less' heat map.
Because of that, the mapping of values to colors ends at 0 with an RGB of (255,255,255), i.e. a white fill color.
Therefore the countries with the highest penetration (Netherlands and Norway, both 88%) get assigned a white fill color and are invisible. Increasing the color grade (control!D6) will not help, because this cell only controls the increase of the steps, starting with the last one.
But you can easily change the last value of the steps (control!D58) from 0 to let's say 15 or 20. This will bring Norway and Netherlands back home to earth ...
Teylyn, Chandoo,
a correction: it should be control!C58 in the last paragraph of my recent comment. Sorry...
@Robert ... thanks for suggesting how to change the whites to something slightly dark. 🙂
Mate nice display with World map.
Anybody knows how to include additional countries into the map?
I realized Taiwan wasn't included and tried to include it. However how do you go about drawing the countries in the map and link it with the cell?
I've tried reading from http://www.tushar-mehta.com/excel/charts/0301-dashboard-conditional%20shape%20colors.htm , however it seems that robert desined the map slightly differently.
I was wondering if anyone could help me. I want to add Taiwan (which is missing) into the map.
How do I go about doing it?
Which autoshape should I use to draw the country and what is the method to link the shape to auto-update when the redraw button is pressed?
This might be a duplicate comment (first one was not accepted). If so, I am sorry...
Jiaming,
please send me an email (use the email-link on my blog clearlyandsimply.com) and I will send you a version including Taiwan.
[...] Using Maps in Excel. Very interesting. Can be customized for your particular needs. [...]
how would you build a legend for this?
gray1 = 0-10% | gray2 =11=20% | etc.
@Andrew: color scales are very common in visualization world. We can have one common legend with a gradient bar (start with lightest gray and go on up to darkest) and label the first and last points.
Its a long shot that anyone will read this but here goes! 🙂
I'd like to recreate something similar to this, but only showing the UK, with county boundaries.
I have to compile reports each week and normally use GIS, linking the spreadsheet with the boundaries shapefile and then taking a screenshot and pasting it into excel. It'd be nice and far less fiddly if I can get it automated like this 🙂
Hope there's someone reading anyway 😉
Rich
@Rich
Download the example at the top
http://chandoo.org/wp/wp-content/uploads/2008/10/the-new-poor-internet-penetration-rates-by-country.zip
Pull it apart to understand how it works
Replace with the UK County boundaries
Have a go, The forums are here to help you
Thanks Hui, I tried that but it went mostly over my head 😛
This was really useful though:
http://www.tushar-mehta.com/publish_train/xl_vba_cases/0301-dashboard-conditional_shape_colors.htm
It made much more sense following it step by step than by trying to dissect the above 🙂
How do we redraw the map with new values? When I hit the Redraw Map button, I get the error message "Cannot run the macro "the-new-poor-internet-penetration-rates-by-country.xls'!UpdateMap'. The macro may not be available in this workbook or all macros may be disabled." Any advice?
Never mind - I figured it out. Great map!
Hello Excel-Experts,
is it possible to show the name of the country plus the selected value under the name?
(e.g. show "United States" "10%"; "France" "15%")
2.) how can I group countries to regions (e.g. "ASEAN" containing some Asian countries?
3.) how can I add countries by myself - I also need "Taiwan"
[unfortunately I am only Excel user and have no clue about VBA programming 🙁 ]
Thank you in advance!
Stephan