How to Make a Histogram in Excel with Two Sets of Data (4 Ways)

Sometimes for data visualization, you may need to make a Histogram. Basically, when you have a large dataset then you can visualize the situation easily with a Histogram. In this article, I will explain how to make a Histogram in Excel with two sets of data.

What Is a Histogram?

A Histogram is a chart that contains some rectangles. Here, the rectangle’s width denotes a certain range as intervals and the height of the rectangle denotes the number of frequencies. Furthermore, you will see a term named Bins here. Bins are actually classes or intervals.

How to Make a Histogram in Excel with Two Sets of Data: 4 Methods

Here, I will describe 4 methods to make a Histogram in Excel with two sets of data. In addition, for your better understanding, I’m going to use a sample dataset. Which contains 3 columns. They are Student ID, Math, and English. Now, let’s calculate how many students get marks between different intervals. The dataset is given below.

1. Use of FREQUENCY Function to Make a Histogram with Two Sets of Data

Here, you can use the FREQUENCY function to make a Histogram with two sets of data in Excel. Basically, I will find out the frequencies with the FREQUENCY function and then plot a simple bar graph for creating the Histogram. Here, I will calculate the frequencies in Step 1 and I will show you how to insert a chart in Step 2.

Step 1: Calculating Frequencies in Excel

Firstly, make your Bins. This means you should write the values about what interval you want to make the Histogram. Here, I want to see how many students get marks below 40, also between 41 to 50, 51 to 60, 61 to 70, 71 to 80, and more than 81.

Now, select the G7 cell and write the following formula.

=FREQUENCY(C5:C15,F7:F11)

Here, in this formula, the FREQUENCY function will count how many times a value comes within a given interval. Here, C5:C15 is the data array and F7:F11 is the Bins array. Furthermore, you will get the frequency for more than the value of F11 also.

Subsequently, press ENTER to get the frequencies.

Now, select the H7 cell and write the following formula.

=FREQUENCY(D5:D15,F7:F11)

Here, in this formula, the FREQUENCY function will count how many times a value comes within a given interval. Here, D5:D15 is the data array and F7:F11 is the Bins array. Furthermore, you will get the frequency for more than the value of F11 also.

Subsequently, press ENTER to get the frequencies for English.

Step 2: Inserting Chart to Create Histogram

Now, I will insert the bar chart.

Firstly, you have to select the data. Here, I have selected the range G7:H12.
Secondly, you have to go to the Insert tab.
Thirdly, from the Charts group section, you have to select Insert Column or Bar Chart.
Lastly, I have chosen from the 2-D Column >> Clustered Column. Here, the selection will be according to your preference.

Now, you will see the following output.

Now, select the chart.
Then, from the Chart Design tab >> go to the Select Data feature. Here, if you don’t select the chart then this Chart Design tab will not be visible.

After that, you will see the following dialog box named Select Data Source.

Now, from the dialog box of Select Data Source, select Series1.
Then, you have to choose the Edit option.

Actually, those are the steps for changing the name of Series1.

After selecting the Edit option, another dialog box named Edit Series will appear.

Now, you may write down or select the Series name in that dialog box. Here, I have written the Series name as Math.
Then, you need to press OK to get the corresponding Histogram.

Subsequently, the previous dialog box of Select Data Source appears.

Similarly, change the name Series2 into English.
Then, you should click on the Edit option to change the Axis Labels.

At this time, a dialog box named Axis Labels will appear.

Then, you have to select the Axis label range. Here, I have selected the range from F7:F12. Which remains in the worksheet named FREQUENCY.
Now, press OK to make the changes.

After this, press OK on the Select Data Source box.

Here, you can add the Axis Titles from the Chart Elements section. Apart from this, you can change the Chart Title.

Lastly, you will get the Histogram with two sets of data.

2. Employing Data Analysis ToolPak to Make a Histogram with Two Sets of Data

In this section, I will show you the quick and easy steps to make a Histogram in Excel using Data Analysis ToolPak.

Firstly, you have to check that in your Excel Custom Ribbon is the Data Analysis ToolPak visible or not. If the Data Analysis ToolPak is invisible then you should follow Step-1. Otherwise, you may follow from Step-2.

Step 1: Inserting Data Analysis ToolPak in Excel

Here, I will show how to insert the Data Analysis ToolPak in Excel.

Firstly, you have to go to the File tab.

At this time, you will see the following window.

Now, from that window, you should choose the Options menu.

At this time, a dialog box named Excel Options will appear.

Firstly, from that dialog box, you have to go to the Add-ins command.
Secondly, choose Excel Add-ins in the Manage: box.
Finally, press the Go button.

