How to use Excel to create heat maps

Situatie

The first step is to create your statistical data in its simplest form. If you’re starting with a blank worksheet, type your column and row parameters, and insert your data. If you wish, you can format your Excel table so that it’s easier to add more data later on. If you already have your completed data set, make sure it’s presented in a way that lends itself to creating a heat map in the next step (such as removing empty rows and columns in a table).

To generate the two sample heat maps shown above, we started with this (details of the number of bonuses each employee received each month):
And this (how many goals were scored from a certain location on a soccer pitch):
To create the soccer pitch in Excel, I inserted it as a PNG image, meaning the cells underneath the graphic remained visible. You can do the same with any image outline to create a heat map in Excel.

Apply conditional formatting

The next step is to apply the color scales to your data. First, select all the cells that will form the heat map. In the example below, I’ve selected all the cells on the soccer pitch, so that any data I might add later on will also be picked up by the color rules I set.
If you are applying the conditional formatting to cells underneath an image, you’ll need to use your arrow keys to navigate to the correct cell, as you can’t select a cell underneath a graphic using your mouse. Then, hold Shift while using your arrow keys to select the relevant cells.
Next, click “Conditional Formatting” in the Home tab on the ribbon, and hover over “Color Scales.” From there, you can choose the color scale that works best with how you want to display your data.
In my case, I’ll choose the “Green To Yellow” scale.
If none of the preset options pique your fancy, click “More Rules” instead. This will launch the New Formatting Rule dialog box, where you can switch to a three-color scale (rather than the default two colors), with more specific rules about how the values affect the colors to be displayed.
To change or remove the color scale after you have applied it, select the cells again, click “Conditional Formatting,” and select either “Manage Rules” or “Clear Rules.”

Remove visible values and gridlines (Optional)

The final step in optimizing your heat map involves hiding the figures and removing the gridlines, if doing so will improve your data visualization.
To hide the figures, select the cells to which you applied the conditional formatting in the previous step. Then, in the Home tab, click the “Number Format” icon in the bottom corner of the Number group.
Then, click “Custom” in the Category menu, and type ;;; (three semicolons) into the field box.
When you click “OK,” the numbers will disappear from the cells, though you can still see them in the formula bar when you select the relevant cells.
Removing the gridlines is much more straightforward. In the View tab on the ribbon, uncheck “Gridlines” in the Show group.

Solutie

Tip solutie

Permanent

Follow Us