Conditional Formatting: Build a Heat Map - Excel Dashboard Tutorial
Conditional Formatting: Build a Heat Map is a free tutorial by Andreas Exadaktylos from Excel Dashboard course
Link to this course(Special Discount):
https://www.udemy.com/course/excel-ch...
This is the best Excel Dashboard Course
Course summary:
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.
Conditional Formatting: Build a Heat Map Create 4 Eye-Catching Dynamic Microsoft Excel Dashboards from Scratch (Excel Dashboard Templates + Workbooks Included)