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.

**Read More:** How to Create a Histogram in Excel with Bins

### 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.

**Read More:** How to Make a Stacked Histogram in Excel

## Practice Section

Now, you can practice the explained method by yourself.

**Download Practice Workbook**

You can download the practice workbook from here:

## 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.

**Related Articles**

- Difference Between Excel Histogram and Bar Graph
- How to Create a Bin Range in Excel
- How to Change Bin Range in Excel Histogram
- [Fixed!] Excel Histogram Bin Range Not Working

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