How to Create a Heat Map from Excel

If a website sells bus tickets, maybe their owners need to know the favorite destinations segmented by region of origin. A table may be useful, but a geographical map with heat zones is a lot better to see most popular regions.

These maps are useful to observe the interaction of two different regions: origin and destination. This can be used in transportation firms, travel agencies, or even to analyze the geographical performance of Adwords campaigns. Google's Geo Chart is a free and good option to create these maps.

Geochart uses a Javascript code where you should insert numeric values associated with each region. This code defines many criteria for the map, such as its geographic and resolution levels (continents, countries, provinces, countries), and its colors.

Check this map with Spain's regions, see the HTML code to understand how to insert numeric values by region. The most recommendable thing is using the nomenclature ISO 3166-2 to identify each spanish region.

We can automatize the process if we create a dynamic code in PHP to retrieve the values directly from the URL, by using $_GET, and then inserting them in the Javascript code used by Google. This way, we'll obtain a dynamical map, whose heat zones will change according to the values inserted in the URL. This is a simple example based on Spain's map:

In this first example, Balearic Islands (ES-IB, in ISO 3166-2) have a value of 1, while Castilla y León has 8, so it becomes darker. Change the numbers inside the URL, and see how the map changes.

If you want to obtain this same map from Excel, there's a very simple solution: create a URL from a list of regions

If you want to get this map from Excel, there's a very simple solution: create the URL from a table with regions and numbers, and visit it from Excel. Download the complete Excel example.

A second option is using Phil Carto, which is must customizable than GeoChart, but harder to download, install and use. Some maps also have failures, since anonymous people design them.

This is the definitive PHP code to create a map in Google GeoCharts after adding values to the URL:



$array = array("ES-IB" => "Islas Baleares", "ES-MD" => "Madrid", "ES-NC" => "Navarra", "ES-RI" => "La Rioja", "ES-AS" => "Asturias", 
    "ES-CB" => "Cantabria", "ES-CE" => "Ceuta", "ES-ML" => "Melilla", "ES-CN" => "Canarias", "AD" => "Andorra", "ES-AN" => "Andalucía", "ES-AR" => "Aragón", 
    "ES-CM" => "Castilla-La Mancha", "ES-CL" => "Castilla y León", "ES-CT" => "Cataluña", "ES-EX" => "Extremadura", "ES-GA" => "Galicia", "ES-MC" => "Murcia", 
    "ES-PV" => "País Vasco", "ES-VC" => "Valencia");

    $serie = "";

    foreach ($array as $iso => $name) {
     if (isset($_GET[$iso]) && !empty($_GET[$iso]) ) {
     $serie .= "[{f: '".htmlentities($name)."', v: '".htmlentities($iso)."'},".htmlentities($value)."], ";

    $serie = substr($serie, 0, -2); ?>

    <html xmlns='' xml:lang='es' lang='es'>
	<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />
      <script type='text/javascript' src=''></script>
      <script type='text/javascript'>
       google.load('visualization', '1', {'packages': ['geochart']});

        function drawRegionsMap() {
          var data = new google.visualization.DataTable();
          data.addColumn('string', 'Country');
          data.addColumn('number', 'Popularity');

          var options = {'title':'Spain',
      'width':650, 'height':400, 'region':'ES', 'resolution':'provinces'};

          var container = document.getElementById('map_canvas');
          var geochart = new google.visualization.GeoChart(container);
          geochart.draw(data, options);

      <div id='map_canvas'></div>


Contribute with a comment comment0 Comments
mode_editContribute with a comment