Subsequently, another dialog box named Add-ins will appear.

Now, you need to click on the Analysis ToolPak.
Then, press OK to get the changes.

Lastly, you will see that there is a new ribbon named Data Analysis under the Data tab.

Step 2: Use of Data Analysis Tool in Excel

In this section, I will demonstrate how to use the Data Analysis tool in Excel.

Firstly, from the Data tab >> go to the Data Analysis feature.

At this time, a dialog box named Data Analysis will appear.

Now, select Histogram and then Click on OK.

After pressing OK in the Data Analysis window there will open a new pop-up window named Histogram.

Here, select the data range for which you want to create a Histogram in the box named Input Range. Here, I have selected C5:C15 as the Input Range which contains the marks of Math.
Then, select the Bin Range of the Histogram. Here, I have selected cells C17:G17 as the Bin Range.
Then, you have to assign a cell as the Output Range where the output chart and frequency table will be created. Here, I have selected the cells F5:G11.
After that, mark the checkbox named Chart Output.
Finally, press OK.

As a result, you will see the frequency table according to the bin range and the Histogram at the assigned position.

Actually, you can make a Histogram with 1 set of data with the Data Analysis tool. Thus, I will add one more set of data to the corresponding Histogram.

Thus, again from the Data tab >> go to the Data Analysis feature >> then from the dialog box named Data Analysis select Histogram and then Click on OK.

After pressing OK in the Data Analysis window there will open a new pop-up window named Histogram.

Here, select the data range for which you want to create a Histogram in the box named Input Range. Here, I have selected D5:D15 as the Input Range which contains the marks of English.
Then, select the Output Range where the output chart and frequency table will be created. Here, I have selected the cells H5:H11.
Finally, press OK.

As a result, you will see another Bin and Frequency table which is for the English mark.

Now, select the chart.
Then, from the Chart Design tab >> go to the Select Data feature.

Subsequently, a dialog box named Select Data Source appears.

Now, from the dialog box of Select Data Source, you have to choose the Add feature.

After selecting the Add feature, another dialog box named Edit Series will appear.

Now, you can write down or select the Series name in that dialog box. Here, I have written the Series name as English.
Then, you have to include the Series values. Here, I have used the range I6:I11.
Finally, you need to press OK to get the corresponding Histogram.

After that, again you will see the dialog box named Select Data Source.

Now, from the dialog box of Select Data Source >> select Frequency.
Then, you have to choose the Edit option.

Actually, those are the steps for changing the name of the Frequency.

After selecting the Edit option, another dialog box named Edit Series will appear.

Now, you may write down or select the Series name in that dialog box. Here, I have written the Series name as Math.
Then, you need to press OK to get the corresponding Histogram.

Lastly, press OK on the Select Data Source box.

Finally, you will get the following Histogram with two sets of data.

3. Using COUNTIF Function to Make a Histogram with Two Sets of Data

Here, you can use the COUNTIF function to create a Histogram with two sets of data in Excel. Actually, I will find out the frequencies with the COUNTIF function and then plot a simple bar graph for creating the Histogram.

Here, in Step 1 I will find out the frequencies and in Step 2 I will show you how to insert a chart.

Step 1: Finding Frequencies in Excel

Firstly, make your Bins. This means you should write the values about what interval you want to make the Histogram. Here, I want to see how many students get marks below 40, and between 41 to 50, similarly 51 to 60, 61 to 70, 71 to 80, and more than 81.
Secondly, select an empty cell like G7 and write down the following formula in that cell.

=COUNTIF($C$5:$C$15,"<" &F7)

Subsequently, press ENTER.

Formula Breakdown

Here, in this formula, the COUNTIF function will count those cells whose values fulfill a given condition.

Firstly, $C$5:$C$15 is the range for the lookup array.
Secondly, “<” &F7 are the criteria, which means whether the cell values are less than the F7 cell value or not.
Finally, the COUNTIF function will count those cells whose values are less than 40.

Now, select another empty cell like G8 and write down the following formula in that cell.

=COUNTIFS($C$5:$C$15,">"&F7,$C$5:$C$15, "<="&F8)

Subsequently, press ENTER.

Formula Breakdown

Here, in this formula, the COUNTIFS function will count those cells whose values fulfill a set of conditions.

Firstly, $C$5:$C$15 is the 1st range for the lookup array.
Secondly, “>”&F7 are the 1st criteria. Which means whether the cell values are greater than the F7 cell value or not.
Thirdly, $C$5:$C$15 is the 2nd range for another lookup array.
Fourthly, “<=”&F8 are the 2nd criteria, which means whether the cell values are less than or equal to the F8 cell value or not.
Finally, the COUNTIFS function will count those cells whose values are between 41 and 50.

