How do I overlay two histograms in Excel?

Question

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:

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.

Answer 1

Apparently (in Excel 2016), using a histogram doesn't seem to be possible with multiple series.

However, you can obtain the same result with a bar chart. It requires a bit more work, but it's fairly easy to do! Here is what I did.

1. Create a "Category" table (orange), that will put the values into different ranges.
2. Make sure the first column is a unique ID.
3. The Max and Min columns can be filled manually, or automatically with a formula. Just make sure that there is a -9999 and +9999 (or any other big value) as the "lowest min" and the "highest max".

4. In your data table, add the following formula (provided the orange table is named Category):

   =VLOOKUP(SUMPRODUCT(([@Value]>=Category[Min])*([@Value]<Category[Max])*(Category[Category ID])),Category,4)
   

5. Insert a pivot table (values: count of your lines) and pivot chart as shown below:

Answer 2

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

Answer 3

Use Past3: https://www.techworld.com/download/office-business/past-314-3330821/

It is a free and powerful tool for doing statistical analysis and making graphical illustrations. I needed to make combined histograms and this program made the process super easy. I struggled with Excel for quite some time but then found Past3 and have been using that for most of my illustrations since then. Here I combined three sets of data in one histogram so it looks a bit crazy with the way it overlaps the colors but still looks good and very intuitive:

With two colors/sets it looks really good.

Answer 4

You are very close to your answer. The only thing that you have yet to do is select the series in your second graph (click the graph, then click the bars and make sure all bars are selected (see picture 1). Press Ctrl+C and then click the first graph and press Ctrl+V. I did the same as you did with random data and I found this (see picture 2):

The red bars are added by copying and pasting.

Answer 5

Follow Peter Hulls instructions however do NOT use the histogram function, this only works for 1 set of data. You must use a bar chart. 1. get data analysis function 2. use to make histogram, selecting data and bins(the bins u choose and must be the same for all) 3. repeat step 2 for all the data you need 4. copy and paste the same bin set you used for everyone of the data sets over to a new sheet once 5. copy and past the frequencies over for each set 6. go to insert, and select bar graph 7. select data 8. add each series as separate series 9. finally select the bins as the horizontal and you should have a histogram but created as a bar graph