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.
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 > Control chart templates and then the appropriate template. In this case, the data is variable (money), so we choose the XmR Five Pack:
This will bring up the XmR Chart template:
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:
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.
QI Macros will create a new sheet (Sheet 1) with the formulas and links you need to automate your 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:
- Click on Sheet 1 (in this example)
- Edit-Copy the cells with the paste-link formulas in them.
- Switch to the control chart template.
- Click on cell A1
- Use Edit-Paste Special and click on the Formulas button to paste the link formulas into the worksheet.
- Similarly, use Edit-Paste Special again and select Formats to paste the date and number formats into these cells.
- 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.
- Right click on the template (e.g., Charges) and select "View Code":
- 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:
- The next time you click onto this sheet, the code will automatically autofilter the report to include the new data.
This will reveal the hidden code to autofilter this sheet:
Pretty slick huh?
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:
- First click on the autofilter arrow in B1 and select "All" to show all of the formulas.
- Select A3:B51 (or whatever is the last row)
- Choose Edit-Copy
- Then select A2 and choose Edit-Paste Special-Formulas.
- The chart will roll up one period.
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.
Other Charts Included in QI Macros for Excel