Use the Excel Add-in Control Charts

The Control Charts feature in the Excel Add-in helps laboratories analyze and visualize QC data directly from AssayNet LIMS. This tool generates control charts for Reference Materials and x–y scatter plots for QC analyses, making it easier to track and evaluate your quality control program’s performance.

Using the Control Charts option, you can download results for any QC type configured in AssayNet LIMS, including replicates and certified reference materials. The resulting data tables include all critical details needed for thorough analysis. For CRMs, the tables display the configured control limits. For all QC types, the data includes measured results, associated Run IDs, and any comments recorded when a QC failed.

The generated control charts not only plot the data visually but also provide summaries of essential metrics such as the number of failures, the average measured value, and the accepted target value. Additionally, the Add-in creates a summary sheet at the beginning of the workbook, containing key information from all retrieved QC results for easy review.

QC data can be retrieved for any selected date range, and the system will automatically pull analyte schemes for all elements marked for QC checking.

To improve workflow efficiency, you can create and save templates with all the analyte schemes you need. By using these templates, you won’t need to reselect schemes each time you run control charts. Instead, simply load a saved template and select the required date range to quickly perform your analysis. You can maintain multiple templates to support different QC analyses or reporting needs.

Note: To ensure a scheme is available for selection, both the Use for QC and Check for QC options must be enabled in the Analyte Scheme configuration. For more information, see Configure Analyte Schemes.

Note: If you make any changes to the configuration of a Reference Material (CRM), you must update the QC data to ensure those changes are reflected correctly in the control chart tables. For more information, see Add and Set up Reference Material.

This activity describes the different options available in the Control Charts menu and guides you through the process of creating, saving, and using templates to streamline your QC analysis in Microsoft Excel.

This activity assumes you have the Excel Add-in installed. For more information, see Install the LIMS Excel Add-in.

Activity Steps:

Open a new Microsoft Excel file.
An existing Excel file can also be used, but any existing data might be overwritten.
Right-click and select LIMS » Control Charts. Alternatively, click the Add-in tab in the Excel ribbon and then click LIMS » Control Charts.

If you have not yet connected to your operational database, a Microsoft Excel information message window displays Lost Connection to LIMS.
Click OK to reconnect. Follow Connect to the Excel Add-in to connect.

The Draw Charts screen displays.
Complete the following fields as required:
- QC Type—Select a QC type from all available types configured in AssayNet LIMS. For more information, see QC/QA.Note: To configure QC for client sample duplicates, select the Replicate option. For preparation duplicate QC, select the Sample Splits option.
- Aliquot Type—Select the type of QC that has been used to categorize the nature of the standard for each one. Each type is created by the user and assigned to each QC. The Aliquot Type options change based on the selected QC Type in the previous step.
- Aliquot—Select the QC name created in the LIMS, based on the QC type and aliquot type chosen in the previous steps. Some QC and Aliquot types may be limited to a single option; for example, the Replicate QC type includes only Replicates as the available Aliquot.
- Analyte Scheme—Select the analyte scheme that has been configured to display QC results. For more information, see Configure Analyte Schemes. The Analyte Scheme list displays all available schemes for all configured methods. When selecting, ensure you choose the correct scheme corresponding to the correct method.
To apply the analyte scheme, click the blue arrow pointing to the right.
Repeat steps 4-5 to add more analyte schemes if required.
To filter the QC results, complete the following fields:
- From Date
  1. Enter the From Date in the format dd-mmm-yy.
  2. Alternatively, click the calendar icon to the right to select the From Date.
- To Date
  1. Enter the To Date in the format dd-mmm-yy.
  2. Alternatively, click the calendar icon to the right to select the To Date.
- Client—Select a client to show only the QC results obtained for that client. You can leave this as All to view all clients.
- Project—Select a project to show only the QC results for that project and client. You can leave this as All to view all projects for the selected client.
- Crew—Select a crew to show only the QC results obtained by that crew. Crews can be assigned for each sample batch when the batch is logged.
To modify how the data is displayed and handled, complete the following fields:
1. In the fields below the Crew field:
  - Save as New File—If selected, AssayNet LIMS prompts you to save the control charts as a new Microsoft Excel file before generating data, rather than generating the data in the open file.
  - Hide Data Table—If selected, only the control chart or charts are generated. The data table from which the control chart is created is not displayed.
  - Single Worksheet—If selected, the control chart and table (or control charts and tables) are generated in a single sheet for each QC selected.
2. In the y-Axis Options category, control how the Minimum and Maximum values of the Y-axis (vertical axis) are determined in the control chart:
  - Auto—The system automatically adjusts the axis range based on the data.
  - Limits—The axis range is set using the configured QC limits (e.g., control limits defined in the analyte scheme).
  - Fixed—Allows manual entry of specific minimum and maximum values for the Y-axis. Enter values in the text boxes provided to the right of the fields.
3. In the x-Axis Options category, select whether to Show Dates on Axis. If selected, the chart displays actual dates on the X-axis (based on when samples were run).
4. In the Control Lines category, define how many standard deviations above and below the mean the high and low control lines are displayed.
  - High—Adjusts the number of standard deviations used for the upper control limit. The default and maximum value is 3.
  - Low—Adjusts the number of standard deviations used for the lower control limit. The default and maximum value is 3.
    Note: You can increase or decrease the High and Low with the arrow buttons, or enter the value directly in the box to right of the fields.
Click OK to generate the control charts.
The Progress screen displays.
An AssayNet warning message appears, (if the Save as New File option is not checked) indicating that any existing data will be removed.
Click Yes to continue.
To save a template:
1. Click Save in the Template field.
  The New Template screen displays.
2. Enter the Template Name.
3. Click OK.
  Note: A saved template cannot be overwritten, as template names must be unique. To modify the analyte scheme list in an existing template, you must save it under a new template name.
  Note: Only the analyte schemes are saved in the templates. You must still complete the remaining fields detailed in steps 7 and 8.
To load a saved template:
1. Click Load in the Template field.
  The QC Templates screen displays.
2. Select the Template Name.
3. Click OK.
  The Analyte Scheme list saved for the selected template loads.
4. Follow steps 7, 8 and 9 to create the control charts.