A picture is worth a thousand words.  Or in this case a heat map video is worth a million data points.  The words may be different but the principle is the same, how can we show what is happening in a clear and concise way?

Heat maps are a representation of data in which values are represented by colours. A simple heat map will give you an immediate, easy-to-digest visual summary of information. A really good example would be a map of the United States during election time.

Being able to convey information obtained from data as simply as possible is at the heart of data visualisation.  A heat map allows us a view of data hotspots and clusters of activity.  When overlaid on a geographical map, these hotspots can show us where this activity is actually located.

So, how could your charity use it? Well, it’s can be an incredibly powerful and sharable way to show data. You could use it to show impact, but also where support is coming from. Take the recent Cumbria floods, by generating a heat map JustGiving were able to show where support was coming from and let those in need know that the whole country was pulling together.  These heat maps were then transformed into bite-sized social media videos to share the message of support.

So how did we create these heat map videos and how can you create your own?  It’s pretty quick and simple and you’ve probably already got the software you need…Excel 2013 (or 2016) on Windows with an Office 365 subscription.

Follow along by downloading the example CSV file or simply read along and see how simple it is.

Creating your own visual data visualisation – A step-by-step guide

1. Get Some Data

The most important step, what do you want to show?  We won’t go into detail regarding quality of data but make sure the data you use is as clean as possible (no character data where numeric data should be springs to mind).

We’ll be using fictional data which shows activity in certain towns on certain dates. The data contains 4 fields:

• Country
• Town
• Date
• Count

Enable the 3D Maps add-in by opening Excel then select:

• File > Options > Add-ins > COM Add-Ins (Manage drop-down) > Microsoft Power Map for Excel (click to enable)

2. Import the Data

• Open the CSV file in Excel
• Save the new workbook as HeatmapData.xlsx by saving as an .xlsx file
• Select the data between cells A1 and D101 then press Ctrl-T.  Make sureMy table has headers is checked then click OK

3. Create the Map

• On the Excel ribbon, click Insert > 3D Maps (or Map) > Open 3D Maps (orLaunch Power Map)

On the right-side in the Layer Pane you’ll see the geographical data mapping options, 3D Maps will have automatically picked up our Country and Town fields and mapped them to 3D Map’s own Country/Region and City fields respectively.

• Click Next to accept the geographical field mappings

On the right-side Layer Pane you may see a blue link with a percentage (e.g. 66%).  Click this and you’ll see the Mapping Confidence window.  Have a quick check, 3D Maps should give you several warnings about locations it wasn’t sure about.  If you check you’ll see it’s found the correct locations so you don’t have to do anything further.  Click OK to close.

4. Add a Numeric Field

• In the Layer Pane window, click the heat map option (4^th icon to the right in the bottom pane)
• Drag the Counts field to the Value box

We have now plotted our geographical and numeric data on our heat map so it’s time to start formatting the look and feel.  If there are any boxes overlaid on the map, then either close or right-click then select Remove.

We’ll format the heat map by using options on the Excel ribbon by selecting:

• Map Labels
• Flat Map
• Themes > Color White (2^nd down, 3^rd from the right)
• Scene Options > Scene duration (sec): 6:00

You can then drag the mouse to position the map.  Use the navigation and zoom buttons in the bottom-right of the heat map to ensure that the whole of the United Kingdom is visible.

5. Take a Screenshot

Once you’re happy with the map, on the Excel ribbon click Capture Screen.  This will save the heat map to your clipboard so now open your favourite image editor (Windows Paint works well) then paste (Ctrl-V) the heat map.  You can now edit the image and save.

6. Generate a Video

We don’t just want an image though; we want to show how the heat map has been generated over time.  As we have a Date field in our data which tells uswhen things have happened at locations, we can use this to animate the heat map.  To do this:

• Ensure the Layer Pane is visible and that our Field list is visible
• Drag the Date field to the Time
• Click the Time drop-down options and select Day.

A timeline now appears at the base of the map, which won’t be visible in the video, and you can click the play (arrow) icon to see the heat map evolve over time.

To generate the video, select:

• Create Video on the Excel ribbon
• Choose an appropriate option, we’ll select Computers and Tablets
• Click Create
• Select a folder to store the outputted video and click Save

You now have an mp4 video file of the heat map showing how activity changed and  evolved over time.

Excel 3D Maps has many options to create stunning geographical data visualisations, have a play and see what you can come up with.