{"id":51490,"date":"2020-04-20T14:40:07","date_gmt":"2020-04-20T09:10:07","guid":{"rendered":"https:\/\/vwo.com\/blog\/?p=51490"},"modified":"2026-01-14T18:52:16","modified_gmt":"2026-01-14T13:22:16","slug":"create-heatmap-excel","status":"publish","type":"post","link":"https:\/\/vwo.com\/blog\/create-heatmap-excel\/","title":{"rendered":"How to Create A Heatmap in Excel"},"content":{"rendered":"\n

Analysts use heatmaps to analyze the magnitude of an event using visual cues. A data visualization technique, heatmaps are utilized to derive quick interpretations of the intensity of an event and do course corrections accordingly.<\/p>\n\n\n\n

One of the examples of heatmaps can be the visual representation of COVID-19 cases being registered globally. The following map shows the geographic distribution of the cumulative number of COVID-19 cases reported per 100,000 people worldwide. Darker shades of orange denote the most affected countries, and the light yellow hues indicate the opposite.<\/p>\n\n\n

\n
\"How
Image source: European Centre for Disease Prevention and Control<\/a><\/figcaption><\/figure>\n<\/div>\n\n\n

There are quite a few efficient ways to generate a heatmap, such as using readily available free heatmap generators<\/a> like VWO\u2019s AI-powered heatmap generator<\/a> or integrated analytical tools<\/a>. Microsoft Excel or Google Sheets is another great option to explore. Let\u2019s dive right in!<\/p>\n\n\n

What is a heatmap in Excel?<\/strong><\/h2>\n\n\n

A heatmap is an effective way of presenting data in applications like Excel, where data values are represented by colors, simplifying the entire data analysis process. You can notice patterns and trends at a glance, which helps drive insights and faster decisions.<\/p>\n\n\n\n

The best thing about Excel is that its built-in features make heatmap creation easy. You can apply conditional formatting to data in just a few clicks, and it automatically assigns colors to the values in the cells. The user-friendly interface, preset color scales, and numerous customization options make it possible for even less technical users to create a heatmap chart in Excel.<\/p>\n\n\n

Learn how to create a heatmap in Excel<\/strong><\/h2>\n\n\n

When using Excel or Google Sheets, you can either create a heatmap by manually coloring each cell depending on its value or act smartly and enter a formula\/function to do all the taxing work for you. We\u2019d suggest you use the latter method to create a heatmap.<\/p>\n\n\n\n

Let\u2019s consider the dataset extracted from the above represented COVID-19 globally registered cases as an example to learn how to create a heat map<\/strong> using the function\u2014apply \u201cConditional Formatting.\u201d<\/p>\n\n\n\n

Country<\/strong><\/td>Cases<\/strong><\/td>Deaths<\/strong><\/td>Confirmed Cases<\/strong><\/td><\/tr>
US<\/strong><\/td>639664<\/td>30985<\/td>422943<\/td><\/tr>
India<\/strong><\/td>12380<\/td>414<\/td>10415<\/td><\/tr>
UK<\/strong><\/td>98476<\/td>12868<\/td>69002<\/td><\/tr>
Brazil<\/strong><\/td>28320<\/td>1736<\/td>21484<\/td><\/tr>
Philippines<\/strong><\/td>5453<\/td>349<\/td>3142<\/td><\/tr>
Canada<\/strong><\/td>28364<\/td>1010<\/td>18769<\/td><\/tr>
Netherlands<\/strong><\/td>28153<\/td>3134<\/td>14539<\/td><\/tr>
Germany<\/strong><\/td>130450<\/td>3569<\/td>56928<\/td><\/tr>
Australia<\/strong><\/td>6458<\/td>63<\/td>1482<\/td><\/tr>
France<\/strong><\/td>106206<\/td>17167<\/td>49217<\/td><\/tr>
Italy<\/strong><\/td>165155<\/td>21647<\/td>54581<\/td><\/tr>
Pakistan<\/strong><\/td>6505<\/td>124<\/td>4214<\/td><\/tr>
Spain<\/strong><\/td>177633<\/td>18579<\/td>75497<\/td><\/tr>
Russia<\/strong><\/td>24490<\/td>198<\/td>21713<\/td><\/tr>
Singapore<\/strong><\/td>3699<\/td>10<\/td>2699<\/td><\/tr>
Indonesia<\/strong><\/td>5136<\/td>469<\/td>3459<\/td><\/tr>
Israel<\/strong><\/td>12501<\/td>130<\/td>6910<\/td><\/tr>
Malaysia<\/strong><\/td>5072<\/td>83<\/td>2164<\/td><\/tr><\/tbody><\/table>

Image source: European Centre for Disease Prevention and Control<\/a><\/figcaption><\/figure>\n\n\n

Step 1: <\/strong>Enter the data<\/h3>\n\n\n

Enter the necessary data in a new sheet. We entered the data above.<\/p>\n\n\n

\n
\"How<\/figure>\n<\/div>\n\n

Step 2:<\/strong> Select the data<\/h3>\n\n\n

Select the dataset for which you want to generate a heatmap. In this case, it would be B2 through D19.<\/p>\n\n\n

\n
\"How<\/figure>\n<\/div>\n\n\n

Download Free: Website Heatmap Guide<\/strong><\/span><\/a><\/em><\/h3>\n\n\n

Step 3:<\/strong> Use conditional formatting<\/h3>\n\n\n

If you are using Excel, go to \u201cHome,\u201d click on \u201cConditional Formatting,\u201d and select \u201cColor Scales.\u201d The color scale offers quite a few options for you to choose from to highlight the data.    <\/p>\n\n\n

\n
\"use<\/figure>\n<\/div>\n\n\n

In our case, we\u2019ve used the first option where cells with high values are colored in green and ones with low values in red.<\/p>\n\n\n\n

If you are using Google Sheets, you will find \u201cConditional Formatting\u201d under the \u201cFormat\u201d option in the menu bar.<\/p>\n\n\n

\n
\"Create<\/figure>\n<\/div>\n\n\n

Then select \u201cColor Scale\u201d and choose the relevant colors for the midpoint, minpoint, and maxpoint.<\/p>\n\n\n

\n
\"Create<\/figure>\n<\/div>\n\n\n

Note: Use a color scheme that best matches your data interpretation needs. <\/strong><\/p>\n\n\n

Step 4: <\/strong>Select the color scale<\/h3>\n\n\n

Once you select a color scale, you\u2019ll see a heatmap as shown below:<\/p>\n\n\n

\n
\"select<\/figure>\n<\/div>\n\n\n

In this color scale, Google Sheets assign a green color to the cells with the highest values and red to the ones with the lowest values. Meanwhile, the remaining values are assigned colors based on the descending value order showing a gradient of shades falling between green and red. And there you have it: your beautiful heatmap to analyze visitor experience<\/a> and get more conversions.<\/p>\n\n\n

How to make a heatmap in Excel without numbers<\/strong><\/h2>\n\n\n

Ideally, you should not remove the numbers, as the heatmap in Excel is based on cell values. Removing them defeats the purpose. However, you can hide the values using custom number formatting.<\/p>\n\n\n\n

    \n
  1. Select your heat map.<\/li>\n\n\n\n
  2. Hit Ctrl + 1 to display the Format Cells dialog box.<\/li>\n\n\n\n
  3. Click Custom under Category on the Number tab.<\/li>\n\n\n\n
  4. Type 3 semicolons in the Type box.<\/li>\n\n\n\n
  5. Click Ok to apply the custom number format.<\/li>\n<\/ol>\n\n\n

    How to create dynamic heatmaps in Excel<\/strong><\/h2>\n\n\n

    When you change a cell’s value, conditional formatting immediately recalculates and adjusts accordingly. This feature enables the creation of dynamic heatmaps in Excel. Let’s explore methods for creating these dynamic visualizations. <\/p>\n\n\n

    Making a dynamic pivot table heatmap<\/strong><\/h3>\n\n\n

    This is how it works: <\/p>\n\n\n\n

      \n
    1. Select any cell in your heatmap.<\/li>\n\n\n\n
    2. On the Home tab, go to the Styles group.<\/li>\n\n\n\n
    3. Click Conditional Formatting > Manage Rules.<\/li>\n\n\n\n
    4. In the Conditional Formatting Rules Manager, select the rule and click Edit Rule.<\/li>\n\n\n\n
    5. In the Edit Formatting Rule dialog box, under Apply Rule To, choose the third option: ‘All cells showing “Sum of Sales” values for “X” and “Y” parameters as shown in the boxes.<\/li>\n\n\n\n
    6. Your heat map is now dynamic, and when you add new data, it will update automatically.<\/li>\n\n\n\n
    7. Remember to refresh your PivotTable to see the updates.<\/li>\n<\/ol>\n\n\n\n

      Conditional formatting will be removed if you alter the row or column fields. For instance, if you remove the Date field and then reapply it, the conditional formatting will be lost.<\/p>\n\n\n

      \n
      \"Pivot
      Image source: Spreadsheeto.com<\/a><\/figcaption><\/figure>\n<\/div>\n\n
      \n
      \"19
      Image source: Spreadsheeto.com<\/a><\/figcaption><\/figure>\n<\/div>\n\n

      Creating a dynamic heat map in Excel with scroll bar<\/strong><\/h3>\n\n\n

      1. Insert a scroll bar<\/strong><\/h4>\n\n\n\n

      Control your dynamic heatmap with a scroll bar using these steps:<\/p>\n\n\n\n