Guide to Creating Action Heat Maps in Microsoft Excel

Step 1: Making the basic outline:

So, first we’re going to make the pitch on which the data is going to be plotted. To do this, just open up a new sheet in Excel and insert your pitch outline, preferably the following one, by going to Insert > Pictures > From This Device:

Step 2: Mapping your values:

In a separate sheet, input your x-y co-ordinate values. In three adjacent columns, create fields called ‘x bin’, ‘y bin’ and ‘bin id’ (so you should have 5 columns in total).

Step 3: Linking your values to the pitch:

In the bottom left cell of your pitch, use the COUNTIF formula, inputting the range as the range of your bin id values, and the criteria as the bin id of that cell.

Step 4: Creating the heatmap:

This is the easiest bit of the process. Simply navigate to one of the cells on the pitch, press ctrl+A, to select all of them, click on conditional formatting and go to ‘New Rule’.

Step 5: Saving the heatmap as an image:

This step is also extremely simple, but is annoying to do, in all honesty.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Rahul Iyer

Rahul Iyer

143 Followers

Journalism student, aspiring sports writer/amateur data analyst