Spiga

Understanding Word Templates

by Susan Daffron

Every document has a template attached to it. If you don't specifically select one, it starts off using the Normal template. Every template has styles stored in them, whether you use them or not. Styles you create can also be stored in the template as well. The template also stores page layout information and text for a certain type of document.

Unlike some desktop publishing applications, in Word, if you change a template, your changes are not reflected in old documents that use the template, unless you reapply the new version of the template to the document. Even if you have a lot of documents that are all based on the same template, they may not look anything like one another. Although the document is based on a certain template, the document matches the template as it was at the time the document was created, which may not be what the template looks like now. It's important to realize that if you change the template and then create new documents, older documents won't necessarily match the new documents.

 

Understand How Templates Work

To understand how templates work, you need to understand where they fall in the Word architecture. If you don't understand Word's view of the document world, it's easy to lose track of where your settings are stored.

 

According to Microsoft, Word has a "layered" architecture. The document is the top layer. Underneath it is the Word application itself. Settings you add to the top layer supersedes the settings below. Here's a list of the layers from the bottom to the top:

  • Word: The Word application itself is the bottom layer. It contains all the built-in commands and functions. The program controls basic function such as right-justifying a paragraph.
  • Global: The next layer is the Global layer. This layer includes global templates, add-ins, and the Normal template. This layer also includes macros, AutoText entries, and toolbars. For example, if you add a macro to the Normal template that has the same name as a built-in Word command, Word runs your command instead of the built-in one.
  • Custom Templates: Your templates live above the Global layer. So the styles and other settings you put in your templates override the settings stored in the Normal and global templates. For example, if you set up style called Heading 1, your settings override the Heading 1 style in the Normal template.
  • Document: The top layer is the document itself. The settings you add to the document override the settings in any templates. If you reformat a Heading 1 paragraph by adding local formatting, those settings override the settings stored in the Heading 1 style in either the Normal template or your own templates.
If you don't create your own templates or styles, Word gets all its document formatting information from the document (the top layer). Any other default settings come from the Normal template or the built-in Word settings.

 

If you have set up your own styles in a template, Word looks for local formatting settings in the document, then looks at the Custom Template layer for your style settings. Then it looks at the Global layer and finally the Word layer to find other settings to construct the document.

Create New Templates

If you create a lot of documents, it makes sense to start creating templates that contain styles geared toward particular types of document. Although many people just add more and more styles to the Normal template, doing this can get very confusing very quickly. When you set up templates for specific document types, you don't have to scroll through thousands of irrelevant styles to find the one you need.

 

You can create a template in two ways. You can create a new file with a .dot extension or base it on an existing file and saving it with a .dot extension. To create a template from scratch, choose File, New and change the radio button in the Create New box from Document to Template. You can base the new template on an existing template or on a Blank Document (the Normal template). You add styles, formatting, macros, toolbars, and text just as you would to any other document. Then you save the file as a template with a *.dot file extension.

 

To save a file as a template. Open it and choose File, Save As. Change the Save As Type drop down-box to Document Template (*.DOT). When you switch the file type to a template, Word automatically switches to its default template directory. If you save the file there, it will be available in the list the next time you do a File, New.

 

When you create a template from an existing document, Word leaves the text in there too. So you may want to delete the text that is specific to the original document, so that just the styles and layout information remain.

Attach Templates

At some point, you may want to reformat the styles in a existing documents to styles you've created in a new template. This process is known as "attaching" a template to a document.

To attach a template, you select File, Templates and add a check mark to the Automatically Update Document Styles option. When you click the OK button, Word overwrites the style formatting of styles in the document that have the same name as the styles in the new template you're attaching. All the matching styles change to the new formatting. Any new styles in the new template are added to the style list. Text formatted with styles from the old template that don't match styles in the new template retain the old style. If you want to make sure that everything is reformatted to the new settings, make sure all the text is formatted with style names that match across the two templates.

 

If you want to retain certain styles from the old template, make sure you rename any matching styles you want to preserve. Choose Format, Style, click Modify, and type a new name.

 

Attaching a template only changes the styles in a document; it does not change other elements such as margins, page size, headers, or footers. Because margins, headers, footers and so on are document-level settings they supersede template-level settings. When you attach a new template, because the page settings are stored at the document level, Word can't overwrite them with settings stored at the template level.

 

Because document settings cannot be overridden, in this situation you must bring the document you want to change into the template. This way, you end up using the document settings stored in the template. You create a new document based on your template and then choose File, Insert. The new document contains the document-level page elements as well as the styles stored in the template.

Copy Styles Between Templates

