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.

This Data Analysis Video teaches you the basic tools for understanding, summarizing, and making future predictions with your collected data. Includes MS Excel templates.

Data Analysis Video

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 Exel Office 365 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 

Click the file tab (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.

This Data Analysis Video teaches you the basic tools for understanding, summarizing, and making future predictions with your collected data. Includes MS Excel templates.

Data Analysis Video

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:

This Data Analysis Video teaches you the basic tools for understanding, summarizing, and making future predictions with your collected data. Includes MS Excel templates.

Data Analysis Video





Quality Assurance Solutions
Robert Broughton
(805) 419-3344
USA
email

Unique QA Products

All Products

Software, Videos, Manuals, Training Material

8D Manager

Corrective Action Software

Snap Sampling Plans!

AQL Inspection Software

TrainingKeeper Software

Plan and Track Training

StreamLiner Software

Lean and Continuous Improvement



Statistical Process Control

Training Video

ISO 9001:2015 QA Manual

Editable Template

ISO 9001 Calibration Manual

Editable Template

ISO 9001:2015 QMS Kit

Templates, Guides, QA Manual, Audit Checklists

QA Training Guides

ISO 9001, Kaizen, Benchmarking, 5S and more

All Products

Software, Videos, Manuals, Training Material


Please Recommend Us!

submit to reddit