Show Temperature in Excel Color Scale

Show Temperature in Excel Color Scale

How hot was it in your neighbourhood last week? It was so hot, here in Mississauga, that I stayed inside, most of the week. 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.

colorscaletemperature02

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.

colorscaletemperature01

Download the Sample File

For more information on conditional formatting, and to download the sample file, 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, watch this short video.

______________

3 thoughts on “Show Temperature in Excel Color Scale”

  1. I like this site. Every day I work on this site. I learn one function per day. It is good.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.