How to Create a Histogram in Excel?
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.
Steps to create a Histogram in Excel:
Note: We’re using Microsoft Excel 2010 for this article, but the steps shown further will be applicable for 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.
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.
So, once that is done, we must calculate our 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)
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:
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
The final result will look something like this
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 in the blue bars and pressing the Delete key.
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 till last and press Enter.
Your Histogram is finally ready!