fbpx

Quality Management 4: Histogram (any version of Excel)

Histogram is used to visualize the frequency with which data occurs. This is a good way of understanding data more than just sum and average. It is a good idea to look at each data set you get as a histogram. Here is how you do it in Excel.

Quality Management: Histogram in Excel

Download this Sample File. Open it, look at the data and follow along. This file is intentionally saved in XLS format so that it can work with older versions of Excel.

The Need for Historgram

SNAGHTML18fca11Look at the data. It shows quantities of a product sold across different transactions. Simple analysis using Excel functions or Pivot table will show that the base summary:image

 

But does it show which quantity range was sold more than others?
There were 100 transactions. Did each transaction happen near 48? We can answer that question by looking at more statistical functions like Mode (most commonly occurring quantity, 31 in this case) or Standard Deviation (deviation from the mean, 2.27 in this case). But these are still numbers. We need to see it visually to understand it better.

That is what a histogram is all about. It is a sort of “historical” diagram which uses vertically arranged things (like mast of a ship) to simplify understanding of data.

The concept

Take the range (10 to 100 in this case) and divide it into smaller pieces (called bins). here we can do it from 1o to 20, 21 to 30 and so on. Now count how many quantities fall into each bin and plot a simple column chart from it. That is the idea.

Excel has two ways to do this for you: using Data Analysis add-in and using Pivot Table.

Using Data Analysis ToolPak

Go to Data menu – Data Analysis option. If you do not see the option, activate the Add-in.

Choose Histogram from the available options. A new dialog opens. Select relevant things in the dialog and click Ok.

image  image

  1. The data range.
  2. Inform Excel that the data has a header (Qty Sold)
  3. Specify where the output of the histogram should go
  4. Force it to draw a chart
  5. All Done! This is what you get …

image

Notice that the frequency of 37 and 46 bars is highest.

Sorted histogram shows the same chart and data but sorted in descending order of frequency.

image

This method helps us focus on the most frequent items more easily. If you reduce the gap between the bars, it looks like a traditional histogram.

image

In the next article, we will see how this can be done using Pivot Table features available in Excel 2013.

***

Queries | Comments | Suggestions | Wish list