# How to Write a Histogram and How to Use Stratification 【Excel Template】

How to Write a Histogram and How to Use Stratification 【Excel Template】

(Duration: 4:19)

＜＜ Related Videos ＞＞

## Histogram Excel Template

Hi, this is Mike Negami, Lean Sigma Black Belt.

Today, I’ll talk about two items from the 7 QC Tools: The Histogram and Stratification with an Excel template. A Histogram graphically shows occurrence frequency of one numerical data group.  You can understand which data range is the most frequent at a glance.

## How to use the Histogram Template

This is the template screen.  Enable ‘Macro’ first.

After opening your source data file in Excel, click “Click this to generate a Histogram” in Cell C19.  Then, follow Excel’s messages,….  and it will ask you “Select your data range (Only numeric data)”, so select your data range and click ‘OK’.  Then, Excel generates a Histogram for you.  If your data is prepared beforehand, it takes less than 1 minute.

This example shows returned products’ GP%’s ranges on the horizontal axis and occurrence frequency on the vertical axis.  This also shows that 328 pieces of data were used and that their average GP% was 22.83%.

## When do we use Stratification?

However, there are two bumps in the charts.  This is a typical example.  It may include two kinds of data groups.  This result “as is” doesn’t show any meaningful insight.  So, I divided the source data into two groups by product category and made separate Histograms as shown below.

Here they are.  They both now show meaningful results.  As you can see, dividing data and making separate data analyses quite often discloses the truth.  This is Stratification.

You can apply Stratification to any data analyses, in fact you have to.  Otherwise, you might miss a critical data point.  Therefore, always keep Stratification in mind when you see data.

## Aim at actionable data analyses.

Let’s return to the Histogram.  The good thing about this chart is that you can visually and objectively see that products were returned with which GP% most.  I compared the high frequency product return GP% with regular GP % average.  Return GP% was much higher than the regular average!  In other words, the price was too high so that it was returned.

You may say “Duh, that’s no surprise.”  It’s important to verify the situation with data.  Now you may have another question: “Why were those products with high prices delivered without customers’ consensus?”  Since you’ve verified the situation with data, you can move to the next phase.

There is one issue regarding data analysis.  Although data analyses are very important, there are people who spend too much time on making charts.

For example, their analyses may have many nice graphs, but lack of actionable insights for the readers.  Those analyses are meaningless.  Therefore, delegate graph composition to template programs, then spend your time on adding actionable insights in your analyses for your readers or yourself.

I talked about Histogram and Stratification from the 7 QC Tools today.  Thank you very much for viewing.  Please click the ‘Subscribe’ button for “Learn world-class Kaizen and improve your work and yourself.  Also click and watch other recent videos.