How to Create a Heat Map with Excel

hello in this video I will show you how to create your own heat map with Excel a heat map is a data visualization technique that shows the magnitude of a phenomenon as color in two dimensions the variation of the color and the intensity provides visual representation of how the data varies in this first step we have historical data of Toronto average monthly temperatures from even 1942 year 2012 and we'll use this matrix to create heat map we are going to be using conditional formatting conditional formatting lives on the Home tab in Styles group and when you click the drop down you have several options and we're going to be using color scales so let's first select our temperatures we will leave the headers and the first column and selected and use only the temperatures to represent in the heat map going to conditional formatting selecting our color scales we have some preset color scales from Excel you can use any of them if you want but I would prefer to customize and use my own colors so I'll go to more rules and in this case we'll have to select what it will be to color scale or three color scale let's go with three color scale and for our lowest value or the minuses I'll go with blue so anything that is winter will be represented in blue the mid point will be our spring and autumn values so I'll leave them to yellow it will be okay and the highest values will be the positive up to 20 something summer values so I'll go with something like orange Shh let's use this so these are the colors we're going to use and once we selected the colors we click OK and it will be implemented on our table looking good now we can see that in year 1945 jr. was one of the coldest when we go closer to the year 2000 you can see in 1994 was even colder the average value of - twelve point four degrees is very cold December you can see is not so cold as the first two months of the year but you can see this December in 1989 was pretty cold right it's the same for the summer months and you can see the warmer temperature are clearly defined with darker red for example July in 87 89 was warmer than the next two years and really warm in 1999 pretty much the same temperature was in 2012 we can see that this summer was warmer because even June was higher average temperature and the last three years from 10 to 12 July was very hot what we can do more with this data we can represent it without looking at the numbers just show the colors so I'll copy and paste same table on this side just to be able to compare the two results and will select the whole temperature range on the second one with conditional formatting we are not able to remove the numbers but if we go to our number format we're able to select custom pen type some special formatting and see how we can remove our values so we'll be using semi columns we have to type three semi columns and this will help our data labels to be removed this still behind you can see them in the formula bar but the hidden for the viewers and in some cases this might be a better representation if you have a dashboard you just want to see like some trends and patterns you don't need to see the exact data moving on to the next up we have average monthly maximum temperature again to the vision of Toronto and the period is 1940 until 2012 this time we'll create the heat map differently we using two different conditional formatting less-than and greater-than and we have some ranges to both 0 0 to 10 10 to 20 20 to 25 degrees and about 25 degrees let's start creating the rules so we'll start with less than 0 and we have to find our custom format color and this is the closest one okay another less than this time we're going to choose 10 degrees and now we have to find the lighter blue it's not exactly the same but similar what happens here is the second rule overrides the first rule so at the end when we add all the new rules we'll go to manage rules window and try to rearrange the rules in is in such way so it will perform each of them in a special order so the result is as expected as we we're hoping the third rule will be again less than this time we'll select 20 degrees and find our green color this is more likely okay the last of the less-than rules will be 25 degrees and we need to use the yellow okay and the very last rule that we're going to use will be greater than because we have over 25 so let's say instead of 25 and over we'll will select 24.9 because we want to include 25 in the rule as well otherwise 25 won't be included in in the previous rule as well so let's go to custom and find our color it's something between already generates okay okay so once we've created our five rules we'll go to conditional formatting and it's rule options and we will rearrange the pros the weight it should be going in order so which one we need move to the top it will be below zero so with these arrows here we are going to move below zero to the top then we'll move the next one will be below ten the third one 20 no 25 and the last will be the greater than 24.9 and this is our final results I can copy the previous table from here so we can compare them easily I'll leave it to you to choose which one to use once we have a particular range it's easier for us to say oh these are the months where the maximum was all almost always below zero and you can see the same bar has been a lot warmer than January in February November never had below zero and even had above 10 degrees in some cases I'll leave it to you to see which heat map works for you moles but I wanted to show you both ways that we can do that thanks for watching hope you liked it hope to see you again don't forget to Like subscribe and share

Channel: EZ Excel

Views: 39,683

Keywords: Excel, MS Excel, Excel 2016, tabs, add commands, beginner, ExcelIsFun, Technology for Teachers and Students, students, teachExc, Leila Gharani, spreadsheets, covid-19, corona virus, how to excel, conditional formatting, data bars, colour scales, color scales, icon sets, heatmap, heat map, greater than, visual representation

Id: Q_kQYvV0tTE

Length: 10min 3sec (603 seconds)

Published: Thu Jun 25 2020