Now, I will copy the same formula using relative cell references. To do this, select cell G8. A square box will be shown in the bottom-right corner of cell G8, it is called the Fill Handle icon. Click the Fill Handle icon, hold it, and drag until you reach cell G12.
Then, release the Mouse button.

Finally, you will get all the frequencies for Math.

Again, select an empty cell like H7 to get the frequencies of English and write down the following formula in that cell.

=COUNTIF($D$5:$D$15,"<" &F7)

Subsequently, press ENTER.

Formula Breakdown

Here, in this formula, the COUNTIF function will count those cells whose values fulfill a given condition.

Firstly, $D$5:$D$15 is the range for the lookup array.
Secondly, “<” &F7 are the criteria, which means whether the cell values are less than the F7 cell value or not.
Finally, the COUNTIF function will count those cells whose values are less than 40.

Now, select another empty cell like H8 and write down the following formula in that cell.

=COUNTIFS($D$5:$D$15,">"&F7,$D$5:$D$15, "<="&F8)

Subsequently, press ENTER.

Formula Breakdown

Here, in this formula the COUNTIFS function will count those cells whose values fulfill a set of conditions.

Firstly, $D$5:$D$15 is the 1st range for the lookup array.
Secondly, “>”&F7 is the 1st criteria. Which means whether the cell values are greater than the F7 cell value or not.
Thirdly, $D$5:$D$15 is the 2nd range for another lookup array.
Fourthly, “<=”&F8 is the 2nd criteria. Which means whether the cell values are less than or equal to the F8 cell value or not.
Finally, the COUNTIFS function will count those cells whose values are between 41 and 50.

Now, I will copy the same formula using relative cell references. To do this, select cell H8. A square box named Fill Handle icon will be in the bottom-right corner of cell H8. Click the Fill Handle icon, hold it, and drag until you reach cell H12.
Then, release the Mouse button.

Finally, you will see the frequencies of English marks.

Step 2: Inserting Chart to Create Histogram

As I have already shown you this step in method 1. So, I will not repeat that here again. You can follow Step-2 of method 1 to make the following Histogram with two sets of data.

Furthermore, I have attached the image of the final result.

4. Applying Statistical Chart to Make a Histogram

Moreover, you can apply the Statistical Chart to make a Histogram with two sets of data in Excel. Actually, this is the built-in feature of Excel. But, here you can’t show two sets of data separately. Because the Histogram chart will consider the total data range and based on that it will give the result. Let’s see the steps.

Firstly, you have to select the data. Here, I have selected the range C5:D15.
Secondly, you have to go to the Insert tab.
Thirdly, from the Charts group section, you have to select the Insert Statistic Chart.
Finally, select Histogram.

At this time, you will see the following result.

Now, double-click on the rectangle. So, a new window named Format Data Series will appear beside the worksheet.
Then, from the Series Options >> increase the Gap Width according to your preference.

Again, double-click on the bins values. So, a new window named Format Axis will appear beside the worksheet.
Then, from the Axis Options >> change the Bin width according to your preference.
Also, you may change the Overflow bin and Underflow bin.

Here, you can add the Axis Titles and Data Labels from the Chart Elements section. Apart from this, you can change the Chart Title.

Finally, your Histogram is ready.

Practice Section

Now, you can practice the explained method by yourself.

Download Practice Workbook

You can download the practice workbook from here:

Creating Histogram with Two variables.xlsx

Conclusion

I hope you found this article helpful. Here, I have explained 4 suitable methods to make a Histogram in Excel with two sets of data. Please, drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Excel Histogram | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Make a Histogram in Excel with Two Sets of Data (4 Ways)

What Is a Histogram?

How to Make a Histogram in Excel with Two Sets of Data: 4 Methods

1. Use of FREQUENCY Function to Make a Histogram with Two Sets of Data

Step 1: Calculating Frequencies in Excel

Step 2: Inserting Chart to Create Histogram

2. Employing Data Analysis ToolPak to Make a Histogram with Two Sets of Data

Step 1: Inserting Data Analysis ToolPak in Excel

Step 2: Use of Data Analysis Tool in Excel

3. Using COUNTIF Function to Make a Histogram with Two Sets of Data

Step 1: Finding Frequencies in Excel

Step 2: Inserting Chart to Create Histogram

4. Applying Statistical Chart to Make a Histogram

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply