If you are searching for a solution or some special tricks to make a histogram in Excel using data analysis then you have landed in the right place. There are some quick steps to make a histogram in Excel using data analysis. This article will show you each and every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the central part of the article.
What Is Histogram?
A histogram shows the frequency of data in different intervals within the data range. It is a graph with a series of rectangular bars. The number and height of the bars are proportional to the number of different ranges called bins and to the frequency of data within those bins. For example, if you want to create a histogram from the result scores of the students in a class, you can assess the performance of the class at a glance. Because you will know how many students failed, how many of them got good grades etc the moment you look at the histogram. It can be very useful during your presentations.
What Is Bin Range in Histogram?
Creating Bins is a must-must step in creating a Histogram. Bins are actually buckets of different sizes. And, you have to distribute the dataset into those buckets to calculate the frequency and then, you can plot a bar chart with bins size vs frequency In short, the bins are described as continuous and not overlapping intervals of the variable. The intervals can be homogenous or not to create a histogram but they should be continuous.
How to Make a Histogram in Excel Using Data Analysis: Easy Steps
In this section, I will show you the quick and easy steps to make a histogram in Excel using data analysis on Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If anything of this article doesn’t work in your version then leave us a comment.
Step 1: Prepare Dataset
Obviously, to create a histogram, first, you have to prepare the dataset. For a histogram, you will need at least two columns where one column will contain the data, and the other one will contain the bin’s range.
- Here, I have created a dataset that contains 3 columns. The first two are for student information which is ID and Name and the 3rd one contains the GPA score of the students. And, I will create a histogram for the GPA scores.
- Then, I added another column to the dataset that is needed for the histogram. Here, I have added the range of bins which starts from 2 and ends at 4 with intervals of 0.25.
Step 2: Enable Data Analysis ToolPak
Before creating a Histogram using the Data Analysis ToolPak, you have to check if there is a Data analysis Toolpak existing in the Data tab or not. If you don’t find the Data Analysis ToolPak then follow the below steps to add this to the top ribbon.
- Open an Excel Workbook and go to File tab >> Options.
- Then, there will appear a pop-up window named Excel Options.
- Here, click on the Add-ins and select the “Go” button.
- Then, another new pop-up window will appear named “Add-ins”.
- Mark the box for “Analysis ToolPak” and press OK.
- After that, if you go to the Data tab in the top ribbon, you will see a menu named “Data Analysis” existing.
Step 3: Go to the Histogram Feature in Data Analysis ToolPak
Now, you have to click on the Data Analysis option under the Data tab in the top ribbon.
- Then, a new pop-up window named Data Analysis will appear.
- Here, click on the Histogram option in the Analysis Tools list and press OK.
Step 4: Insert Input, Output, and Bin Range
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:D24 as the Input Range which contains the GPA scores of the students.
- Then, select the cell range containing the histogram’s bin values. So, here, I have chosen cell E5:E13 as the Bin Range.
- Then, you have to assign a cell as the output range from which the output chart and frequency table will create.
- Lastly, mark the checkbox saying “Chart Output” so, you will get the Histogram chart as output and press OK.
- As a result, you will see the frequency table according to the bin range and the histogram at the assigned position in the active worksheet.
Read More: How to Make a Stacked Histogram in Excel
How to Make a Histogram in Excel with Two Sets of Data Using Data Analysis
While working with two sets of data, you can also create a histogram using the data analysis toolpak in Excel but the problem with this is that here, Excel will merge the 2 sets of data and show one set of bars in the histogram. I am showing this with an example below:
- Here, I have created 2 sets of data that contain GPA scores for Section A and Section B
- And, the list of bins is in cell range F5:F13.
- Then, insert the input range as “D5:E24” and the bin range as “F5:F13”.
- Also, select a cell from where the output will show.
- Similarly before, mark the checkbox of chart output and press OK.
- Thus, the result will be like those shown below. Here, you can see that, in the histogram, there is no separation between the two sets of data, and Excel has merged the two datasets as shown in the histogram.
If you want to create a histogram chart showing 2 sets of data individually then, you have to calculate the frequency of the dataset using the FREQUENCY function and then plot a histogram chart.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
In this article, you have found how to make a histogram in Excel using data analysis. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.