You can copy styles from existing documents or templates to other documents or templates. To copy styles from one document or template to another:

  1. Choose File, Templates.
  2. Click Organizer and select the Styles tab.
  3. You may need to close the document or template on one side or the other and open the desired document or template. Be sure to check that the names are correct before you start copying.
  4. Choose a style from the Styles in box. When you click a style name, you see a description.
  5. Click the Copy button. The styles are copied to the document or template.

Copying styles can be useful if you set up a nifty new style, but forgot to add it to the template. Just open the document with the style and copy it to the desired template.

Excel Array Formulas

What are Array Formulas?

Excel Array formulas are very powerful and useful formulas that allow more complex calculations than standard formulas. The "Help" in Excel defines them as below:

"An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments."

Array Formula Rules:

Before we show some examples of array formulas it is important to know 4 fundamental rules.

  • Each argument within an array must have the same amount of rows and columns.
  • You must enter an array by pushing Ctrl+Shift+Enter.
  • You cannot add the {} (braces) that surround an array yourself, pushing Ctrl+Shift+Enter will do this for you.
  • You cannot use an array formula on an entire column.

Pet Shop Example:

Suppose you have 5 Columns of data each with 200 rows.
Column A is used to keep track of the sex of each dog sold i.e. Male or Female
Column B is used to keep track of the breed of the dogs sold.
Column C is used to keep track of the age of the dogs sold.
Column D is used to keep track whether the dog is sterilized or not i.e. Yes or No
Column E is used to keep track of the cost of the dog sold.

  • To count the number of male Poodles sold:
    =SUM(($A$2:$A$200="Male")*($B$2:$B$200="Poodle"))
  • To count the number of male Poodles sold over 3 years old:
    =SUM(($A$2:$A$200="Male")*($B$2:$B$200="Poodle")*($C$2:$C$200>2))
  • To get the total cost of male Spaniels sold:
    =SUM(IF($A$2:$A$200="Male",IF($B$2:$B$200="Spaniel",$E$2:$E$200,0),0))
  • To find out the average age of male dogs sold:
    =AVERAGE(IF($A$2:$A$200="Male",$C$2:$C$200))
  • To find out the average cost of male dogs sold over 2 years old:
    =AVERAGE(IF($A$2:$A$200="Male",IF($C$2:$C$200>2,$E$2:$E$200)))
  • To find out the Minimum age of dogs sold that are sterilized:
    =MIN(IF($D$2:$D$200="Yes",$C$2:$C$200))

All the above formulas must be entered with Ctrl+Shift+Enter

TIP: If you are having problems writing an array formula to sum your totals then use the Conditional sum wizard, Tool>Wizard>Conditional sum. If you don't see it then you will need to add it via Tools>Add-ins>Conditional sum wizard.

 

While using array formulas can be very handy they have one draw back and that is, too many of them within your workbook WILL slow down Excels recalculations. If you will need a lot of array formulas within the same workbook consider using, Pivot Tables the Database functions instead. These are ideal for extracting information from tables and databases.

Excel Autosum: sum up values in Excel automatically

Because adding numbers is probably the most common function that Excel is used for, Excel has a built-in Feature called AutoSum located on the Standard toolbar.  AutoSum is represented as the Greek Capital letter Sigma Σ.  You can use AutoSum to sum a range of cells.  A Range can be one single cell, or many cells.  You can sum cells in a contiguous (no gaps) range of cells, or a non-contiguous (cells not joined together) range.

 

To use AutoSum  you must click in the cell that you wish your result, or addition to appear in.  As a default, AutoSum looks up a column for figures immediately above it to add together.  This works great, unless it encounters a blank row or text.  If it does, then it stops at the last cell with a number in it.  If there are no numbers above it, AutoSum will automatically go to the left looking for numbers to add up, but will again stop at a blank column or text.   This is Excel's default, but you can easily change it.

 

The SUM Function is written as =SUM(number 1, number 2).  = is the trigger to Excel that a function or formula is following.  SUM is the name of the function and (number 1, number 2) are the arguments that the SUM function needs to work, or in our case the numbers it is to add up.  When you click the AutoSum icon, you will see the SUM function written in your cell, with a marquee (floating dotted line) around what the AutoSum intends to add up.  If the highlighted range is what you wanted to add up, click OK, if not then change the range you wish to add.

 

Following are three screen shots showing the AutoSum.

AutoSum automatically picks up the numbers above it

[Image]

AutoSum automatically looks left for numbers if it encounters no numbers immediately above it, but numbers to the left.

[Image]

AutoSum automatically stops when it encounters a blank line, or text in the middle of the range it is trying to add up.

[Image]