Products

Consulting

Tools

Management

Systems

[?] Subscribe To This Site

Creating a Histogram in Excel

Here are instructions for making a histogram in Excel. An excel histogram is not the easiest function in Excel. I don't know why Microsoft made it difficult. Histograms are extremely useful in quality assurance as they create a picture of your data.

Introduction to Data Analysis

Training Video (Created by Quality Assurance Solutions)

Price $49.95

Data Analysis is key to making business decisions about your process or product. To improve the process or product you have to measure it. Once you have the measurements, you need to quickly interpret the data.

This hour long video will show you how to present the data to management and others. Normally, management is not interested in the raw data. Business decisions are based on the summarized data or graphs of the data.

This course provides the basic tools for understanding, summarizing, creating graphs, and making future yield predictions with your collected data.

You will learn about

the types of data
basic statistics such as averages and variation
creating and interpreting histograms
normal distribution
process capability
predicting yields
the relationship between process improvement and data
pareto charts
data analysis in excel

In addition you will learn how to use Microsoft Excel to conduct the data analysis. A final spreadsheet is provided for your review.

Your Satisfaction is Guaranteed.

Within 30 days, if you are not satisfied with this training video, I will refund your money.

Price $49.95

Histograms are necessary for continuous improvement measurements. They show your data distrubution before and after the improvement.

Histograms provide one method to document your continuous improvement efforts.

Before creating a histogram in excel, be sure to read this discussion on data analysis.

Below is partial of the data set that I'm using for the histogram.

In the discussion below, we use MS Excel 2007 version. Earlier versions can also be used.

Before you make a histogram, you must load the analysis tool pack.

The Analysis Tool Pack.

Open Excel 2007

Click the office button (top left)

Click Excel options

Click Add-ins

Click Go…

Check Mark Analysis ToolPack

Click OK

Excel will now install the tool pack.

Calculate the Interval

To create a Histogram in Excel, first figure out the interval. See below for the recommended interval. The interval is the number of columns of data that you want for the histogram.

Take the range and divide by your selected interval. Round that number to a number that is easy to work with. For example if you calculated an interval of 1.4234 use 1.5.

For my data set, I chose .5 as the interval.

Create the Bins

In a blank work space in excel, record your intervals from low to high. You can start with the minimum or below the minimum of your data set.

In my case I start with 18.5 which is just below the minimum of the data set.

Add you interval to minimum and then to each adjacent cell. So my cells read 18.5, 19, 19,5, 20....23.5.

This creates the Bins.

Create the Histogram in Excel

Select an empty cell where there are no filled in cells around it.

Click Data Menu

Click Data Analysis on the Analysis tab

Select Histogram

Input Range – Select your entire data set

Bin Range – Select the cells that created your bins.

Output Range - Select a cell where you want the data chart to go. Be sure there is no other data surrounding this spot as Excel may want to overwrite that data.

Check Mark the Chart Output

Click Ok

Here is the histogram in excel:

Data Analysis
Click here for basic data analysis in Excel

Data Analysis Explained
Discusses key data analysis statistics

Histogram Examples
See here for histogram examples and continuous improvement discussion

Continuous Improvement Tools
List of Continuous Improvement Tools

Quality Assurance Solutions
Robert Broughton
(805) 200-7979
USA
email