1 00:00:01,620 --> 00:00:05,720 In this video we will learn how to draw a heat map. 2 00:00:06,460 --> 00:00:08,730 Heat Map is not a graph. 3 00:00:08,770 --> 00:00:11,030 It is not a chart. 4 00:00:11,380 --> 00:00:19,110 It is just formatting the data table so that you can quickly identify the pattern in that data. 5 00:00:19,120 --> 00:00:19,360 They will 6 00:00:22,540 --> 00:00:29,160 as you can see in this data the same data that we used to create this first chart. 7 00:00:31,310 --> 00:00:35,740 So on the left we have these seven days Monday Tuesday. 8 00:00:35,740 --> 00:00:38,940 So on to the sun and on top. 9 00:00:39,250 --> 00:00:42,870 We have intervals of the words for that particular day. 10 00:00:43,840 --> 00:00:48,410 And this data is giving me the number of customers visiting our website. 11 00:00:48,490 --> 00:00:50,560 In that two hour interval. 12 00:00:50,770 --> 00:01:02,670 So on a Monday between 2 a.m. to 2 a.m. I get 494 customers on my website so each cell is representing 13 00:01:02,940 --> 00:01:06,590 the number of customers visiting on that particular day. 14 00:01:06,780 --> 00:01:09,460 In a two hour interval on our website. 15 00:01:11,220 --> 00:01:18,330 So if I just give you this table without any formatting it will be difficult to find out at which point 16 00:01:18,330 --> 00:01:19,750 of time India will be. 17 00:01:19,830 --> 00:01:25,560 You are getting more number of customers and which part of the week there are fewer customers on our 18 00:01:25,560 --> 00:01:27,360 website. 19 00:01:27,460 --> 00:01:34,590 This is where the heat map will help us heat map will highlight different value ranges with different 20 00:01:34,590 --> 00:01:38,820 colors for example. 21 00:01:39,270 --> 00:01:41,130 You can see a spectrum here. 22 00:01:41,550 --> 00:01:47,520 The lowest values in this table are in green color dark green. 23 00:01:47,550 --> 00:01:53,370 Slowly the darkness is decreasing and you see larger values of colors. 24 00:01:53,490 --> 00:01:57,270 Then it becomes yellow where you have medium values. 25 00:01:57,270 --> 00:02:05,630 Then it becomes slowly to red where you have the highest value to this spectrum is covering largest 26 00:02:05,630 --> 00:02:07,070 to smallest value. 27 00:02:07,070 --> 00:02:14,450 And it is easy to identify that you are getting most number of customers on a Sunday or a Saturday in 28 00:02:14,450 --> 00:02:25,520 the 10 a.m. to 2 p.m. timespan and you are getting minimum customers on a Monday or Tuesday at the. 29 00:02:25,580 --> 00:02:27,910 That is 10 p.m. to 14. 30 00:02:29,790 --> 00:02:32,510 So let us learn how to create this heat map 31 00:02:35,910 --> 00:02:39,310 so here's that data with no formatting. 32 00:02:39,630 --> 00:02:43,800 We will select these cells which contain the numbers. 33 00:02:44,190 --> 00:02:49,480 We will go to home and this conditional formatting option. 34 00:02:50,110 --> 00:02:55,260 Here you can see we have a number of options to draw a heat map. 35 00:02:55,260 --> 00:03:05,170 We will use the color skills option and we can choose different types of color scales you can hold over 36 00:03:05,170 --> 00:03:13,390 these options to select the color scale or the spectrum of colors that you want and select the second 37 00:03:13,390 --> 00:03:14,440 one. 38 00:03:14,440 --> 00:03:21,630 You can also customize these colors by going to the more rules option inside this conditional formatting. 39 00:03:22,090 --> 00:03:32,770 You can click on this to select the starting color and the end singular and give the spectrum that to 40 00:03:32,770 --> 00:03:36,910 know more about what all automatic options we have in excel. 41 00:03:36,910 --> 00:03:42,250 You can add effort to another course the Excel master class scores which is a four hour course and it 42 00:03:42,250 --> 00:03:46,570 will tell you all the formatting techniques that you can use in Microsoft Excel. 43 00:03:49,030 --> 00:03:57,910 Also if you just want to pick colors and not sure this numerical value in individual cells you can remove 44 00:03:57,910 --> 00:04:06,200 these numerical values by selecting this data right click on it go do what matters here. 45 00:04:06,210 --> 00:04:15,030 We will give it custom formatting to this and the formatting is semicolon semicolon semicolon. 46 00:04:15,180 --> 00:04:18,650 This means that whatever number is here it should be hidden. 47 00:04:19,140 --> 00:04:25,380 Click on Okay all those numbers at it and you just have a heat map telling you where the values are 48 00:04:25,380 --> 00:04:34,280 large and where the small as you can see this chart is also giving us all the information that the surface 49 00:04:34,290 --> 00:04:35,970 star gives. 50 00:04:35,970 --> 00:04:42,840 So the heat map is a very good substitute for surface chart whenever you want to display the values 51 00:04:42,840 --> 00:04:44,680 or the pattern in your data table. 52 00:04:44,910 --> 00:04:50,200 Instead of drawing the surface jar it is always preferable to use a heat map.