Excel has many fantastic tools to present data. You can create graphs and present statistics, or just overall swarm the receiver with numbers. One way of doing this is to create a dynamic heat map data that will instantly give your audience a way of understanding what you’re trying to say.

Visual representation is one of the most important things when it comes to functionally communicating in the workspace, and the Excel heat map is a great way of accomplishing that task. So, in this guide, we’ll explain how to create this kind of graph. 

As this guide takes place in the wonderful world of Excel, you’ll probably need one to go through with it. If you don’t have one, well - you’re in luck! Here at Royal Cd Keys, we have some of the best offers for you. Within those, you will find Microsoft Office for a very affordable price! Now, let’s get on with the guide.

What Is a Heat Map?

A heat map is, in some way, a kind of visualization of data. Depending on the format style drop, heat maps have color scales that represent all new data in all the cells. For example, you can set it out to make the more concentrated data with higher numbers glow red, and all the remaining values start to cool down from orange to a bright green color.

What Is Heat Map Used For?

It is a very good representation of data as they will instantly show you where the most data is concentrated and what elements are on the “colder” side. It’s mostly used for weather analysis, by scientists, and by marketers who present patterns of customers' tastes, for example. In those times, you can often see a heat map used by economists to show how inflation changed throughout the years and how it’s different between countries. In Excel, a heat map looks like this:

Creating a Heat Map In Excel

So, now that we’re all familiar with the concept of a heat map, let’s go right into some practice. We’re going to try to explain it as simply as possible.

Step 1 - Picking the Data

The first thing you need to do is enter the data into Excel. It may sound trivial, but if you put your data efficiently, you will save yourself a lot of time and trouble in the future. So - remember to put the data in the cells next to each other. It will be a lot easier to compile them in our formula.

Next, select your data with a left click. You will need to highlight all the information you want to be included in the heat map.

Step 2 - Choose Formatting

Next, you will need to choose the formatting you want for your heat map. To do so, go to your Home tab and go over Styles. There, click on the Conditional Formatting and go to Color Scales. Choose the ones you like, and voila - you have your own heat map created with all the data points you need in different shades.

Step 2.1 - Custom Colors

As we’ve explained in the first few paragraphs, a heat map shows you all the values from the “hottest” to the “coldest” depending on the values you put into cells. Those colors would be green for “cold,” yellow for medium, and red for “hot.” Everything in between depends on the intensity of the value. 

But - as with all things Excel-related, you can just go ahead and ignore this suggestion and create your own color scheme.

First, go to the Home tab again, Styles again, Conditional Formatting, Color Scale and here we’ll need to do something different. Go to More Rules. You will see the New Formatting Rule window. There, you’ll need to pick the colors you want for your heat map. Excel will give you three options to fill out - Minimum value, Maximum Value, and the Midpoint. 

You can now put the entire data into the color combinations you desire.

But - it’s not all; as you may have presumed, there are many different ways to create a heat map, and you don’t even need to use numbers. 

How to accomplish that? Well, first, you’ll need to select your heat map. Then, press Ctrl and 1. Format Cells dialog box will open. There, you’ll have to go to the Number tab, then - Category, and select a Custom bracket there. There is a Type Box there, you’ll need to click it and put three semicolons in it (;). Click ok, and it’s done!

Tired of rectangles? What about squares? You’re not limited to a single layout while creating a heat map. You can do it - your style. To do so, you’ll need to align column headers vertically. Go to the Home tab and pick the Orientation button. Click - Aligning column headers vertically.

Now you need to align the columns. To accomplish that, you’ll need to select all the columns you want to be squared, pick them up by the header with the left click and drag them to make them wider or narrower. A sudden tooltip will appear with a pixel count. Write this number down somewhere; it’ll come in handy later. 

Now we need to set the height of the rows. Do the same thing with rows as in the case of columns. A wild toolkit will appear again. Put the pixel value there, the same as it was in the case of rows. And - voila, now you have your perfect squared Excel cells.

So, now that we have the simple stuff out of the way, let’s get into something more complicated, by which we, of course, mean PivotTables. Everything goes pretty much the same, but there is one, very important difference. When you add new data to the source table, it will not get applied to the data in a heat map.

How to make it more dynamic? Well, there is a way. First, go to the Home tab, Styles, Conditional Formatting. There, click the Manage Rules button. A Conditional Formatting Rules Manager will pop up. You’ll need to click on Edit Rule.

Edit Formatting Rule Box will show up. There, go to Apply Rule To and select the third option. Click Ok twice to close everything. Now - you have your own, personal heat map Excel Pivot Table style with dynamic updates. To see the changes, refresh it.

Here’s another conundrum. But - we’re happy to help. Sometimes, you don’t want your heat map to be seen at all times. Maybe you want to surprise someone with the excellent Excel skills you’ve learned with us. It’s just a maybe, but it’s really more than probably, to be honest.

Anyways, what you want to do is to create a checkbox to do so. And here is how you accomplish that.

First, click on a cell with no cell values in it. Then, go to the Developer tab, Insert, Form Controls, and Checkbox. Now - you want to connect your checkbox to your cells. For that, right-click the checkbox and select - Format Control, Control tab, and enter the cell address into the Cell link box. To confirm it - click OK.

Now what you want to do is to create conditional formatting. Select your data and click Conditional Formatting, Color Scales, More Rules, and configure your color scheme the same as we’ve explained before. Now - select a 3-color scale.

Find Minimum, Midpoint, and Maximum, and select Formula for each one of them in the Type drop-down list. Go to the value box and enter the formula. X and Y is the place of your checkbox and A, B, and C, D are the locations of your data cells.

So, for Minimum, enter:

Midpoint:

Maximum:

Then - click ok to end the process.

And lastly, to add everything we’ve learned so far, we’re going to add a way to create a dynamic heat map without numbers. 

Select your data. Go to the Home tab, Styles, New Rule, and select - Use a formula to determine which cell to format.

Put this formula there:

Where X and Y are your linked cell. 

Now - click the Format… button. A dialog box will pop up, go to the Number tab, Custom, and then Category list. There - put three semicolons in the Type box again and lick OK.

Click the Format… button.

In the Format Cells dialog box, switch to the Number tab, select Custom in the Category list, type 3 semicolons (;) in the Type box, and click OK and OK to close the New Formatting Rule dialog box.

Now you can proudly say that you are a professional heat map creator. This was a very practical guide, but a rather basic one. There are a lot more things you can do in Excel. The possibilities are endless, so you can also come up with a new and exciting way to present your data. 

The whole concept of a heat map is very interesting and will definitely please your employer or office friends. It is also a very useful tool, especially if you want to describe how something changes gradually or how something is redistributed in a certain data set.

If you have any questions or you want us to cover any other software/gaming-related topics - let us know, we’re more than happy to help you out for sure.

Thank you very much for checking out our article on everything Excel related. As always - it would be great if you could spread the news about our efforts to become the number one place for creating guides on the internet.

Check out our other guides to ensure you’re on top of your Microsoft Office games.

In other news - be sure to check out our offers! It’s always the time and the season for some extraordinary software/games for you to get for literally a fraction of the original price. Royal Cd Keys prides itself in providing you with the best prices on the market - no strings attached.

Thanks for stopping by, and we’ll see you in the next one. Stay safe!