10. Templates & Multiple Worksheets

If you need several worksheets that will be used to pass their data to a Consolidation Sheet, and you want the worksheets to have a consistant structure, look and feel, then it may be worth your time to think about creating a Template. A Template is a special workbook that you can create and then use as a pattern in creating similar worksheets for several projects or jobs.

We began working on our template for a multi-location business example. We setup our Title Rows and Column Headings just as we would for a regular worksheet, changing Row Height and Column Width, where appropriate. And we used a Function to include the System Date in the Header area. We entered some data so that we could test our formulas. The most interesting formula was for the Average Unit Price on an item sold by the business, in which we calculated the Margin on the products to be sold.

We formatted Column Titles for a consistant look, applying Number Formats, with the Currency Style in the first and Total rows of dollar figures, and applied a Custom Format by adapting a Comma Style, with Blue format for Negative Numbers.

The key move for us was to Save the Worksheet as a Template in the 'Save As Type' field. Excel already knows that it has to save the file to our Template Folder.

We used selected worksheets to 'Drill' entries through three sheets, and then customized each sheet for a different location.

The best part is that using a Template to create multiple worksheets is a great way to expedite this kind of project, where we want to funnel data from several sheets into a Consolidated Worksheet with easy to create Three-Dimensional references to Sum up data across the sheets.

We worked on customizing a Clustered Cone Chart that reflected the consolidated data. We formatted the background color, and added a title using Word Art. We added a Text Box and an arrow to accentuate the most important part of the Chart.

Getting the Workbook ready for printing is also an important part of this project. By selecting all the worksheets, we were able to change margins and create a Custom Header and Custom Footer that enhanced all our worksheets.

In short, there are projects where Templates can multiply the usefulness of the way we use Excel.

Materials Covered: MS Excel - Project 6:
Creating Templates and Working with Multiple Worksheets and Workbooks, pp. 418-480

Case 5: Working with Excel Tables:

• End of Year Stationery Sales
• Follow the instructions to download your file, complete work on it, and then to upload it for grading.
• In your journal, write a short note letting me know what you learned from working with Charts in this case. Thanks.