Share via

Histogram

Anonymous
2021-04-29T19:31:03+00:00

Hello, 

I've created a histogram and I'm trying to add reference lines to visualize mean and standard deviations on the chart. I want these lines to be dynamic i.e., I want to see them change as I change the data. Is there a way I can do it?

Best, 
Priya

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2021-05-06T01:06:57+00:00

    Microsoft Excel Histograms, the native ones, are different from regular column charts. They are newer and use a new charting system which may ultimately make charting much easier and more flexible, but so far they have not been developed enough to realize their promise.

    If you built a regular old column chart to serve as a histogram, you could add more data points to the chart, as XY Scatter series, and use these added points as anchors for error bars that would illustrate the mean, standard deviations, and other features of the distribution. But you can't add data series to one of the newfangled histograms.

    I wrote a tutorial called Histogram With Normal Curve Overlay, where I showed how to add an XY Scatter series for a bell curve that overlaid a histogram made using an area chart (that's right). Maybe you could use a similar approach to add points for mean, ±SD, etc.

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2021-05-05T00:54:12+00:00

    Excel 365 Pro Plus with Power Pivot and Power Query.

    Since your 2104 row database is well protected,

    I generated a similar, fictional database.

    Mean and Standard Deviation are properties  of the database,

    not the Histogram, and cannot be properly charted together.

    https://www.mediafire.com/file/3akqhyh62tu4tiq/05\_03\_21.xlsx/file

    https://www.mediafire.com/file/uu6awe14ypl209y/05\_03\_21.pdf/file

    1 person found this answer helpful.
    0 comments

11 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-05-03T14:18:48+00:00

    Hi Priya,

    Really appreciate your effort to provide the screenshots with us, we do wanna further investigate it, if convenient, a detailed data from your chart would be much better for us to test, so if convenient and possible, could you please upload a sample file to OneDrive and share with us? We’ll try our best to figure out.

    Thanks again for your cooperation and understanding.

    Best regards,

    Gloria

    0 comments
  2. Anonymous
    2021-04-30T15:22:41+00:00

    Hi Priya,

    Thanks for your time to post in our community! 

    It seems that you're trying to add references lines on the histogram chart and prefer the dynamic effect, to better understand your scenario,we would like to test it in our side, so could you please provide some screenshots about your scenario or upload a sample file to OneDrive and share the link with us? This helps us better understand and further investigate it.

    We highly appreciate your cooperation in advance.

    Best regards, 

    Gloria

    Hi Gloria, 

    Thanks for the prompt reply. I'm trying to work with a basic histogram data, like the one below.

    What I  would love to add to my histogram is the 3 reference lines of Mean and 1st standard deviations like below, To get this, I had to manually scale the needed points to the reference X axis. 

    I followed the following steps: https://www.storytellingwithdata.com/blog/2018/8/7/tactical-tip-embedding-a-vertical-reference-line-in-excel 

    This is a long process and yet the chart is not dynamic with data. I'm sure there must be a simpler way. It would be very helpful if you can walk me through it. 

    [PII is masked by Gloria Li MSFT Support]

    0 comments
  3. Anonymous
    2021-04-29T23:17:48+00:00

    Hi Priya,

    Thanks for your time to post in our community! 

    It seems that you're trying to add references lines on the histogram chart and prefer the dynamic effect, to better understand your scenario,we would like to test it in our side, so could you please provide some screenshots about your scenario or upload a sample file to OneDrive and share the link with us? This helps us better understand and further investigate it.

    We highly appreciate your cooperation in advance.

    Best regards, 

    Gloria

    0 comments