Creating a Histogram in Excel

histogram in excel business slide

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, 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, 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

Comment Box is loading comments...

More Info

  • Download Today. Don’t take chances without understanding your data. Data drives business decisions. But how does this work? This introduction to Data Analysis Video shows you how to gather, summarize, and present data to management and your team. $69.00. Satisfaction guaranteed.

    Data Analysis Video

    Download Today. Don’t take chances without understanding your data. Data drives business decisions. But how does this work? This introduction to Data Analysis Video shows you how to gather, summarize, and present data to management and your team. $69.00. Satisfaction guaranteed.

  • A Run Chart displays the process performance over time. It is a line graph of data points plotted in chronological order. Learn more!

    Run Chart

    A Run Chart displays the process performance over time. It is a line graph of data points plotted in chronological order. Learn more!

  • See our article on regression, includes details, collecting the data, examples, roadmap and possible problems

    Regression

    See our article on regression, includes details, collecting the data, examples, roadmap and possible problems

  • Data analysis in excel discusses calculating averages, ranges, and standard deviation in  Microsoft Excel.

    Data analysis in excel

    Data analysis in excel discusses calculating averages, ranges, and standard deviation in Microsoft Excel.

  • Learn how to evaluate your continuous data and assure satisfactory inspection. Without conducting an MSA on your data set, you put your inspection data at risk.

    Continuous Data

    Learn how to evaluate your continuous data and assure satisfactory inspection. Without conducting an MSA on your data set, you put your inspection data at risk.

  • Process capability studies demonstrate the fit of your data to your specifications.  Machine process capability determines current and future defects.

    Process Capability Studies

    Process capability studies demonstrate the fit of your data to your specifications. Machine process capability determines current and future defects.

  • Learn how to apply Chi Square in practice, when to use it , how to insure results

    Chi Square

    Learn how to apply Chi Square in practice, when to use it , how to insure results


Quality Assurance Solutions
Robert Broughton
(805) 419-3344
USA
email
Enjoy this page? Please pay it forward. Here's how...

Would you prefer to share this page with others by linking to it?

  1. Click on the HTML link code below.
  2. Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.

All Products

Software, Videos, Manuals, On-Line Certifications

PDCA Complete

An Organizational Task Management System. Projects, Meetings, Audits & more

8D Manager

Corrective Action Software

TrainingKeeper Software

Plan and Track Training

Snap Sampling Plans!

AQL Inspection Software

QAS Business Slide Deck

450+ Editable Slides with support links

TRIZ Kit

Learn and Train TRIZ

ISO 9001:2015 QA Manual

Editable Template

ISO 9001:2015 QMS Kit

Templates, Guides, QA Manual, Audit Checklists

ISO 14001:2015 EMS Kit

EMS Manual, Procedures, Forms, Examples, Audits, Videos

On-Line Accredited Certifications

Six Sigma, Risk Management, SCRUM

All Products

Software, Videos, Manuals, On-Line Certifications