Open In App

How to Make a Histogram in Excel – Different Methods

Improve
Improve
Like Article
Like
Save
Share
Report

A histogram is one of the most common data analysis tools in the business world. It is a graphical representation of data that clubs all the data that fall under specific regions. A histogram counts the values in datasets and groups them in “Bins” according to the frequency of their occurrence.

You can plot your data (large ones too) into a histogram to analyze quickly and make better decisions.

What is a Histogram in Excel

A histogram is a graphical representation, similar to a bar chart in structure. A Histogram organizes a group of data points into user-specified ranges and condenses a data series into an easily interpreted visual by taking many data points and grouping them into logical ranges or bins. 

The histogram looks the same as the example below.

Demo of Histogram

The numbers on the X-axis represent bins

How to Create a Histogram in Excel Using Formulas

You can make an Excel histogram easily using the FREQUENCY or COUNTIFS function. It automatically updates when your data changes. Just put your data in one column and bin numbers in another. Then, use a formula to count values in each bin and create the histogram from that summary data.

How to Create a Histogram in Excel Using FREQUENCY Function

To create a histogram in Excel, you can utilize the FREQUENCY function. This function counts values within designated ranges while excluding text and empty cells. The format is:

Syntax of FREQUENCY Function

FREQUENCY(data_array, bins_array)

Data_array represents a collection of values for which you aim to tally frequencies, while Bins_array is an array of bins used for categorizing these values.

Note: We’re using Microsoft Excel 2010 for this article, but the steps shown further will apply to all later versions of Excel.

Step 1: So the first step is to set up your table. For this article, I’m taking a table that shows the students’ names and the marks they obtained in their exams.

Dataset of students with their marks.

Step 2: Now, we need to set up the Bins/Ranges for our marks. This will help us to choose the bins for our histogram chart. 

Note: Bins are the intervals or categories into which your data will be grouped. The number of bins you choose will depend on your data and the details.

set up the Bins for your data.

So, once that is done, we must calculate the data frequencies for our bins. Let us make another column titled, ‘Frequencies’

We can calculate these frequencies easily by using the Excel function, ‘FREQUENCY’ Just go to the first cell of the Frequencies column and type the following –

=FREQUENCY(Start_cell_of_data_column: end_cell_of_data_column,Start_cell_of_bins_column: end_cell_of_bins_column) 

And press Ctrl + L-Shift + Enter.

In our case, it will look like this: =FREQUENCY(B2:B15, E2:E5) 

Frequencies
4
7
10
11

Now that the first frequency of the frequencies column has been successfully calculated, we can apply the same formula to the entire column by selecting that cell and dragging the cursor till the end of the column. The final result is as follows:

Calculate the Frequnecy

Step 3: Let us create the chart now. First, select the Bins and the Frequencies columns. Then go to the Insert section and select the Clustered Column from the Column dropdown under the Charts sub-sections

Go to Insert and select Clustered Column chart.

The final result will look something like this

Final result of Histogram.

Now, we don’t want the Bins to be displayed like this. Instead, we want them to be displayed under the X-axis. So, let us go ahead and delete it by clicking on the blue bars and pressing the Delete key.

Only with Frequency

Then, click on values on the X-axis and select them. Go to the Data section and click on “Select Data”. The following box will appear.

Now Click on Edit and wait for the following box to appear.

Now click on the small icon at the end of the dialog box and then select the Bins column from the first number to the last and press Enter.

Creating a Histogram using Data Analysis Tool Pack

To create a histogram using the Data Analysis tool pack, first, you need to install the Analysis Tool pack add-in. This will help you to quickly create the histogram by taking the data and data range( bins).

How to Install Data Analysis Tool Pak in Excel

Step 1: Click on the File tab and then select ‘Option’.

Select 'options' from the File tab.

 

Step 2: Select Add-ins in the navigation pane of the Excel options dialog box.

Select Add-ins from the pane.

Step 3: In the Manage drop-down, select Excel Add-ins and click Go.

Select Excel  Add-ins and press GO.

This would install the Analysis Toolpak and you can access it in the Data tab in the Analysis group.

How to Create a Histogram using Analysis Tool Pak in Excel

Step 1: Put the data in the Excel sheet 

Step 2: Now Go to Data Tab.

Step 3: In the Analysis Group click on Data Analysis.

Step 4: In the ‘Data Analysis ‘ Dialog box, select Histograjm from the list and click ok.

In the Histogram dialog box:

  • Select the Input Range
  • Select the Bin Range
  • Leave the Labels checkbox unchecked 
  • specify the Output Range if you want to get the Histogram in the same worksheet or in a different worksheet.
  • Select Chart Output.

This Would insert the bin frequency distribution table and chart in the specified distribution. 

FAQs

What are Histograms in Excel?

Histogram is a graphical representation, similar to a bar chart in structure. Histogram organizes a group of data points into user-specified ranges and condenses a data series into an easily interpreted visual by taking many data points and grouping them into logical ranges or bins. 

How you can Customize your Histogram Chart?

You can Customize your histogram chart by selecting the following options.

  • Add chart elements
  • Resize chart 
  • Format Axis
  • Remove the space between the bins

How do I edit the Histogram Chart?

After creating the histogram chart, you can edit it by selecting the chart and using the chart design and formatting options available in Excel. You can modify the chart’s title, axis labels, colors, styles, and other visual elements.

Can I update my chart if my data changes?

Yes , Histogram Chart can be updated by simply right – clicking on the chart and seletcing edit or modify the changes and you can see the changes you made in your data.



Last Updated : 06 Dec, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads