# How to Create a Histogram in Excel with Two Sets of Data – 4 Methods

The sample dataset contains 3 columns: Student ID, Math, and English. You want to calculate how many students get marks between different intervals.

### Method 1 – Use the FREQUENCY Function to Create a Histogram with Two Sets of Data

#### Step 1: Calculating Frequencies in Excel

• Choose your Bins: the intervals you want the Histogram to use. Here, 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.

• Select G7 and enter the following formula.
`=FREQUENCY(C5:C15,F7:F11)`

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. You will get the frequency for more than the value of F11.

• Press ENTER to see the frequencies.

• Select H7 and enter the following formula.
`=FREQUENCY(D5:D15,F7:F11)`

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. You will get the frequency for more than the value of F11.

• Press ENTER to see the frequencies for English.

#### Step 2: Inserting a Chart to Create the Histogram

• Select the data. Here, I G7:H12.
• Go to the Insert tab.
• In Charts, select Insert Column or Bar Chart.
• Choose 2-D Column >> Clustered Column (here).

This is the output.

• Select the chart.
• In the Chart Design tab >> go to Select Data. (if you don’t select the chart, the Chart Design tab will not be visible)

In the Select Data Source dialog box:

• Select Series1.
• Choose Edit.

In the dialog box Edit Series:

• Enter the Series name in that dialog box. Here, Math.
• Click OK to see the Histogram.

In Select Data Source:

• Change the name of Series2 into English.
• Click Edit to change the Axis Labels.

In the Axis Labels dialog box:

• Select the Axis label range. Here, F7:F12 in the FREQUENCY worksheet.
• Click OK.

• Click OK in Select Data Source.

You can add Axis Titles in Chart Elements. Change the Chart Title.

The Histogram with two sets of data will be displayed.

### Method 2 – Using the Data Analysis ToolPak to Make a Histogram with Two Sets of Data

If the Data Analysis ToolPak is invisible, follow Step 1. Otherwise, move to Step 2.

#### Step 1: Inserting the Data Analysis ToolPak in Excel

• Go to the File tab.

• Choose Options.

In the Excel Options dialog box:

• In Manage:, choose Excel Add-ins.
• Click Go.

• Click Analysis ToolPak.
• Click OK.

Data Analysis is displayed on the ribbon.

#### Step 2: Use the Data Analysis Tool in Excel

• In the Data tab >> go to Data Analysis.

In the Data Analysis dialog box:

• Select Histogram and click OK.

The Histogram window will open.

• Select the data range to create the Histogram in Input Range. Here, C5:C15.
• Select the Bin Range of the Histogram. Here, C17:G17.
• Assign a cell as the Output Range. Here, F5:G11.
• Check Chart Output.
• Click OK.

You will see the frequency table according to the bin range and the Histogram.

You can create a Histogram with 1 set of data with the Data Analysis tool.

• In the Data tab >> go to Data Analysis >> In Data Analysis select Histogram and click OK.

In the Histogram window:

• In Input Range, select the data range for the Histogram . Here, D5:D15.
• Select the Output Range. Here, H5:H11.
• Click OK.

You will see another Bin and Frequency table for English.

• Select the chart.
• In the Chart Design tab >> go to Select Data.

In the Select Data Source dialog box:

In the Edit Series dialog box:

• Enter the Series name. Here, English.
• Enter the Series values. Here, I6:I11.
• Click OK to see the Histogram.

In the Select Data Source dialog box:

• Select Frequency.
• Choose Edit.

In the Edit Series dialog box:

• Enter the Series name. Here, Math.
• Click OK to see the Histogram.

• Click OK in Select Data Source.

The Histogram with two sets of data will be displayed.

### Method 3 – Using the COUNTIF Function to create a Histogram with Two Sets of Data

#### Step 1: Finding Frequencies in Excel

• Choose your Bins: the intervals you want the Histogram to use. Here, 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.
• Select an empty cell, G7 (here), and enter the following formula.
`=COUNTIF(\$C\$5:\$C\$15,"<" &F7)`
• Press ENTER.

Formula Breakdown

The COUNTIF function will count cells whose values fulfill a given condition.

• \$C\$5:\$C\$15 is the range for the lookup array.
•  “<” &F7 is the criteria: cell values less than the F7 cell value.
• The COUNTIF function will count cells whose values are less than 40.

• Select another empty cell, G8 (here), and enter the following formula.
`=COUNTIFS(\$C\$5:\$C\$15,">"&F7,\$C\$5:\$C\$15, "<="&F8)`
• Press ENTER.

Formula Breakdown

The COUNTIFS function will count cells whose values fulfill a set of conditions.

•  \$C\$5:\$C\$15 is the 1st range for the lookup array.
•  “>”&F7 is the 1st criterion: cell values greater than the F7 cell value.
• \$C\$5:\$C\$15 is the 2nd range for another lookup array.
•  “<=”&F8 is the 2nd criterion, cell values less than or equal to the F8 cell value.
• The COUNTIFS function will count cells whose values are between 41 and 50.

Use the same formula with relative cell references:

• Select G8. The Fill Handle will be displayed at the bottom-right corner of G8.
• Drag the Fill Handle to G12.

You will see all the frequencies for Math.

• Select an empty cell, H7 (here), and enter the following formula to see the frequencies for English.
`=COUNTIF(\$D\$5:\$D\$15,"<" &F7)`
• Press ENTER.

Formula Breakdown

The COUNTIF function will count cells whose values fulfill a given condition.

• \$D\$5:\$D\$15 is the range for the lookup array.
• “<” &F7 is the criterion, cell values are less than the F7 cell value.
• The COUNTIF function will count cells whose values are less than 40.

• Select an empty cell, H8 (here), and enter the following formula.
`=COUNTIFS(\$D\$5:\$D\$15,">"&F7,\$D\$5:\$D\$15, "<="&F8)`
• Press ENTER.

Formula Breakdown

The COUNTIFS function will count cells whose values fulfill a set of conditions.

• \$D\$5:\$D\$15 is the 1st range for the lookup array.
• “>”&F7 is the 1st criterion, cell values are greater than the F7 cell value
• \$D\$5:\$D\$15 is the 2nd range for another lookup array.
• “<=”&F8 is the 2nd criterion, cell values are less than or equal to the F8 cell value.
• The COUNTIFS function will count cells whose values are between 41 and 50.

• Use the same formula with relative cell references:
• Select H8. Drag the Fill Handle to H12.

You will see the frequencies for English.

#### Step 2: Inserting Chart to Create Histogram

Follow Step 2 in Method 1 to create the following Histogram with two sets of data.

### Method 4 – Using a Statistical Chart to Create a Histogram

• Select the data. Here, C5:D15.
• Go to the Insert tab.
• In Charts, select Insert Statistic Chart.
• Select Histogram.

This will be the result.

• Double-click the rectangle to open the Format Data Series window.
• In Series Options >> increase the Gap Width.

• Double-click the bins values to open the Format Axis window.
• In Axis Options >> change the Bin width.
• Change the Overflow bin and Underflow bin.

• In Chart Elements, add Axis Titles and Data Labels.
• Change the Chart Title.

Your Histogram is will be displayed

Read More: How to Make a Stacked Histogram in Excel

Practice here.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF