Making a Histogram Using Excel

Stesp to group data and make a histograms using Excel are presented.
Note that you need to first load the analysis toolPack in Excel if it is not loaded yet.

Example uses the data shown below and a class with of 10.
A set of 50 data values to be used in the steps to make a histogram using Excel are shown below.
142.6   ,   100.3   ,   102.6   ,   99.5   ,   120.6   ,   101.5   ,   100.4   ,   100.0   ,   111.6   ,   102.6   ,   90.7   ,   96.9   ,   103.2  ,  109.4  ,  115.5  ,  121.3  ,  101.6  ,  112.5  ,  100.4  ,  110.4   ,   104.5  ,  122.4  ,  100.9  ,  122.5  ,  150.0  ,  104.7  ,  112.7  ,  112.5  ,  121.5  ,  123.7   ,   102.5  ,  110.2  ,  113.6  ,  121.3  ,  115.5  ,  109.4  ,  103.2  ,  96.9  ,  90.7  ,  84.6   ,   78.8  ,  73.3  ,  109.3  ,  111.5  ,  113.7  ,  79.0  ,  107.6  ,  109.3  ,  103.8  ,  78.0

The whole data set, in a CVS file format, may be downloaded at data for histograms for practice.



Step 1 - Organize the data sets in a column as shown below. Only the first 15 rows are shown, but there is a total of 50 data values.
Organize Data in a Column
Step 2 - Use the Excel MAX and MIN functions to find the minimum and maximum of the data values:
1) Type the functions "=MAX(A1:A50)" and press "Enter"
2) then type "=MIN(A1:A50)" and press "Enter".

Type the Maximum and Minimum Functions in Excel
2) The results should be as follows

Type the Maximum and Minimum Functions in Excel
Step 3 - Calculate \( N \) the number of classes and their limits. The minimum, the maximum and the class width (given above) are needed.

\( N = \dfrac{Maximum - Minimum }{Class width} = \dfrac{150 - 73.3}{10} = 7.67 \)

Round the number of classes \( N \) to the nearest integer \( 8 \).
Start from the nearest "easy to read" value close to the minimum. The minimum of this data is \( 73.3 \) and it makes sense to start the classes from 70 in order to make them easy to read when we interpret the histogram.
Write the limits of the classes starting from 70 addig the class with:
70
70 + 10 = 80
80 + 10 = 90
90 + 10 = 100
100 + 10 = 110
110 + 10 = 120
120 + 10 = 130
130 + 10 = 140
140 + 10 = 150
We stop at 150 since the maximum of the data is 150 and we therefore can cover all the data values in the set.

Step 4 - Use the limits calculated above including the minimum \( 70 \) to set the bin range in Excel as shown below. "Bin" is just a label.

Set Up The Bins in Excel
Step 5 - Press on the "Data" tab and click on "Data Analysis".

Press Data Tab and Click Data Analysis
Step 6 - Select "Histogram" and click on "OK".

Select Histogram and Click OK in Excel
Step 7 - Clear Input, Bin, labels, and output areas.

Clear Input and Output Areas
Step 8 - Click inside "Input Range" and use the mousse to select the column containing the data values. There should be 50 data values (which are not all shwown) in this example.

Select Input Range Data
Step 9 - Click inside "Bin Range" and use the mousse to select the column containing the Bin without the label "Bin". (This is just to remember what these numbers are)

Select Bin Range
Step 10 - Select and click inside "Output Range" and use the mousse to select a cell for the output of the frequency table.

Select Output Range
Step 11 - Select "Chart Output" and click OK.

Select Chart Output
Step 12 - Outputs of histogram done by "Data Analysis".

Output of Histogram Application
Step 13 - Click on one of the vertical bars; a panel should appear on the right where you can make the "Gap Width" equal to zero in order to eliminate any gaps between the bars to make a histogram.

Output of Histogram Application
Note The following steps are included to better format the histogram and make it easy to interpret and use by readers.
Step 14 - Rewrite classes using inequalities and intervals open on the left (value not included) and closed on the right (value included).
The first bin \( 70 \) defines a class including all data values smaller than or equal to 70, hence written as "<=70"
The second bin \( 80 \) defines a class including all data values larger than 70 smaller than or equal to 80, hence written in interval for (70 , 80]. All the remaining bin are written in interval form except the last one.
The last bin "more" means greater than 150 and is written as ">150".


Rewrite Bins as Classes
Step 15 - Double click any label on the horizontal axis (it should give a rectangle) then right click the mousse to obtain a panel where you click on "Select Data"..


Select a Label and Double Click
Step 16 - Click "edit" under "Horizontal (Category) Axes Labels"


Click Edit
Step 17 - Firs clear the "Axis label range" (window), then click the mousse inside and select the column with the new labels


Select a Label and Double Click
then click "OK" to end up with a histogram with a well defined classes as shown below that you can further customize to your own needs and applications.

Histogram Made Using Excel


More References and links

  1. Data Analysis in Excel
  2. Load The Analysis ToolPack in Excel
  3. Histograms for Grouped Data
  4. Histograms of Real Life Data .