This guide will explain how to overlay two histograms in Excel.
Presenting two histograms in a single chart can help compare how data is distributed between two different populations.
Histograms are a type of graph that visualizes the frequency of numerical data. Histogram charts show the frequency distribution in a population.
If you want to compare the frequency distribution of two different populations, you can do so by comparing their histograms. However, you may want to overlay the two histograms into a single chart.
Let’s take a look at a quick example to illustrate when you may need to create a chart with two histograms.
Suppose you have demographic data for two different cities. You want to compare and contrast the frequency distribution of the ages of both populations.
We can use the FREQUENCY function to create a table that tracks the frequency distribution of both populations.
After creating a frequency distribution table, we’ll use a line or column chart to present both histogram data in a single graph.
Now that we know when to overlay two histograms in Excel, let’s learn how to use it on an actual sample spreadsheet.
A Real Example of Overlaying Two Histograms in Excel
The following section provides an example of how to create a chart with two histograms. We will also explain the formulas and tools used in this particular example.
First, let’s take a look at a real example of a spreadsheet that overlays two histograms.
Our dataset includes customer ratings ranging from 1 to 5. We have a column indicating whether the rating was given in Week 1 or 2. This data will help us create a histogram that compares the performance of both weeks.
We can use the FREQUENCY function to create a frequency distribution table for both the first and second weeks.
To get the values in Column G, we just need to use the following formula:
=FREQUENCY(B15:B31, E3:E6)
The FREQUENCY function will return the number of instances found that fall within specified bins. In this example, the bins are the ratings from 1 to 5.
For example, the table above indicates that we received three 4-star ratings in the first week and 6 in the second week.
After finding the frequency distribution, we can now convert the table into a chart. The chart below overlays the histograms of both populations as a line graph.
Alternatively, we may also use a 2-D or 3-D chart to compare the frequency distributions of both groups in a single chart. In the example below, it is much easier to determine the difference in the distribution of ratings between the first and second weeks.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try out this technique to overlay two histograms in Excel, head over to the next section to learn how to do it!
How to Overlay Two Histograms in Excel
This section will guide you through each step needed to overlay two histograms in a single Excel chart. You’ll learn how to use the FREQUENCY function to create a table with the frequency distribution of both populations.
Next, you’ll learn how to convert the table into an Excel chart that presents the frequency distribution of both populations.
Follow these steps to overlay the histograms of two different groups in Excel:
- First, create a table for placing the frequency distribution of both populations. In this example, we’re defining five bins for our histogram, one for each possible rating.
- We’ll use the
FREQUENCYfunction to return the frequency distribution of the first population. Select the first population’s range of values as the function’s first argument.
- Next, select the bins array as the second argument. We can exclude the last row since the function will add a last row for the count of ratings greater than 4.
- Hit the Enter key to evaluate the
FREQUENCYfunction.
- We’ll use the same technique to return the second population’s frequency distribution.
- While the table is selected, head to the Insert tab and click on the 2-D Line icon. Select the option labeled ‘Line with Markers’
- You should now have a line chart that acts as two overlayed histograms.
These are all the steps you need to overlay two histograms in Excel.
This step-by-step guide should provide you with all the information you need to overlay two histograms in Excel.
You should now understand how to use the FREQUENCY function to create your own frequency distribution dataset.
Histograms are just one of many ways you can visualize your Excel data. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.
With so many other Excel functions available, you can find one appropriate for your use case.
Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!
