How To View Actual Versus Target With A Thermometer Style Chart

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 qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Subscribe

Related Posts

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 Microsoft Excel Tips

Follow Us

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