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? 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.
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.
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:
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)
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 sure My table has headers is checked then click OK
Create the Map
- On the Excel ribbon, click Insert > 3D Maps (or Map) > Open 3D Maps (or Launch 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.
Add a Numeric Field
- In the Layer Pane window, click the heat map option (4th 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 (2nd down, 3rd 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.
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.
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 us when 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.