How-to Make a Non-Chart Excel Dashboard Chart (Heat Index)

So I saw this chart in an Excel forum and a user was asking this:

“chart – not sure about this one

I’ve created a few different types of charts in Excel over the years, but I’m trying to recreate a certain chart (with different values of course). The only thing I can think of is perhaps some type of area chart?? I don’t know. Any suggestions?”

 

Here is the picture that Brian posted.   So take a look and think how you could make this Chart in Excel and also give some thought on how you can use this type of chart in your next executive presentation or company dashboard:

NOAAs National Weather Service Heat Index Chart

This Excel user wants to create a similar chart to this NOAA’s National Weather Service Heat Index.  Now the user thought that it might be advisable to use an Excel Area Chart.  And as I look at it, it would have to be an Excel Stacked Area Chart.  But, Excel area graphs are normally smooth and not pointy.  Also, they typically move from one point to the other and not vertically then to the next point.





What chart type did you pick?

The answer probably doesn’t surprise you based on this blog post Smile but the easiest way to make this Excel dashboard chart is to not create a chart at all.  There are many ways to make dynamic charts in Excel and now I will show you one way to make such.  Here is my chart of the same NOAA’s National Weather Service Heat Index done in Excel:image

Looks just like the sample and it is very dynamic based on the values.  So how did I make this?  Just in regular Excel spreadsheet cells.  It was all done with Conditional Formatting.  Here is how to do it yourself for your own dashboard charts.

 

The Breakdown



1) Create your Data and Borders and Legend

2) Highlight the Chart Range

3) Create Conditional Formatting Rules

4) Rearrange / Manage Conditional Formatting Rules

 





Step-by-Step Tutorial

1) Create your Data and Borders and Legend

First we just need to make our chart look like a chart by:

a) Creating your data values – This is simple enough.  In my chart where ever you see a number, enter that number in a cell in your chart.  See how the formula bar for cell E7 is just a number:image

b) Creating titles and Axis Labels – This is simple in that we are using regular text and also the “Merge & Center” button on the Home Ribbon to make the chart title span across many rows. image



You may find it just a little more complex for the vertical axis label as the text is vertical.  You can do this from the Orientation button on the Home Ribbon.image

c) Creating a legend – imageThis is simply text next to a cell with a fill color equal to the color you want for each region of the chart.image

d) Removing the Gridlines from the spreadsheet – You can do this by unchecking Gridlines from the View Ribbon.  This gives you a clean background.image

Your non-chart Heat Index chart should now look like this:image

 





2) Highlight the Chart Range

So now you just need to create the chart colors.  To do this you need to highlight the range of D6:S18image

That was pretty easy Smile but the next step is the trick to make the chart area look like a chart.

 

3) Create Conditional Formatting Rules



Conditional charts and tables are great ways to highlight data in any spreadsheet range.  These types of charts are made with the use of Conditional Formatting on a range of spreadsheet cells.  They are very powerful, but can make some people crazy because of some quirks in Excel.  I HIGHLY recommend that you check out this post if you are new to Excel Conditional Formatting:

The Tricks to Writing a Conditional Formatting Rule Formula

Now that we have the range we want to color highlighted, we need to create the following conditional formats.  All but the last one of these Conditional Formatting Rules are created by the Greater Than… choice from the Highlight Cell Rules.image

a) >79 with a custom format of yellow:SNAGHTML759437bSNAGHTML758b669

Your chart should now look like this:image

b) >90 with a custom format of light orange:





SNAGHTML75eb10cSNAGHTML75d0c74SNAGHTML75cd83b

Your chart should now look like this:image

c) >100 with a custom format of dark orange:SNAGHTML76264d1SNAGHTML7667832SNAGHTML765cfb3

Your Excel chart should now look like this:image

d) >124 with a custom format of red:



SNAGHTML76cfb65SNAGHTML7695354

Your chart should now look like this:image

So this matches pretty close, but we need to fill in the white area that is to the right of the red area.  To do this, we need to create a special formula for when a cell is blank.  So keep the same highlight but instead of Greater Than, we need to choose new rule from the conditional formatting button:image

Then choose Use a formula to determine which cells to format from the Select a Rule Type:SNAGHTML772e382

and then in the Edit the Rule Description section, you need to put the following formula in the “Format Values where this formula is true:” cell:   =isblank(d6)SNAGHTML775ae9d





Now that you have set the rule, you need to click on the “Format…” button and choose a red color from the Fill tab:SNAGHTML776abb0SNAGHTML777433d

Your chart should now look like this:image

Now if your chart doesn’t look like this then you need to check the following as it may be your issue.  Some times excel doesn’t set the reference right for the conditional formatting.  At times and I am not sure why, but it will change your reference to some far off cell like I34344.  You need to edit this cell reference in the last formula you created.  Do this by going to the Manage Rules choice in the Conditional Formatting button:imagethen change the “Show formatting rules for:” to “This Worksheet”image

Your will now see a list like this and you need to choose the last rule you created (at the top) and then click on the Edit Rule button:SNAGHTML77e412b

If you see a reference that is not D6 in the Format values where this formula is true”, then you need to change the reference back to D6:SNAGHTML77f4060



When you change this reference, then your chart should be all set.

 

4) Rearrange / Manage Conditional Formatting Rules

Now if your chart looks nothing like what I showed you above, and if it looks something like this then you didn’t follow my order and you need to rearrange your formatting rules.  image

You can do this by clicking on the Conditional Formatting button on the Home Ribbon and then choose Manage Rules:image





Then choose “This Worksheet” from the Show formatting rules for pick list: image

Now from the Rule (applied in order shown) select a rule and then move it so that the red is on top down to the yellow in color order.  If you are doing this for another dashboard project, and you chose greater than as your rule type, then you need to make sure that your top value has the largest number to check against.  If it is less than then your top values should be the smallest number.  For this example, you chart conditional rules should look like this:SNAGHTML78b0a34

If they do, then press on the Apply button to set the new rules into effect.  Your chart should now look like this:image

 

Video Tutorial

Here is a video demonstration of me creating this dashboard chart using conditional formatting:

 

Please make sure you sign up for my blog so that you get the latest posting delivered to your inbox.  Also, if you liked the video demonstration, I always appreciate the comments and the likes Smile.  Finally, let me know in the comments below if you can see how you would use these types of Conditional Formatting charts in your next Excel Dashboard.  Perhaps some sort of heat map for your sales tables or productivity KPI or metric?

Steve=True









LEAVE A REPLY

Please enter your comment!
Please enter your name here