Quick Data Analysis with Excel

Exporting LIMS data to Excel for further analysis is a technique many LIMS users employ. LIMS’ integrated Excel interface can make the job easy. Understanding how data can be exported to Excel will help simplify your data analysis tasks. A recent technical support email included a scenario that offers a good example.

One LIMS user had not yet explored the capabilities of the Excel interface. She knew it existed and now she had a good reason to learn how it worked. Her boss had given her the task of analyzing specific analytical results for the past year. Jumping right in, she used the Sample Summary report and queried a year’s worth of samples. In the report’s print preview window she clicked the “Analyze It with MS Excel” toolbar button (see below) to export the data to Excel but she was discouraged by what she found in Excel.

“Analyze It with MS Excel” is a standard feature in Microsoft Office applications. We have preserved this feature within LIMS’ print preview window because it does provide some utility in certain situations. When you use this feature, all of the report’s data and field or control names are exported in a format that attempts to mimic the appearance of the LIMS report. Try an example yourself and you will see that, in this format, the data is not particularly useful for further analysis. Extracting all of the results for the required analyses and assembling them in columns for statistical analysis would require significant cutting and pasting. With only a few samples, this format may suffice. But with a year’s worth of data it was not practical.

We suggested she export the data to an Excel template from the print preview window using File | Export to MS Excel Template or the equivalent “Export to MS Excel Template” toolbar button (see the example, below). But what template should she use?

The LIMS Export Template is the perfect solution. This template can be found in folder C:\ LIMS\Examples\Excel Export Templates on all LIMS workstations.

Most export templates are designed to receive a specific report’s data and include macros to manipulate the data after receipt. However, the LIMS Export Template has a single LIMSData sheet with empty macros so it can be used with any LIMS report. Unlike the “Analyze It with MS Excel” feature, the “Export to MS Excel Template” software does not attempt to recreate the appearance of the report.

Rather, the main report’s underlying recordset is exported to the template’s LIMSData sheet. The recordset may contain more data than the report lists. However, when a report is constructed with subreports, each with its own recordset, only the main report’s recordset is exported to Excel. Such is the case with the Sample Summary report. When you export a Sample Summary report, the LIMSData worksheet will contain all of the characteristics for each sample but the analysis results are not exported.

A template can in turn query the LIMS for the additional data but that was not the quickest solution.

Since the results for only a few analyses were required over the one year span, the simple solution was to use the Analyte Comparison report. Selecting the analyses of interest, querying a year’s worth of data, previewing then exporting to the LIMS Export Template produced all of the required analytical results in an easy to manipulate tabular layout.

Whenever you need to quickly export LIMS data for any purpose, remember the “Export to MS Excel Template” toolbar button and the generic LIMS Export Template. Use this combination with any MSCLIMS report and you will have a wealth of information at your fingertips.