Automatic Control Charts

Do you routinely pull information from corporate databases and then use QI Macros to draw control charts? If so, there might be a better way using the control chart templates.

Instead of doing charts every month or week, here's how to link your data to the QI Macros control chart templates. Every control chart template has a place for up to 50 data points (or more as required) which create the chart.

Here's an example of the XbarR template. Using the data in the yellow shaded input area, this template provides the average and range control charts, histogram, normal probability plot, and scatter plot.

X bar R template to automate control charts

To automate the production of your charts, all you need to do is link your monthly data spreadsheet to one or more of these template input areas.

Adding a Control Chart Template To Your Workbook

The first step is to add a template to the same workbook that your data is in. Click on QI Macros, Lean Six Sigma templates, SPC, and the appropriate template. In this case, the data is variable (money), so we choose the XmR Five Pack:

xmr-five-pack-template-image

This will bring up the XmR Chart template:

XmR template to automate control charts

Now, just right click on the template to move or copy it into your data workbook:

Creating Useful Links

Let's suppose that every month you get a data dump that looks like this:

link input sheet to template to automate control charts

Using Excel, you can select the labels and data for your chart and use Paste Special to paste a link into another sheet (in this case the template input area).

This may work for you, however, Excel treats blanks as zeros which can be a problem. Also, Excel won't allow you to paste link and transpose data. If you have zeros or need to Paste Link and Transpose at the same time, we have created QI Macros tools to do both. Just select your data and click on Data Transformation-Paste Link (or paste link transpose if your data is horizontal).

Tip: Select more rows than currently exist to cover you for the next several reporting periods.

paste-link-image

QI Macros will create a new sheet (Sheet 1) with the formulas and links you need to automate your control charts:

use paste link command to automate control charts

Adding Links to Your Control Chart Template

Now all you have to do is add the "paste links" created earlier to this template. To do so, simply:

  1. Click on Sheet 1 (in this example)
  2. Edit-Copy the cells with the paste-link formulas in them.
  3. Switch to the control chart template.
  4. Click on cell A1
  5. Use Edit-Paste Special and click on the Formulas button to paste the link formulas into the worksheet.
  6. Similarly, use Edit-Paste Special again and select Formats to paste the date and number formats into these cells.
  7. If you have less than 50 data points, click on the autofilter arrow in cell B1 and select show non-blanks.

This will collapse the chart to show just the available data points.

If you want to, you can click on the chart and run stability analysis from the QI Macros Chart menu.

Repeat this process for every chart you produce from this data dump.

Here's the beauty of this process: next month, just copy and paste the data from the new data dump into the data sheet in this workbook. You'll have new charts!

Automating the Autofilter

Now, let's say that you have fewer than 50 data points and you're adding one a month. You don't want to go back into every control chart and Autofilter for non-blanks. Here's how to do it with hidden code.

  1. Right click on the template (e.g., Charges) and select "View Code":
  2.  

    This will reveal the hidden code to autofilter this sheet:

  3. The apostrophes in front of every line change the lines to comments. Simply delete the apostrophes (in this order) before End Sub, Selection.Autofilter, Range("B1), and Private Sub to activate the code. When you're done, it should look like this:
  4.  

  5. The next time you click onto this sheet, the code will automatically autofilter the report to include the new data.

Pretty slick huh?

Adding Data to the Charts

If you have more than 50 data points, you can copy the last row of control chart formulas and paste as many rows as you need. Then just change the charts to include the additional data points.

  1. Select and copy the last row of control chart formulas:
  2.  

  3. Paste as many rows as you need. (Hint: if you paste linked more than 50 data points, the formulas should be in cells A52:B52 and so on.) If not, you'll need to redo the paste links of the formulas.
  4. Click on the X chart and QI Macros Chart Menu-Add Rows to Control Chart. 
  5. Indicate the number of rows you just added.
  6. If you are adding more than 50 rows, you will need to change the number of rows for the process change to something larger than the total number of rows:
  7. Do the same for the R chart.

Rolling Your Monthly Reports

If you want to "roll" say a 24 month period, but your database gives you every month since time began, just copy and paste the formulas up a row every month:

  1. First click on the autofilter arrow in B1 and select "All" to show all of the formulas.
  2. Select A3:B51 (or whatever is the last row)
  3. Choose Edit-Copy
  4. Then select A2 and choose Edit-Paste Special-Formulas.
  5. The chart will roll up one period.

In Summary

It's possible to create a dashboard of measures that are driven off your monthly database downloads. Just use this month's download, the QI Macros Paste-link functions, and the QI Macros Lean Six Sigma control chart templates to build your dashboard.

Once it's built, you can paste new data downloads into it indefinitely.

Create these charts and diagrams in just seconds using QI Macros for Excel...
Try It Now!