In excel how do I show a histogram with a vertical marker for the mean and median values

Posted on 2014-09-15
Last Modified: 2014-09-18
Dear Experts,
Let's say I have a column of 800 values and I want to display them in the form of a histogram.

That seems fairly simple.  I can just select the cells and then click on "Insert --> Charts -->Column Chart"

However, I want to display vertical lines on the histogram to indicate the mean and median values.

How can I achieve that?
Best Wishes,
Question by:Philip-David Truscott
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 50
ID: 40324260

I take it that with "histogram" you are not plotting all 800 data points, but an aggregation of the data.

You can use a combination chart with an XY chart plotted on the secondary axis to create lines.  The exact steps would be easier to describe if you could post a sample file and also let us know your Excel version, since there are some differences. Also, it would help if you already have formulas for the mean and median calculation in place.

cheers, teylyn
LVL 27

Expert Comment

by:Glenn Ray
ID: 40324314
Well, a histogram would likely be a derivative of the raw data from the 800 cells - say a distribution set of values in a set of bins.  Displaying the mean and median of the original values wouldn't be really practical...where would you plot them?
histogram plot
As shown here, it really isn't even possible to plot such lines...assuming that's what you're asking.

However, if you wanted to plot the mean and median values as horizontal lines across ALL the raw data (assuming you create a chart as you described in the original question) then this isn't hard; you just set up a two additional columns of data that return the AVERAGE (mean) and MEDIAN of the original data and them plot them using a different chart type.
mean median - line plot
If you'd like more details, please specify which type of plot you're looking for and I'll be glad to elaborate.


Author Comment

by:Philip-David Truscott
ID: 40329072
Dear Teylyn,
Here is an example file.
Best Wishes,
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.


Author Comment

by:Philip-David Truscott
ID: 40329088
Dear Glen Ray,
The sort of display I am after is more like your TOP image, the one with the vertical blue lines.  

Could I do something like this:

a) Have  a column with all my raw data (say in column a). Let's say the values
b) Have a column just with the mean and median (say in column b)

Is there some way to label the data in column b so that it appears different?  For example a vertical blue line as in your example?
Best Wishes,
LVL 27

Expert Comment

by:Glenn Ray
ID: 40329110

So, reiterating Teylyn's and my question, are you wanting to plot a histogram of this data, and, if so, what are your bin values?  There are none in your example data.

Although it's possible to combine line and bar charts, I'm not sure how an X-Y chart can be overlaid on a bar chart - which is what's needed to generate the vertical lines.

Also, I'm not sure how a hybrid bar chart could be created to show two thin, vertical bars representing the mean and median.  But I'll give it a go.

LVL 27

Expert Comment

by:Glenn Ray
ID: 40329162
This is the closest I've been able to produce so far:
 hours of work - histogram and mean-median lines
Even though I was able to combine a bar chart with an X-Y chart, the issue becomes obvious when noting the position of the bars on the x-axis; they do not line up ON the bin values, but rather in between.  That may not be a big deal; in some sense, this is correct as the bars represent the number of values "in between" each x-axis point.

It looks like you're using Excel 2003 or earlier, so the histogram functions are set up for that.


Author Comment

by:Philip-David Truscott
ID: 40329199
Dear Glenn,
I am not sure I know how to go about combining a bar chart with an X-Y Chart.  Could you give more detail?

The effect you have achieved looks absolutely fine.
Best Wishes,

Author Comment

by:Philip-David Truscott
ID: 40329252
In answer to the question about bin sizes I have shown bin sizes in this new version of the spreadsheet.

I have also made a column chart with the resulting frequency table.
Best Wishes,
Phil HoursOfWorkWithBins.xls
LVL 27

Accepted Solution

Glenn Ray earned 500 total points
ID: 40329316
Generally-speaking, you'll first create an X-Y chart using the histogram bin and frequency values for your x and y components.  This will generate a chart like this (choose the example x-y plot with lines only  and no points)
Step 1 - set up XY chart on histogram data
Then you'll create a set of cells that calculate the mean (average) and median of the values in the "hourly" sheet:
Step 2 - Create Mean and Median valuesThe formulas are:
=AVERAGE(hourly!A:A) (for mean)
=MEDIAN(hourly!A:A)  (for median)

To get these to plot as two vertical lines you'll need y-values of zero and a high number, higher than the largest histogram frequency returned.  I chose 300 in this example.

Next, you add new series to the chart.  Here's the set up for adding the Mean; you'd do a repeat of these steps to add the Median:
Step 3 - Add Mean and Median series
Here's the key step:  Right-click on the original histogram series and select "Format Data Series" from the shortcut menu.  Change the "Plot Series On" setting to "Secondary Axis".  An identical axis will appear on the right.  Close this window.
Step 4 - Secondary Axis for histogram data
While the series is still selected, right-click on any point again, and select "Change Series Chart Type".  Find the column type on top of the list and select it.  You'll see something like this:
Step 5 - change series chart type
Now, change the x-axis scale by double-clicking it and setting the minimum to 0, the maximum to 100, the major unit to 5 (same as bin increment):
Step 6 - Set x-axis
Lastly, change the column width of your bars (like 50-100%) and add a legend to note the lines:
Finish - formatting and labeling
I did all this in Excel 2010, so I might be missing some parts of the chart interface, but it should be pretty similar.

LVL 27

Expert Comment

by:Glenn Ray
ID: 40329324
By the way, I missed another step:  Make sure that both Primary and Secondary y-axes are the same range (0 to 300).  I forgot to scale them in the above steps.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
web browsers 9 43
Cannot locate cell 15 43
Error 1004 Excel 2013 11 18
URL to download Acrobat 8 Standard 3 22
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question