Excel – How to overlay two histograms in Excel

chartsmicrosoft excel

The first few lines of my raw data looks like this:

0    -4.05291
0    -2.75743
0    -0.374328
1    -23.829
1    -21.5973
1    -21.0714

I want to plot the data points with 0's and 1's separately as a histogram. This wasn't that hard to do: insert -> charts -> insert statistics charts and select the relevant data and I'm done. The charts are:

enter image description hereenter image description here

The orange plot corresponds to the first distribution (indexed by 0), and the blue one corresponds to the second (indexed by 1). The problem: I want to combine the two into a single chart with two differently-coloured bars. However I can't figure out how to do it. The obvious way is to right click -> select data -> add both data series to the chart, but the histogram still shows only one set of data. The data is definitely there – if I change chart types the other series shows up – but it doesn't show up in the histogram.

How can I do this with Excel? If Excel is unable to do this: what program would be able to do it? If it matters, I'm using Excel 2016.

Best Answer

Another option is to use the Histogram option of the Analysis Toolpak.

  1. Make sure the toolpak is enabled (if not, go to Files|Options|Add-ins)
  2. Split your data into columns (one for your '0' points and one for '1') points
  3. Create bins in another column (Excel will do this automatically but you need to be sure both series have the same bins)
  4. Go to Data|Data Analysis|Histogram
  5. Select your '0' points and the bins, then put the output on a 'new worksheet ply'
  6. Repeat for the '1'
  7. Combine those two tables and plot the result

Input Data Output chart

Related Question