Hi, this is Wayne again with a topic “Create a Heat Map in Excel”.
In this article, i will show you how to easily and quickly create a beautiful heat map out of your data in excel. So here we have a spreadsheet with a list of different foods and some detailed information about those foods, and i collected this information by using the data tab and the data types group. If you want to know how to do that, watch my video on the topic, but with all of this data in the spreadsheet, it’s hard to really understand and digest what the data mean. So, to help with that, i’d like to use a heat map here are a few examples of other heat maps. Heat maps are often used as a way to show the temperature in a geographic location.
Let’S take a look at this example, so this shows temperatures across the united states, but heat maps are also used for other things, maybe to show where population centers are or where there’s higher crime or lower crime in a place, and they can also be used completely Separate from geography to indicate, in this case, what’s critical, what’s minor, etc, but one of the best things about a heat map is that it can give the viewer an immediate sense of what the data means and what stands out in that data. So, let’s look at how easy it is to set up a heat map kind of effect in my spreadsheet. So the first thing we need to do is select the data that we’d like to turn into the heat map and unfortunately, you can’t just click and drag and get all of it at once. Let me show you why, if i get all of that data that i’d like to turn into a heat map, and then i go to the home tab in the styles group, i can go to conditional formatting and go to color scales and you can see, as I put my mouse over the different options. I get basically a heat map effect.
If i choose this option here, the lowest numbers will be in red and the highest numbers will be in green. If i switch to this option, it will reverse that the highest number will be in red, the lowest number in green and there’s all of these different options, and you can look through those and choose the one that best fits the heat map that you want to Create i’m going to stick with this, at least for now, but, like i said a few seconds ago, this is not the right way to do it for such complex data as what i have in this spreadsheet. Let’S look at why, when i selected all of my data and then applied the conditional formatting, the lowest numbers, no matter where they are in my spreadsheet, were colored as red and the highest numbers were colored green.
Well, that’s not exactly what i want. I don’t care how this number relates to, let’s say calories or fat or protein. I want to know how the amount of vitamin a in carrots relates to the vitamin a in asparagus, let’s say or in ham, so i’m going to undo by holding ctrl and tapping z, i’ll undo the heat map or the color scale that i applied to this Data – i just wanted you to see why it’s important, at least in this situation, to select only the data that you want to be compared, so i want to compare the calories of each of these foods to each other.
With that selected, i go back to the home ribbon the styles group conditional formatting color scales and that same green to yellow to red color scale. Now it works beautifully. I can just glance at this and i can see right away that the chicken breast has the most calories out of this list of foods. Now, as you can see, some of the serving sizes are a little off, but i can see also right away that asparagus is very low in calories. Now i’m going to go ahead and do this for each of these columns of data i’ll just click and drag to select everything that i want to be compared with each other go to conditional, formatting, color scales and i’ll.
Select that same option. Give me a minute to do so and then i’ll resume the video okay, great i’ve finished, applying the color scale to each of the columns. Now that this is done, i might want to actually think it through a little bit better. Maybe i’m trying to highlight what is particularly good or healthy about each of these kinds of foods.
So, for example, in the case of calories, it might actually be better to have high calories show up as read showing that maybe that’s something that you need to be careful of. So i would just highlight the data again conditional, formatting color scales and i can switch it to the opposite option. So now some of the perhaps healthier options show up in dark, green and some of the higher calorie items show up in dark red. I could do the same thing with the fat column, maybe someone’s trying to reduce the amount of fat that they eat or carbohydrates or whatever it might be i’ll, just select fat and i’m holding the ctrl key on the keyboard and i’m also selecting cholesterol. Let’S say we want to limit the amount of cholesterol and then vitamin a vitamin c calcium we’d like to keep those high, so the higher numbers could be better and so they’re in green. So i’m going to leave those as is, but i would just like to change fat and cholesterol to match calories so that the higher numbers will be marked with red and the lower numbers with green.
So, with both of those columns selected, i’m going to go to conditional formatting, color scales and we’ll swap the option again so that in these cases the lower numbers show up as green. So there isn’t really one correct way to do this. It just depends on the data and the information that you’re trying to share the way i have this set up.
Now it’s meant to show what foods are perhaps healthier with maybe fewer calories, a little bit less fat and cholesterol and maybe higher amounts of vitamin a vitamin c and calcium. So i hope you can see some of the power of creating heat maps in excel, using conditional, formatting and specifically the color scales, if you’d like to learn how i pulled in this data about the different foods. Please watch my tutorial on that topic.
I hope you enjoyed the video if you did please like follow and subscribe, and when you do click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, consider clicking the thanks button or supporting me through my patreon account, or by buying channel merch and you’ll, find information about those options in the description below the video .