Home » Histogram Excel » Histogram Bin Intervals

How to Determine Histogram Bin Intervals

You can create Histograms in Excel using the Analysis Toolpak, but...

• You have to provide an array of bins for Excel to summarize the data;
  QI Macros will do this automatically for you.
• Excel's histogram will require some cleanup to remove the Chartjunk;
  QI Macros does this for you automatically.
• You have to run a separate tool to get descriptive statistics; QI Macros will do it all!
• Excel's histogram won't calculate process capability metrics like Cp, Cpk, Pp and Ppk;
  QI Macros does this automatically.

But if you still want to do it on your own, to show a useful spread of data, you may need to estimate the ideal histogram bin interval. Here's how:

1. Count the number of data points (e.g., count(A1:C5)).
2. Take the square root of the number of data points and round up to determine the number of bins required: (number of bins = sqrt(count(a1:C5)).
3. Divide the specification tolerance (USL-LSL or Max-Min value) by number of bins.

For example: 25 data points = 5 bars;
If the specification tolerance was 10 (USL-LSL), then the bin width would be 2. You would need to create a column of cells (0, 2, 4, 6, 8, 10, 12).

Juran's Quality Control Handbook provides these guidelines for the number of bars and states that they are not "rigid" and should be adjusted when necessary.

Number of Data Points	Number of Bars		Number of Data Points	Number of Bars
20-50	6		201-500	9
51-100	7		501-1000	10
101-200	8		1000+	11-20

Three Reasons to Use the QI Macros to Create a Histogram vs Manual Calculations or Using Excel by Itself

1. Ease of Use: Its easy to create a histogram in the QI Macros. Just select your data, click on the QI Macros menu and select Histogram. The QI Macros will perform all of the calculations and create the histogram for you. Saves lots of time over out of the box Excel and manual calculations.
   
2. Process Capability Metrics and Bell Curve: The QI Macros will prompt you for spec limits and will calculate Cp Cpk Pp Ppk for you. It also calculates twenty other metrics like % defects, sigma, etc. and draws a bell curve for you.
3. Interactive Features: QI Macros histograms are easy to update and experiment with:
   • change bin intervals and number of bars
   • change spec limits and see how that impacts Cp Cpk and Pp Ppk 

Learn More

• Watch video of how to create a histogram using the QI Macros
• QI Macros Histogram in Excel
• Process Capability Analysis with Cp, Cpk, Pp, Ppk