Excel is known as a complex number cruncher that only experts can use, but, in my view, labeling the program with such a sweeping generalization undermines its ability to make life easier. And in no scenario is this truer than in its wish to help you visualize data more efficiently.

That brings me to heat maps, which you can easily create in Excel to represent values relative to each other using colors.

What Is a Heat Map and What Are They Used For?

In today’s fast-paced world, where everyone seems to be in a rush, displaying data in a way that can be easily interpreted and analyzed—such as in a heat map—is essential. Excel lets you automatically color code figures to demonstrate their relationship with one another, using darker colors for higher numbers and lighter colors for lower numbers, for example. This means you can see trends and anomalies at a glance.

But this Excel tool isn’t exclusively reserved for corporate finances or complicated data analysis. Indeed, you can use Excel to create heat maps for pretty much anything, from displaying your sports team’s on-field strengths to showing how climate change is impacting temperatures over time.

The colors in the examples above are automatically generated using numerical data (which has been hidden—we’ll look into this later) and conditional formatting. Let’s explore in more detail how this can be done.

Excel has a separate tool for creating geographical heat maps (for example, if you wanted to color each country based on GDP per capita or millimeters of rainfall per year). However, in this article, we’re going to explore how to create all other types of heat maps manually.

Create Your Data Set

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 Rule s” 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.

---

Heat maps are just one of the many ways to visualize data in Excel, and which method you choose depends on the type of data you have, and how you want to present it. For example, you can create dynamic charts with dropdown lists, insert a combo chart that combines a column and line graph into a single chart, and use pivot tables to analyze your data more comprehensively.

Credits: