Thematic Maps With Ease: Data-Based Color Changes

Recently I was asked by a customer if DataPoint can show thematic maps, colored by data. A thematic map is a type of map that portrays the geographic pattern of a particular subject matter in a geographic area or shape. Of course, the answer was yes, but you need to apply a trick to make it work. Setting the fill color of given hand-drawn shapes was an obvious idea, but that is not possible (yet). So I came up with this solution. Follow me.

Start with a new presentation like this below. Add a background image and draw the 3 areas or maps on your slide. We start by inserting the maps with whatever fill color.

Then we have the data stored in an Excel file for this example. With DataPoint you can connect to any data source and get the information from there. In the datasheet, we can find the name of the map, then the value that we want to show, and a calculated field.
We want to color the maps in color A when the value is smaller than 60. Fill it with color B when the value is between 60 and 80, and set the fill color to C when the value is 80 or more.

The calculated field holds this formula:

=IF(B4<60,1, IF(B4<80,2,3))

So the IF function is used twice to calculate first if a value is lower than 60. Then we check if the value is smaller than 80, and otherwise the value is 80 or higher. All possible values are covered. Copy down this formula to every line of data. The result is that this calculated field will hold values 1, 2, or 3.

That value 1, 2 and 3 corresponds to the 3 color variations that we want to use. So we have a folder for each map or regions, and in there, we have 3 color variations of the same map.

We are using DataPoint to show up-to-date presentations or to show real-time information on a television screen. Click DataPoint and then click the List button of the Connections group. Next, locate the Excel data provider.

Click the Add connection button and browse to the Excel file.

Select the datasheet that has your data and set the range in this case to A3:C6. Check the option that the first field contains field names, and hit OK to close.

You can see now a preview of your Excel data in PowerPoint. Now we can start the real work and bind PowerPoint shapes to this data.

Select the first map or image and click DataPoint and then Picture from the Shapes group.

Confirm that you want to make this picture dynamic.

At data, the connection selects the data query that you want to use from the list. Since we have set up only 1 query to the Excel file, will we only have 1 entry.

Then select the column ColorCode which is our calculated field holding values 1, 2, and 3 only.

At the preview pane, you can see the filename of the picture that is constructed, but that is not yet valid at this moment.

At the options pane, select the option Filename only. That allows us to specify the folder as well as the extension of the images that we are going to use.

Set the folder to the folder where we have all images of this map holding all color variations. And set extension to PNG as that is the file extension of the images that we are using here.

Now you will see a better and valid filename as we now have C:\Demo\Abu Dhabi\Al Dhafra\2.png. And this one is valid because we see the corresponding picture there. Hit OK to close and bind this shape to the color code value from Excel.

Repeat the same linking for the other maps on the slide and point them to other folder holding their corresponding images.

Now we have finished this presentation with dynamically filled map colors. Next time you open your presentation, the maps and colors will be updated and show the new updated values. Or you can send this presentation to a television screen and show the coloring of the maps and zones in real-time.