How to create a heat map in Excel

Heatmaps are used for parse a sheet with a large amount of datasince different types of colors will be used to represent all existing values ​​and thus be able to compare them in an easier way.

this is very clear way to help usso it’s one of those types of options that might become important after we try it, as it’s a very efficient way to analyze a lot of data.

Yes Do you think this heat map can help you in your work?we are going to tell you how you can get it in Excel, a program that excels in data processing.

You will see how much easier it is than you ever imagined.

Create a heat map with conditional formatting in Excel

We can create a heatmap using the function Conditional Format.

The best thing about this type of map is that if at any time we need to change the data, the map itself will be modified accordingly.

The way to do it is like this:

• We choose data which we want to include in the heatmap.
• We open a tab Start off.
• We are going to Conditional Format and then Color scales.
• Let’s choose one of the options which are shown to us, being able to try several by hovering over them with the mouse. We’ll see Excel show a preview of what each heatmap looks like.

Add Rules to the Heat Map

If we want to give a more professional look to our sheet calculation, we will need to add the rules to the heat map we made.

So it will have to match some of the modifications we make to it and thus it will be more like what we expected.

• we return to Conditional Format and then color scales.
• We choose manage the rules and then in New rule.
• We will be able to select a new rule from the list Select rule type.
• For example, we will choose the option Format all cells based on their values.
• Now in the “Format” style, we open the drop-down list and select 3 color scale.
• At that time we can edit the settings Minimum, middle and maximum. They may even be change colors to make the map easier to understand.
• When we’re done, all that’s left to do is click on To accept.

Heatmap to Excel PivotTable

A pivot table is one that has an interactive piece of data. These tables allow us to see the same data from different perspectives. in dynamic tablesyou can play with your data in different ways.

The way to create a pivot table is extremely simple:

• We open the Excel spreadsheet we want to analyze.
• We need to make sure that there are no empty rows or columns in your data, but only a row header.
• We allocate cells from which you want to create a pivot table highlighting your data.
• Now we surrender Insert on the menu and then dynamic table.
• Press From Table/Range.
• In the popup we check if the table/range is correct.
• We then choose where we want to place the PivotTable report.
• We choose new table place the pivot table on a new sheet or in Existing table.
• If we select an existing worksheet, we place the location where we want the pivot table to be displayed.
• It remains only to click on To accept.

Well, to this kind of tables you can also add a heat map to themalthough if you do as in the previous case, then it is possible that Excel does not apply the rules Format depends on the new data we enter.

The way to do it is like this:

• We allocate cells which contain relevant data.
• We are going to Conditional Formatting > Color Scales and select one of the available options.
• We save the selected cells.
• We are reopening the menu Conditional Format and we click on manage the rules. Excel will show a window Rules Manager conditional formatting.
• We push the button change the rulewe see that everything is correct and To accept.

Show map without number

If we want to render a heatmap, but without any intermediate numbers, i.e. to see only the colors that Excel labels, we can also do that.

This is done in the following way:

• We open a tab Start offselect cells and go to Format.
• Then we go to Cell Format.
• On the menu Categorychoose an option personalized.
• Then we write ;;; (three semicolons) and press To accept.
• We will see how the numbers have been erased.

Now you know how heat maps work in Excel and you can use them to make your documents more visual.

If you found it useful, you can tell us about it on our social networks, as we are happy to hear your opinion.