How to Write a Histogram and How to Use Stratification 【Excel Template】
This topic is how to make a histogram using an Excel template. With the template, you can create a histogram in one minute. It is more important after it is created. Understanding stratification at that time is essential. Please spend your time for further analysis and action planning.
(Duration: 4:19)
DOWNLOAD ← Click this to download the “Histogram” template file.
＜＜ Related Videos ＞＞
- 【What are the 7 QC Tools?】 Flowchart, Histogram, Pareto Chart: These are some of the 7 QC Tools.
- 【How to make a Pareto Chart】 This is the first step of Data Mining!
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.
I made a Histogram Excel template that you can download by clicking the link below. Download and use the template and share how you like it.
DOWNLOAD ← Click this to download the “Histogram” template file.
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.
