Custom Charts in Excel :: Comparing Series Against Average or Benchmark

Posted: April 18, 2014 by MaxFraudy in Excel
Tags: , , , ,

This is a common requirement while creating dashboards. One selects an option and the charts update to show data for that particular selection. It would be very useful if the same chart could show you how that particular selection fares against average or a set benchmark.

Comparison Chart Against AverageLet’s take an example. I have data for a class of students with their subject marks. I make a selection of the student name, his marks for each subject are plotted on a chart. I wish to see how this particular student has performed in each subject as against the class average. This custom chart does just the same, and it can be very very easily created in Excel as below:

 

  1. Create a Clustered Column chart with both the series.

    Comparison Chart - Step 1

    Comparison Chart – Step 1

  2. Right-click on the series that you want to compare and plot it on the secondary axis.

    Comparison Chart - Step 2

    Comparison Chart – Step 2

  3. Format the ‘average’ data series, decrease gap width.

    Comparison Chart - Step 3

    Comparison Chart – Step 3

  4. Ensure that the primary and secondary axis have the same units and same minimums and maximums (as desired)
  5. Format the chart until you are happy with how it looks.Comparison Chart Against Average
  6. Your chart is ready ūüôā

 

If you’re feeling lazy, simply download the¬†Custom Chart – Comparison that I have created. Data for this chart is hidden in columns (A:C)

 

You may also like:

Advertisements
Comments
  1. […] Max Fraudy‚Äôs sample file, to see how compare a chart series against an average or benchmark. This simple technique uses formatting, not […]

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s