Control Chart Dashboards in Excel

QI Macros Dashboards Can be Created and Updated in Seconds

Do you spend too much time?

  • Updating existing charts every month with new data?
  • Organizing multiple control charts onto a single page?
  • Creating control chart dashboards for management?

Input Data Once and Update Charts With the Click of a Button

dashboard input sheet

arrow

example of control chart dashboard in Excel

Every two weeks I have to update 600 control charts for more than 30 hospitals in our system. I used to spend three days updating these charts. Once we created the charts using QI Macros Control Chart Dashboards, and utilized automation including stability analysis, we reduced the time it takes to update the charts to just three hours. QI Macros Control Chart Dashboards save me valuable time that I can use for other important projects. Thanks for making this process quicker and easier.

- Mary Ayuso
Adventist Health System


Here's How to Create a Control Chart Dashboard using QI Macros:

View Control Chart Dashboard Tutorial (6 minutes) or follow these steps:

1. Click on the QI Macros menu, Control Chart Templates and open one of the four available dashboards:

control-chart-templates-menu

Each dashboard contains several tabs:

c, np, p, u Dashboard Template
C Chart | NP Chart | P Chart | U Chart | Run Chart

cnppu dashboard tabs

Levey Jennings Dashboard Template
Levey Jennings | Levey Jennings Percent | Levey Jennings Fixed Standard Deviation | Levey Jennings Target

levey jennings dashboard tabs

XmR Dashboard Template
XmR AverageR | XmR Rolling | XmR Scrolling | XmR MedianR | Run Chart

xmr dashboard tabs

XbarR Dashboard Template
XbarR Chart | XbarR Skyline | Precontrol Xbar

x-bar-r dashboard tabs

2. Click on the data input sheet and input or cut and paste data into the sheet:

dashboard input

 

Tip - Chart Titles: Chart titles are used to name worksheet tabs when the dashboard is created. Make these names unique and limit them to 31 characters.

 

Tip - Number of Charts: Input data for all of the desired charts before you create the dashboard.

 

3. Input spec limits or use the defaults - XmR and XbarR Dashboard only.

Rows 1 and 2 of the XmR and XbarR dashboard data sheets contain input cells for Upper and Lower Spec Limits. Input your spec limits here or use the defaults. Note: LSL and USL defaults are estimated as the average +/- 3*stdev (this differs from the XmR Five Pack and XbarR Six Pack templates which use Sigma Estimator).

spec limits on Variable chart input sheets

4. Now click on the worksheet tab for the desired chart. You will see a chart for the first data set:

xmr dashboard output

Click on the previous and next arrows to view charts for each data set. Click on the Create Dashboard icon to create a dashboard with each chart.
prev-next-dashboard-buttons create-dashboard-button

Once you click on Create Dashboards, a macro will run for several seconds and a new Dashboard sheet with all charts will be created:

control chart dashboard excel

 

Tip - Custom Chart Formats: QI Macros dashboards are presentation ready. However, if you want to customize chart formats, save time using QI Macros "Remember Format and Apply Format" features. Just customize one chart, then select Remember Format and then Apply to All. Learn more...


Update Existing Charts

To add new data to your charts, go to the data input sheet and add your new data.

Then select the Refresh Charts or Refresh with Stability icon to update the charts and re-run stability analysis:

dashboard-buttons


Add new charts to an existing dashboard

You can add new charts to an existing dashboard manually:

  1. Add new data to Data Sheet.
  2. Select the sheet containing the type of chart you want to add.
  3. Use the Next/Previous buttons to get to the new chart you want to add.
  4. Right Click on the Sheet name and choose Move/Copy (Check the Create a Copy box). Click OK.
  5. Right Click on the new Chart and choose "Copy".
  6. Click on the Dashboard Sheet, Click on an empty cell and choose "Paste" to copy the chart into the dashboard.
  7. Reposition the new chart on the dashboard.

The July 2018 release of QI Macros adds a new button that will do steps 3-6 for you.

  1. Add new data to Data Sheet.
  2. Select the sheet containing the type of chart you want to add.
  3. Use the Next/Previous buttons to get to the new chart you want to add.
  4. Click on the "ADD THIS CHART TO DASHBOARD" button:
  5. add-chart-to-dashboard

  6. Reposition the new chart on the dashboard.

You can also use this feature to create custom dashboards containing more than one kind of chart (e.g., c Charts with p Charts and u Charts in the cpu Dashboard).


Create a Histogram Dashboard:

  1.  If you would like to create a Histogram Dashboard, based upon your data set, select the sheet containing the type of chart dashboard you created.
  2. Select the Previous button to get to the very first chart in your data set.
  3. Next, select the "Create Histogram Dashboard" button:
  4. create-histogram-dashboard-button

Change the Y-Axis Multiplier in p and u Chart Dashboards:

  • Specific to the p and u Chart Dashboard, the Multiplier is located to the right of your chart, this functionality allows you to change the multiplier. This automatically changes the y-axis value (e.g. 1, 10, 100, etc.):

y-axis-multiplier


Need to Add More than 100 Rows of Data?

Dashboards only provide up to 100 rows of data calculations. If you have more than 100 rows, you will need to manually extend the formulas and use our "Add Data" functionality - see instructions below:

  1. For any charts requiring more than 100 rows of data, go into each control chart tab created after your Dashboard was developed. Next, unfilter your data by selecting the Filter button > "Select All" > "OK":
  2. dashboard expand calculations

  3. Select and extend the formulas in row 101 to the number of additional rows you need:
  4. expand calculations down

  5. Click on your chart in that tab, select "Add Data" from the QIMacros Chart section, and input how many rows you added:
  6. add data to charts

    add data prompt

  7. Next, go to your Dashboard tab and click on the chart you added data to and repeat the same process listed in step 3:
  8. add data to charts

    add data prompt

  9. Then, go to the Data Sheet tab and select either the "Refresh Charts" or "Refresh Charts w Stability" buttons:
  10. refresh charts button Dashboard Refresh with Stability Analysis Button

  11. Your Dashboard will now be updated to include your extra data!

"Reference Invalid"

If you receive a "Reference Invalid" message with a Control Chart Dashboard opened, this is because AutoSave is enabled in your Excel. Note that it is not possible to use AutoSave with a macro-enabled workbook.

To no longer receive this message, disable AutoSave when working with a macro-enabled workbook, such as our Control Chart Dashboard.


Learn More...


affordable

Affordable

  • Only $299 USD - less with quantity discounts
  • No annual subscription fees
  • Free technical support

easy to use

Easy to Use

  • Works right in Excel
  • Create a chart in seconds
  • Easy to customize & share charts

proven and trusted

Proven and Trusted

  • More than 100,000 users
  • In More than 80 countries
  • Five Star CNET Rating - Virus free