# Excel Histogram

Frequency Histogram and Relative Frequency Histogram with normality analysis and capability analysis

## What is a histogram?

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

What's a Relative Frequency Histogram?

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

## When to use a histogram

Use your Excel Histogram template

- to visually show and analyze the shape of the frequency distribution of your data
- to determine whether or not the data fits a normal distribution curve
- to decide whether to use Average or Median when using your Kanban Calculator
- to evaluate Process Capability (can your process meet your specifications?)

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.

Bookmark = how

How to use your

## Excel Histogram template

Find and open your template

Find and open your

Histogram Excel template

(Histogram.xlsx)

in the same way that you find and open

your other 150+ Systems2win templates.

f 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 bar > Systems2win tab > Open a Blank Sheet

This blank sheet is where you will do your real work

(not on the Sample sheet)

Rename your new 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 your team is ready to start using your

## Histogram Excel template

Enter header data

You'll get a lot more out of this training if you have your Histogram Excel template open in front of you

Title

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.

Author

The Author is the one person (or team)

authorized to make changes to this document.

Revised Date

To change date format: Right-click > Format Cells > Number tab

Header Data

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.

Bookmark = data

Enter (or link to ) your data

Click the Page Navigation link >>>

to go to the Data section.

Data column

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

are optional.

Specifications (optional)

After you have entered your data in the Data section...

click the Navigation link to go back the the Histogram section of the page.

Or you can always use CTRL+Home to go the the top left corner of any Excel document.

The Specifications section is optional, and can be hidden if not used.

If you don't enter specifications, you can optionally chart the Mean Average + or - 3 Standard Deviations

Target (T)

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:

- Vertical lines of Target, LSL and USL,
- or vertical lines of Mean Average, and +/- 3 Standard Deviations

and to to switch between:

- Frequency Histogram (Y axis is actual count of frequency distribution)
- Relative Frequency Histogram (Y axis is percentages... that add up to 100%)

Whenever your data changes...

Whenever your data changes,

use the button to 'Update Chart'

which will automatically hide or show the correct cells to make your chart look right.

Excel Ribbon bar > Systems2win tab > Update Chart

Observations

Observations (n)

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, Many of the calculation fields will display an asterisk (*) which you will find in the next section of the page — below the Histogram |

Range

The next section of analysis data beside your histogram frequency chart

analyzes the range of how clustered or spread out your data is.

Range

High minus Low

Low and High values

Lowest and highest values observed.

Standard Deviation

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.

Bookmark = norm

## Normal Distribution?

The next analysis section of your Excel Histogram

allows you to quickly determine whether or not your observed data fits a normal distribution curve.

Mean Average

Sum of all observed data values / Number of Observations (n)

Median Average

An equal number of values occur above and below this value

Mode

The value that occurs most frequently

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.

Skewness

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'

Kurtosis

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)

Bookmark = cap

## Capability Analysis

The final analysis section of your Excel Histogram

helps you to determine whether or not your process is capable of meeting your specifications.

Defects (dpmo)

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

Defects (%)

Anticipated defects per million opportunities — as a percentage

Yield (%)

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)

User-Defined Analysis

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.

### Histogram Frequency Distribution Patterns

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.

This histogram Excel template comes with
priced low enough to empower every team member |