Lean TrainingSite Map Lean Principles Muda 8 Wastes Goal - Lean Flow Roadmap - Lean Journey Hansei Lean Thinking Lean Dictionary Online Lean Training Private Training
Systems2win TrainingQuick Start Initial Training Systems2win Leadership
CustomersOperational Excellence Lean Manufacturing Lean Office and Service Lean Healthcare Customer List
Frequency Histogram and Relative Frequency Histogram with normality analysis and capability analysis
A histogram is a special type of vertical bar chart
that is used to discover and show the shape (frequency distribution) of a set of continuous data that has been separated into equally sized bins
Use your Excel Histogram template to measure (and reduce) variation in any process
Rather than showing the frequency counts...
the bars show the relative percentages.
By simply using a dropdown list...
you can easily switch between a Frequency Histogram, and a Relative Frequency Histogram.
Sample Histogram example
using dropdown lists to easily switch between Spec Target vs. Mean Average,
and between Frequency Count and Relative Frequency Percent
Try it for yourself
Why and when to use your
A true histogram requires continuous numeric data
If your data is not continuous, but rather is categorical data, such as:
Nominal data: this is not numerical
For example: How often do each of these 8 common types of defects occur?
Dichotomous data: that has only 2 choices
Example: Yes or no. True or false. Good or defective.
Ordinal data: choices that can be ranked, but not quantified
Example: Horrible, Bad, OK, Good, Great
then you can follow our instructions for how to make an Excel Histogram for discrete data.
How to use your
Find and open your template
Find and open your Histogram Excel template
in the same way that you find and open your other 150+ Systems2win templates.
If you don't yet own a license, you can download your free trial now.
Save your working document
following the usual document storage and naming conventions established by your leaders
Open a Blank Sheet
When you're ready to start doing your own real work...
click the button to 'Open a Blank Sheet'
Excel Ribbon > Systems2win tab > Open a Blank Sheet
This blank sheet is where you will do your real work
(not on the Sample sheet)
Or... Insert Sheet
As an alternative to opening a stand-alone document (as instructed above), you also have the option to Insert Sheet into any other Excel workbook.
If English is not your preferred language
Switch to your language, just like every Systems2win Excel template.
Now you are ready to start using your
to measure and reduce process variation
Enter header data
Brief description of the primary purpose of this document.
The Title that you enter here will also appear as the Title in your Excel Histogram.
The Author is the one person (or team) authorized to make changes to this document.
You'll get a lot more out of this training if you have your Histogram Excel template open in front of you
To change date format: Right-click > Format Cells > Number tab
Any data that you want at the top of your document.
Tip: Hide unused rows. Or copy this row for unlimited user-defined header data.
Enter (or link to ) your data
Click the Page Navigation link
to go to the Data section.
Enter (or link to) your data here — between the gold lines.
You can add unlimited rows above the bottom gold line.
Description and User-Defined columns
If your data is fractions (rather than whole numbers), then enter your desired number of decimal places in the Rounding field. (just above the Data section)
The Specifications section is optional, and can be hidden if not used.
At any time, you can use the dropdown list in row 4 to chart either:
The perfect ideal that your specifications seek to produce.
Lower Specification Limit (LSL)
The lowest boundary of what is still acceptable to deliver to meet your customer's needs.
Upper Specification Limit (USL)
The highest boundary of what is still acceptable to deliver to meet your customer's needs.
At any time... you can use the dropdown lists
At any time, you can use the dropdown lists at the top of the page to switch between:
Whenever your data changes...
Whenever your data changes,
use the button to 'Update Chart'
Excel Ribbon bar > Systems2win tab > Update Chart
This will automatically hide or show the correct rows to make your chart look right.
Tip: If you used the Insert Sheet button to add a histogram to a workbook that has your data, then the 'Update Chart' button won't be available.
You are, however, able to manually hide and show rows to optimize the size of the chart to fit the correct number of bins for your data.
Do not edit blue cells.
It will automatically count the number of rows of Observations in the Data section.
Minimum number of Observations
In order for analysis to have any meaning, a typical Histogram will usually have at least 50 rows of data in the data section.
Many of the calculation fields will display an asterisk (*)
until your data has at least the minimum number of observations that you specify in the field called 'Lowest'
which you will find in the next section of the page — below the Histogram
The next section of analysis data beside your histogram frequency chart analyzes the range of how clustered or spread out your data is.
High minus Low
Low and High values
Lowest and highest values observed.
A measure of how closely data values are clustered around the Mean Average.
+/- 3 Standard Deviations
3 Standard Deviations below and above the Mean Average.
If the data fits a normal distribution curve, then 97.3% of all values will fall within +/- 3 Standard Deviations.
The next analysis section of your Excel Histogram allows you to quickly determine whether or not your observed data fits a normal distribution curve.
Sum of all observed data values / Number of Observations (n)
An equal number of values occur above and below this value
The value that occurs most frequently
A value very close to zero (0)
means that your data is symmetrically balanced
A positive value
means that your data is 'right-tailed'
A negative value
means that your data is 'left-tailed'
A value very close to zero (0)
means that your data is distributed close to normally
A positive value
means that your data is peaked
(Data is more tightly grouped than a normal distribution)
A negative value
means that your data is flat
(There is less in the middle and more near the ends... compared to a normal distribution)
Skewness and Kurtosis should both usually be less than 2 or 3 to be considered normal.
Values turn bold red to alert you if over the thresholds that you specify in the Andon fields outside of the print area.
The final analysis section of your Excel Histogram helps you to determine whether or not your process is capable of meeting your specifications.
Anticipated defects per million opportunities.
Even if your sample data did not observe any defects, this calculates how many defects might be expected if you made a million observations.
Defects less than LSL and greater than USL
Expected defects per million opportunities that fail below the Lower Specification Limit and that fail above the Upper Specification Limit
Anticipated defects per million opportunities — as a percentage
1 minus Defects %
Sigma Quality Level
A popular measure of quality.
Larger number is better.
Six Sigma is superb (more than the quality level needed for most processes)
You have all the power of familiar Microsoft Excel to perform any additional analysis of capability, normality, or anything else that you want to analyze about your Excel Histogram.
ASQ has published a wonderfully succinct training page to help you make sense of different histogram data patterns that you might encounter in your frequency distributions.
And the ultimate purpose of every Lean Six Sigma tool or method is to stimulate people to ask Hansei questions for Lean Thinking.
to empower every team member
Download this Histogram template to easily switch between a Frequency Histogram and a Relative Frequency Histogram
Download Free Trial
If your organization has
not yet provided a license,
download your free trial now
Your Systems2win templates come with free New User Training
to quickly learn features that are common to all 150+ templates