Conditional Formatting: Build a Heat Map

Andreas Exadaktylos
A free video tutorial from Andreas Exadaktylos
Best Selling Teacher | Msc Computer Scientist
4.5 instructor rating • 7 courses • 20,711 students

Learn more from the full course

Excel Dashboards and Data Analysis Masterclass

Create 4 Eye-Catching Dynamic Microsoft Excel Dashboards from Scratch (Excel Dashboard Templates + Workbooks Included)

09:29:49 of on-demand video • Updated March 2020

  • Create 4 Professional Interactive Excel Dashboards from scratch
  • Create over 10 Amazing Interactive Non-Standard Charts in Microsoft Excel. BONUS: Excel Workbook files + Sample files included
  • Improve general Excel knowledge - Pivot Tables and Pivot Charts, Conditional Formatting, Functions, Formulas and Macros
  • Understand and Identify the Principles of Data Analysis
  • Build Interactive Dashboard Reports with Buttons and Drop Down Menus
  • Learn from an instructor with over 18 years of experience with Microsoft Excel, teaching thousands of students in his own computer learning school
  • Analyze Excel data using Excels based Functions
  • Course updated regularly
  • An instructor ready to answer your questions in less than 24 hours
  • Watch high-quality video lectures with lifetime access
  • Quizzes and exercises
  • Certificate of completion
English Hello Students! In this lecture we are going to learn how to create a Heat Map in Excel. Basically, a heat map is a presentation of data with colors according to the values. We can visualize trends or relationships using color scales. For example I can easily spot which are the months for a company when the sales were low (highlighted in red) as compared with other months. Or I can spot the low and high temperatures by city , by month. Keep in mind that in order to have a nice heat map you have to use intuitive color scales like red to green for earnings or blue to red for temperatures. First we will create a simple heat map using conditional formatting to highlight cells based on the value. This way, in case you change the values in the cells, the color/format of the cell would automatically update the heat map based on the pre-specified rules in conditional formatting In this table you can see earnings of a company by month, from January to December, and from year 2000 to year 2017. The steps are very simple. The first step is to highlight cells from B2 to S13 and from Home menu find conditional formatting options and go to build in color scale. Here I can choose various color combinations that can be used to highlight the data. The most common color scale is the first one where cells with high values are highlighted in green and low in red. Note that as you hover the mouse over these color scales, you can see the live preview in the data set. I will choose the third one that serves my purpose, more green more earnings more red less earnings. Now, if you want to show only the colors and not the values in the cells, a great tip is to use custom formatting rule from Format Cells properties, to the bottom of this category list, select 0, delete it and just type three consecutive semicolons and ok. The numbers are invisible , the conditional formatting is still based on the values but the user doesn’t see them. In that way you just visualize the trends. Another option is to change the color scale by managing the rules from this window. Edit the rule and change the color scale , for example I’ll change the color of the lowest value. Okay, okay again and see the different shades of green. So Heat map is a wonderful tool to tell a story.