How to Make a Histogram
This article explains how to make a histogram and how to use it. Histogram is a graphical analysis tool widely used in statistics. It is one of seven basic quality tools of process improvement. Histogram graphs frequency of data value occurrences and is often a handy tool for successful lean six sigma project implementation and quality improvement. It is a diagram consisting of bars which correspond to how often a data point or a group of data points fall within a predefined interval. Histograms help visually glean how data is distributed within a given data set and draw conclusions. For example, we may have a data set on the salaries of 25 employees and would like to know how they are distributed.
By looking at the salary distribution histogram we can quickly tell that most employees from a sample earn medium range salaries as the highest frequency occurs in the middle. 18 people out of 25 earn between $75,000 to $135,000. Now you are probably asking how to create a histogram. It is not complicated just follow the simple steps below to make a histogram.
How to make a histogram
Before laying out a step by step instruction on how to make a histogram, we need to have a data set that we would like to analyze and describe. Let’s use already mentioned data on salaries of 25 imaginary employees. Here is a list containing raw data on how much twenty five employees make per year.
First step to make a histogram is to create intervals
In order to create a histogram we need to develop a frequency table which will group our raw annual salary data by predefined intervals (or as statisticians like to refer to them bins). In our example, let’s use bins ranging from under $60K through $165K. Each interval has $15,000 spread. In excel you can create two columns for intervals. The column labeled “Intervals” will be used to group salaries by the intervals that they belong to. We will use “Interval Labels” for labels when we make a histogram chart.
Second step: run a frequency distribution formula
In the same excel spreadsheet where we have salary list, intervals and interval labels let’s create a column called frequency.
Then we need to run a FREQUENCY formula in excel which will group salary frequencies (i.e. a count of people who fall within an interval) by appropriate interval. We highlight a list of salaries to be included as the data_array. Then highlight intervals to be included as bins_array and hit ENTER.
The screen should look like this:
It takes a little trick to copy and paste the formula to the rest of the blank cells under Frequency column. The FREQUNECY formula will not be pasted to the rest of the blank cells by just using copy and paste. Instead you would need to highlight all the blank cells under Frequency where you would like the formula to be applied and then go to a formula bar and click on the formula as illustrated below:
Click CTRL+SHIFT+ENTER at the same time. You will see the Frequency column getting populated with numbers.
Thirds step creating a histogram
The last step demonstrates how to create a histogram using a bar charting function of excel. Once we develop a frequency table (that’s exactly what a set of numbers under the Frequency column is), we can highlight the numbers and then go to INSERT>COLUMN and select the first clustered column chart.
A histogram that we just made should pop up on the screen.
In order to add interval labels to the X axis of the histogram that we just made, right click the chart with the histogram. Click select data and Edit for horizontal axis labels and highlight the interval labels. That's all. We just created a histogram.
Finally I am making an excel file available for you to practice all the histogram development steps. You will find the discussed data , tables and the chart in this file. I recommend to copy the list of the salary raw data and try to recreate the frequency distribution table followed with a histogram. You can also use RANDBETWEEN function to develop a dummy data set if you desire to play with a larger data set or a data set with a different range of values. With this function you can define upper and lower range limits and let excel do the rest.