XmR Chart Dashboard in Excel

The purpose of an XmR Chart dashboard is to evaluate stability of processes using variable data--time, cost, length, weight when your sample size is one.

Here is an example of the XmR Chart dashboard in QI Macros for Excel.

The XmR Chart dashboard has a few main components:

  • Data Entry Sheet
    Just type data into the yellow/white input areas on the data sheet:
  • XmR Dashboard Data Sheet

  • Select the XmR AverageR tab:
  • XmR AverageR Dashboard Tab

Creating the XmR Chart Dashboard

  1. Input measurement titles, specification limits and data into the data sheet as shown above.
  2. Click on the tab you would like to create a dashboard from - XmR AverageR, Rolling, MedianR or Run Chart:
  3. XmR Dashboard Tabs

  4. Use the Next/Prev buttons to look through the various charts to make sure they are what you want:
  5. Previous Next Button

  6. When you have the right data and charts, click on the CREATE DASHBOARD button. This will create new worksheets for each chart as well as the dashboard showing just the X Charts:
  7. XmR Dashboard Chart Output

  8. Delete the unused worksheets, as they are no longer needed.
  9. You can rearrange the charts on the dashboard by clicking and dragging them around.
  10. You can copy other charts from the new worksheets (e.g., Histograms) and paste them onto the dashboard.
  11. You can then continue to add data to the data sheet and update charts by clicking the "Refresh Charts," "Refresh Charts w Stability," or "Refresh Charts w Rules" buttons on the data sheet. Refresh with Stability will run QI Macros stability analysis on all control charts. Refresh Charts with Rules will run QI Macros stability analysis on all control charts and show specific rules violated.
  12. If you save this workbook as an Excel "Shared" workbook, you could have multiple people updating various measurements. Just refresh the charts every so often. Anyone can then look at the dashboard.

I think you'll find this a very powerful feature of QI Macros. Creating dashboards of key measures quickly and easily. And keeping them up-to-date with a click of a button.


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.


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

NOTE: Conditional Formatting is applied to Cp Cpk and Pp Ppk metrics when values are as follows:

Red: Under 1.00 (Not capable)
Yellow: 1.00 - 1.33 (Will not meet most manufacturing specifications)
White: 1.34 - 1.66 (Capable)
Green: 1.67 and up (Approaching Five Sigma to Six Sigma - Exceptional)

conditional-formatting-histogram-dashboard


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. How Many Rows of Data

    How Many Rows of Data Prompt

  7. Next, go to your Dashboard tab and click on the chart you added data to - perform same steps listed in step 3:
  8. How Many Rows of Data

    How Many Rows of 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!

Learn More...


To create a X M R Dashboard using QI Macros...
Try It Now!