15. Creating a Template

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 a project.


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.

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.

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