Creating heat maps using conditional formatting

- [Instructor] You may have heard the term heat map. It's described on this worksheet as a graphical representation of data where the individual values contained in the matrix are represented as colors. We've got two different worksheets here. The first one has to do with monthly payments and these amounts could be easily readjusted to reflect higher numbers or possibly used be for examples like buying a car or boat or something like that and similarly, we've got interest rates across the top here. What we'd would like to do is have a whole series of answers here showing us the monthly payment for each of these loans based on the amount and the percent here and let's say for the moment, all of these are 360 months.

We're going to be doing this by way of what's called a data table. If you haven't seen that feature, you'll see it on the data tab in the ribbon and the forecast group under what-if analysis. Data table, see the results of multiple inputs at the same time. We begin this process by putting in a master formula here and I'll be using the data from these cells right here for the master formula, so let me zoom in on this by holding down control using the mouse wheel. We'll be using the function called PMT.

It has three arguments to it. First is the right. We're referring to cell B1. Since we're talking about monthly payments, we'll need to divide this by 12 comma then NPER, the number of periods of the loan. In other words, how many payments? 360, 360 months, 30 years, comma and PV meaning present value or what are we borrowing? This amount right here. As I press enter, we do get the correct answer.

However, it's negative and that is accurate actually, but it's going to be easier to work with the numbers if it's a positive entry, so let's make a simple change here by double clicking and I need to put a minus in from of the PMT or in front of the B3. Then the answer will be expressed as a positive number. It's going to be easier to work with. So that's going to be our master formula. Remember, in the master formula, the right is referred to based on what's in cell B1 and the borrowing amount is referred to by cell B3 and as we work with data table.

First I'll zoom back, holding control, using the mouse wheel. There we go. The data table will consist of all of these cells here so we highlight all of these. The master formula is in the upper left corner. So under what-if analysis... This is in the forecast group on the data tab, choose data table. It's asking us for a row input cell. Which cell on the formula relates to the numbers that we see in row five? That's going to be cell B1. Just click on it.

And which number in our formula relates to the column entries that we see over in column A? So the column input cell here is cell B3. Click OK and we've got a bunch of answers. Now, these show dollars and cents, so it's a bit busy looking and before I move on, do notice something here too. This cell right here, cell C10, 1193.66 is very similar to the entry that we see over here in cell H6. For the moment, I'll highlight both of them in yellow.

And it does point out the idea, if the interest rate goes up a quarter of a percent or it goes down by a quarter of a percent, we're talking about a $20,000 difference in the amount of borrowing here, so what if it's somehow dropped and you had been looking at this number on a $250,000 loan. Then over here, you see almost the same amount. It's only a 12 cent difference per month, but here, you can borrow $270,000 if it were that interest rate. So, we'll be able to see this more clearly if first of all, we change our display here to not include the pennies.

Let me undo the last action with control + Z to get rid of the color and select all of these cells here and on the home tab in the ribbon, in the number group, let's show by decreasing the decimals. Now, it's important to note here, we are not changing the value. We are simply displaying rounded versions of these numbers. Do this twice, easier to read now. We will be able to make these columns a bit narrower too so we can see them a little bit more clearly. There we go. Now, let's apply the heat map, not by way of a feature called heat map because you won't see that.

But under conditional formatting, we use color scales and as soon as we slide over the first entry, I think you get a clear idea of what's going on here. Now, we could use this combination or any of the other 11. I think the second one might be a little bit better. There's probably a bias using either or these two. These are both referred to at different times as the traffic light pattern, but red often means something less desirable. In this case, we're paying more, but if we choose this option, it's pretty clear that we pay more wherever the numbers are redder and less when they're greener over here, but we do see that diagonal representation like we saw earlier on these two numbers here, which for the moment look identical, although they're very close.

We saw these earlier, though, but anyway, that's a good visual depiction of the different amounts that you'll be paying. Now, just because we use the word heat in the term heat map doesn't mean that we couldn't be using this with temperatures, so on the next worksheet over, this is a list for every state, the average temperature. By the way, I'm really skeptical of that number because I think a lot of you know that in some states, there are deserts and mountains and quite a range of temperatures within a state, but these are the averages for each state by month, so let's select this data here.

Every state, the average temperature per month, let's go to conditional formatting, color scales and I think here, we'd agree, red should be the hot entries and there they are. We'll use this option right here and state by state, we can see what's happening. Probably no major surprises as you look at this. The hottest temperatures are generally in July. The coldest ones are generally in January. We see that clearly here, but before making a copy of this, something else we might consider. It's not uncommon to see hot represented by red and cold represented by blue, so selecting these cells here, we could go back to conditional formatting and change the rule by way of an option called manage the rules and here, we'll edit the rule so instead of the lowest numbers being green, let's make them be blue.

You don't want to choose a really dark blue, but certainly a medium to dark blue so you can see the numbers through the color and now, we'll go from blue to red as we click okay a couple of times, so there's that list. Now, although not truly necessary, we could take this list here. All I'm going to do is drag it with the right mouse button over here and as I let go of the red mouse button, copy here and I'd like to accentuate those states that are coldest in the winter, so I'll click on one of the January entries here and then on the data tab, choose AZ.

In other words, the lower temperatures are going to be on top. So North Dakota, Minnesota up there, as you would expect, so that's another way to display the data. Again, using the heat map idea and then the reds pop up again in July as you would expect, so let's copy this one more time. Again, we just take this data here if we wish and I'll drag it with the left mouse button this time and the control key over to here and go to the mouse first. Let's click on one of the July entries now and do a descending sort. That's on the data tab, ZA, so now the hotter states are on top based on the July temperatures.

So just different thoughts on how you might use this, so heat map doesn't of course necessarily have to relate to temperatures, but here, that's a good combination of the idea of a heat map and the actual numbers that we're seeing here, so we've got three different lists here. So this idea called heat map doesn't come to us directly. But we do get to it by way of conditional formatting and applying color scales. You'll see lots of variations on this worksheet here and the example earlier with the actual monthly payment list that we saw in this worksheet.