On the Number tab, under Category, select Custom.Press Ctrl + 1 to open the Format Cells dialog.To hide the cell values without removing them from the sheet, use custom number formatting. The heat map you create in Excel is based on the actual cell values and deleting them would destroy the heat map. In this custom heatmap, all the temperatures below 45 ☏ are highlighted in the same shade of green and all the temperatures above 70 ☏ in the same shade of red:Ĭreate a heat map in Excel without numbers Assign a color to each of the three values.įor this example, we've configured the following settings:.For Midpoint, you can set either Number or Percentile (normally, 50%).For Minimum and/or Maximum value, choose Number in the Type drop down, and enter the desired values in the corresponding boxes.Pick 3-Color scale from the Format Style drop down list.In the New Formatting Rule dialog box, do the following:.On the Home tab, in the Styles group, click Conditional Formatting > Color Scales > More Rules.In case you want to highlight all the cells lower/higher than a given number in a certain color irrespective of their values, then instead of using an inbuilt color scale construct your own one. All the remaining values get different shades of the three main colors. When applying a preset color scale, it depicts the lowest, middle and highest values in the predefined colors (green, yellow and red in our case). For the conditional formatting rule to apply to new data automatically, you can convert your data range to a fully-functional Excel table. The colors will adjust automatically when the cell values change. In the result, you will have the high values highlighted in red, middle in yellow, and low in green. As you hover the mouse over a particular color scale, Excel will show you the live preview directly in your data set.įor this example, we've chosen Red - Yellow - Green color scale: On the Home tab, in the Styles group, click Conditional Formatting > Color Scales, and then click the color scale you want.To make a heat map in Excel, these are the steps to perform: Excel conditional formatting effectively overcomes both hurdles. And secondly, you'd have to redo color-coding every time the values change. Firstly, it'd take a lot of effort to apply an appropriate color shade according to the value's rank. If you were thinking about coloring each cell depending on its value manually, give up that idea as that would be a needless waste of time. In Excel, a heat map is used to depict individual cells in different color-codes based on their values.įor example, from the heatmap below, you can spot the wettest (highlighted in green) and the driest (highlighted in red) regions and decades at a glance: Risk management heat map - shows different risks and their impacts in a visual and concise way.Geographical heat map - displays some numeric data over a geographic area using different shades.Air temperature heat map - is used to visualize air temperature data in a certain region.They are extensively used by scientists, analysts and marketers for preliminary analysis of data and discovering generic patterns. Typically, warm-to-cool color schemes are employed, so data is represented in the form of hot and cold spots.Ĭompared to standard analytics reports, heatmaps make it a lot easier to visualize and analyze complex data. Create a dynamic heat map without numbersĪ heat map (aka heatmap) is a visual interpretation of numeric data where different values are represented by different colors.Create a dynamic heat map with checkbox.Luckily, there is a quick and simple way to create a heat map in Excel with conditional formatting. As you probably know, Excel has a number of inbuilt features to create graphs. But in some cases, visuals are way easier to comprehend and digest. Microsoft Excel is designed to present data in tables.

This step-by-step guide will walk you through the process of creating a heat map in Excel with practical examples.

Click map 4