Put Your Cards on the Table

Home Office Computing, Sept, 1999 by Helen Bradley

Collate information from Excel worksheets into a single data table for at-a-glance analysis

CAN YOU SPOT TRENDS HIDDEN IN A STACK OF purchase orders or receipts? If you use Microsoft Excel 97 to create receipts, invoices, or other business documents, you can export the data from your worksheets into a table that you can view and analyze using either Excel or a dedicated database program such as Microsoft Access--which can make reporting and business planning a lot easier.

The process is simpler than you'd expect, too. To begin, you'll need Excel's Template Wizard add-in. To see if it's installed, select Tools/Add-Ins and inspect the Add-Ins Available list. If "Template Wizard with Data Tracking" is present, select its check box and click OK.

If you don't see the add-in, install it from your Office 97 CD-ROM by selecting Setup, picking Microsoft Excel from the Options list, and then clicking the Change Option button. Select Add-Ins, the Change Option button, and then the check box beside "Template Wizard with Data Tracking." Click OK twice and continue with the install.

Once the add-in is installed, you can follow five simple steps to turn your current worksheet into a data template.

Step 1 Select Data/Template Wizard to start the add-in. The Step 1 of 5 dialog box should show the current spreadsheet as the name of the workbook from which to create the template. You can alter the destination template name if you like, but allow Excel to supply the path to its own template directory. Click Next to continue.

Step 2 In the second dialog, select Microsoft Excel Workbook as the database type. Alter the name and location of the database file if you wish, then click Next.

Step 3 In this dialog box, you'll need to specify each cell in your worksheet containing data you want to include in your database. Select the first cell text box and type a cell reference in it, or collapse the dialog box and select the cell by pointing.

For each cell, Excel provides a Field Name that you can alter if you wish. These are the column headings in your database (and can include spaces for two- or three-word headings). Repeat this process for every cell you want to add to your database and click Next.

Step 4 Choose your response to the prompt "Would you like to add information from existing workbooks to the database?" If you have workbooks with existing data, you'll probably want to add content from them to your new database. Otherwise, select "No, skip it" and click Next.

Step 5 Select Finish, and Excel will create your template and the database file.

Using Your Template From this point on, you can create worksheets based on the new template by selecting File/New, selecting the template, and clicking OK. You can edit such a worksheet as you would any Excel file.

When you save your workbook, Excel will ask if you want to add the details from the template to your database file. To do this, select the "Create a new record" check box and click OK. If you alter a workbook that has been added to the database, you'll get an additional choice when you save--"Update the existing record"; choose it to modify the old database record with the changes you've made.

To see the information in the data file, you can open it as you would any other Excel file. It's a simple worksheet containing one row for each worksheet you've created using the template, with all the information that you selected in Step 3 of the Template Wizard. You can format this worksheet and add formulas to it to analyze the data.

If you'd like more information on using the Template Wizard, Microsoft's Knowledge Base contains a good summary article. You can get it via e-mail (as you can any Knowledge Base article) by sending a message to mshelp@ microsoft.com and including the article number (in this case, Q148314) in the subject line.

RELATED ARTICLE: Other Database Formats

The instructions in this article save your worksheet data into a new Microsoft Excel workbook. However, you can create databases in other formats if you've installed the Data Access Objects for Visual Basic. If these aren't installed, launch the Setup program on your original Office 97 CD, select Data Access from the Options list, and select Change Option. If the "Data Access Objects for Visual Basic" check box is blank, select it, then click OK and Continue to install the objects. If the check box is selected, the Data Access Objects should be installed on your system, so exit Setup.

Then in Step 2 of the Template Wizard, you can choose a different database format to store the information from your template. Of course, you'll need a database program capable of reading this file to make use of it.