Show Temperature in Excel Color Scale

How hot was it in your neighbourhood last week? It was so hot, here in Mississauga, that I was afraid to stay outside for more than a minute, in case I melted, or burned to a crisp! On the bright side, I got lots of work done, but I’d rather have cooler temperatures, than hot, muggy days.
With Excel, I can decide when it’s better to stay inside. When I think of hot temperatures, red comes to mind, and blue is what I think of for cold days. So, to show the daily temperature by colour, I created a worksheet with a conditional formatting color scale.
This feature is available in Excel 2007 and later versions, and I’ve used Excel 2013 in this example. You can see the steps in the video at the end of this post, and written instructions are below.

Show the Temperature by Colour

Here is how I set up a worksheet where I enter the current temperature, with a colour code to highlight hot and cold days.

  1. Enter the temperature in cell B3
  2. In cells F6:F25, enter the numbers 140 to -50, as shown below
  3. In cell G6, enter a formula that will show an empty string if the current temperature is equal to the temperature in that row, or between that temperature, and the one above:=IF($B$3=F6,””,IF(AND($B$3>F6,$B$3<F5),””,F6))
  4. Copy the formula down to row 25
  5. Select cell B3, then press the Ctrl key and select cells G6:G25 (the cells with the formulas)
  6. On the Ribbon’s Home tab, click Conditional Formatting
  7. Click Color Scales, then click on the Red – White – Blue scale
  8. Change the temperature in cell B3, and the cell color will change, based on the color scale.
  9. (optional) Hide columns F:G, so the color scale is not visible.


Remember the Excel Tips Book Giveaway

The Excel Tips book giveaway is open until Wednesday, July 24th, at 12 noon Eastern time, so remember to enter, if you haven’t already. Just go to the blog post and add your tip in the comments. Come back on Thursday, July 25th, to see if you won. Good luck!

Download the Sample File

For more information on conditional formatting, and to download the sample file, please visit my Contextures website: Conditional Formatting Examples Page.

Video: Show Temperature in a Color Scale

To see the steps for showing the temperature with a color scale, please watch this short video.