How to Copy and Open an Excel Template

Many newcomers to LIMS find the use and manipulation of Excel template files less than intuitive. This overview should alleviate any reservations and help you put your own Excel templates to use.

Most Microsoft Office applications including Excel support templates from which you can quickly create new documents. For simplicity, we will use here – and you should continue to use – Excel templates in Excel 97-2003 format. Such files have an xlt file extension to identify the file.

Before we start, it is advantageous to ensure Windows is configured to show a file’s extension (e.g. xls, xlt, etc.). If you do not see file extensions following file names in Windows Explorer, use Tools | Folder Options, select the View tab then disable the “Hide extensions for known file types” option and click the OK button. With file extensions visible in Windows Explorer, Excel will also include file extensions in its title bar.

Simply put, a template file is used to create new documents which are derived from the template. To see this process at work, open Windows Explorer, navigate to the LIMS example templates in folder C:\LIMS\Examples\Excel Export Templates then double-click any file with an xlt file extension. Your double-click action will open Excel if it is not already running, then instruct Excel to create a new workbook in memory only (i.e. not yet a file) from the selected template.

It is very important to note that double-clicking did not open the actual template file. As evidence, note the name of the file in Excel’s title bar. For example, if you double-click on file CofA Example.xlt in Windows Explorer, Excel will list CofA Example1 without an extension in its title bar. It is Excel’s practice to append a sequence number to the file name when creating a new workbook from a template. Double[1]clicking a second time will yield a second workbook titled CofA Example2. Note that the lack of an extension (if Windows file extensions are enabled) indicates the workbook is only in memory and has not yet been saved as a file.

The easiest method to create your own template is to copy an LIMS example template. Once you have decided which template you need, simply use Windows Explorer’s features to make a copy of the xlt file. For example, select the template file, use Ctrl+C to copy, then Ctrl+V to paste. Now rename your new template file accordingly.

To make changes to a template file you must open the xlt file (as we’ve already seen, double-clicking the file in Windows Explorer does not open the file; rather it creates a new workbook from the file). Open a template file by right-clicking in Windows Explorer and selecting Open from the shortcut menu as shown below. Note that double-clicking a template file is the equivalent of selecting New from the right-click shortcut menu.

You can also open a template file directly from within Excel. Whenever you intend to make a change to a template file, ensure you have opened the actual template by inspecting Excel’s title bar. If you have opened the template file and Windows file extensions are enabled, you will see the xlt file extension in the title bar.

One final note about working with Excel templates for use with LIMS. An Excel workbook created by exporting a report’s data to a template should not itself be saved as a template. The resulting workbook will include LIMS data and the named ranges automatically created for the data. Attempting to export to such a workbook saved as a template will result in numerous errors as the template’s macro attempts to recreate named ranges that already exist. Excel templates used with LIMS should lack any LIMS data and their corresponding named ranges.