How To View Actual Versus Target With A Thermometer Style Chart

2017-01-08

Get The Completed Workbook

If you want to view your performance versus a target for some metric like sales then a thermometer style chart is a great way to do this.

 

 

In this example we have a table with columns for the sales person, their target sales and the actual sales they had. We will create a 2D bar chart for this data.

  1. Place the Active Cell cursor somewhere in your data.
  2. Go to the Insert tab.
  3. From under the Charts section, press the Bar Chart icon.
  4. Select the 2D Bar Chart from the menu.

 

 

 

Add your performance data to the secondary axis (in this case the sales data).

  1. Right click on one of the sales bar columns in the chart.
  2. Select Format Data Series from the drop down menu.
  3. In the Format Data Series window, select the Secondary Axis radio button.

 

 

Delete the resulting secondary axis in the chart.

  1. Right click on the secondary axis.
  2. Select Delete from the menu.

 

 

Format the Target data series.

  1. Right click on one of the Target bar columns in the chart.
  2. Select Format Data Series from the menu.
  3. Adjust the Gap Width value until the desired thermometer result. I’ve set it to 80%.

 

 

The result is an easy to read performance versus target thermometer style chart.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Related Articles

Comments

1 Comment

  1. John

    Instead of having all the sales reps in one graph, how do you add a drop down box to select individual sales reps?

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest