Running and Updating Histograms Using QI Macros

Histograms are simply bar charts that show the distribution of your data using the number of times your data points fall into each of the bars on the histogram. When you add the upper and lower specification limits, it’s easy to see how your data fits your customer's requirements and what improvements might be necessary.

QI Macros histograms calculate key indicators of process capability including Cp, Cpk, Pp and Ppk. See our formulas page for more information about these indexes.

Step by Step instructions for running a Histogram in QI Macros SPC Software

If you don't have any data of your own, click on QI Macros menu and then QI Macros Help on the far right. Click Open Sample Data to access files for each chart. Open histogram.xlsx.

sample data

To create a histogram in QI Macros follow these steps:

  1. Click and drag over your data to select it.
  2. Click on QI Macros Menu and select Histograms & Capability and then Histogram. Tip: Do NOT sort your data before running a histogram or the calculations will be incorrect:
  3. excel histogram with cp cpk on QI Macros menu

  4. QI Macros will prompt you for upper and lower spec limits. Use the default or input your spec limits. For one-sided histograms, select Cancel if there is no USL or LSL. In this example, we input 346 for the USL and 200 for the LSL:

    set specification limits for histogram in Excel
  5. QI Macros will perform all of the calculations and draw the histogram for you:

    Histogram created by QI Macros for Excel

QI Macros Allows You to Update or Change a Histogram Once You Create It

Move arrows or text boxes:
  • Arrows: Click on each arrow and drag it to the appropriate position. To extend an arrow, click on it, then click on the handle at either end and extend the arrow.
  • Text: Click on each text box and drag it to sit on top or beside its corresponding arrow.

Revise the process capability calculations:
Switch to the data calculations sheet. You can change the USL and/or LSL by typing over the current values in the cells provided. Excel will recalculate Cp, Cpk and Pp, Ppk and update the contents of the text boxes on the chart.

Change the histogram graph: change the number of bars, class width and beginning point of the x axis.

histogram fields that can be changed

You can also change:

  • The method used to calculate the estimator. Pooled Standard Deviation (1) is the default, but you can switch to Sbar (2) or Rbar (3) by scrolling to the right and changing the value in cell S2.
  • The calculations used for Cp and Cpk in cell S3.

Select Pooled SD, Sbar or Rbar for Estimator

Notes about changing the # of bars and class width

Note that you can change the number of bars used on the chart by changing either the number of bars field OR the class width field in the Histdata sheet. if you want an exact number of bars, use the class width field. Change the class width in small increments until you get the number of bars desired.

If you change the number of bars, you may need to copy and paste the formulas down to new cells:

add data to histogram in Excel

Then go to the Histogram chart and see if it is still centered on the page. In this example, we changed the number of bars from 10 to 20. After the change the chart looks like this:

histogram after new data was added

Next, we need to update the Source Data the chart is graphing. Right click on the chart and choose "Select Data."

change source data plotted by histogram in Excel

Excel will open a window with the source data used for each item on the chart. To update the source data, click on the name of the series and then Edit.

data series tab to update source data plotted by histogram in Excel

In step 9, we copied formulas down to row 44 before we started getting bars with zero values, so we need to change all references from row 26 to row 48 in the series window.

Change all of the values for the Count series, then click on Count 2 and Distribution and change the values for these series as well.

The updated chart should look like this:

histogram after source data is updated in Excel

To add data, switch to the histdata sheet and add the new data:

add data to histogram created by QI Macros for Excel

QI Macros Calculations

To view formulas used in QI Macros, see our formulas page.

To view the calculations made to create the histogram, take a look at the Histdata sheet created when a macro is run:

Learn More...

Process Capability Analysis Quick Reference Card

Create these charts and diagrams in just seconds using QI Macros for Excel...