Your Remote Business Partner

Creating and Using Templates in Microsoft Word

Knowing how to use a Word template can save a lot of time and help to produce professional looking and standardized documents for your organization.  Most of the formatting and other administrative tasks involved in creating Word documents and their layouts can be done for you if you create your templates correctly. 

You really shouldn’t create a new template for a document that you will just use once, but if you use a type of document many times over, such as invoices, letterhead or newsletters, the use of template (.dot )files can be very helpful. They can store features like auto text, styles, shortcut keys, custom toolbars, and macros. One can use the style and features of an original document by saving it as a template in Microsoft word. All the data relating to these items are stored in the template, rather than the document (.doc) itself.  If you store a document as a .dot file, all of the formatting is already done for you – and you don’t have to start “from scratch”.

Every Word document has a template associated with it. When you open up a new Word document, the template associated with that new document (by default) is Normal.dot. This is the default template that Microsoft has created for you and what ships with Microsoft Word.

But what if you never use Times New Roman, and prefer to use Arial in all of your documents? And you like your top and bottom margins to be .5”, not 1”? Then you need to know how to use Templates!

You can edit the formatting of the ‘blank’ document that pops up when you open a new Word. To do this, go to C:\Documents and Settings\user name\Application Data\Microsoft\Templates – open the Normal.dot file, make your changes, and save.  The New Blank Document which will open in the future will automatically show these changes.

Templates are especially useful if multiple people use the same document.  Instead of having everyone work on a .doc, where it’s very easy to overwrite the document with new changes, a template is, by its nature, read-only. People can use the same template over and over and no one will override it accidentally because that has to be done manually.

Saving a Document as a Template

To save a document as a template, simply create a new document with the settings and format you prefer and then save as with these steps:
File > Save As > Change the File Type to .dot (in the pulldown)

Word will typically change the file path to the Template destination folder. It’s best practice to save all of your templates in the same folder. If Word doesn’t take you to the default Template folder (typically C:\Documents and Settings\user name\Application Data\Microsoft\Templates), I recommend that you do this manually.

Generating a Document from a Template

In order to create a new document using a template, you can’t just click the “New Blank Document” icon on the toolbar or use CTRL+N, (doing this will simply open the default Normal.dot). To use your own templates, you must go to File > New, and then choose Templates > On My Computer.

And lastly, when using a template to create a document, remember to save it as a document (.doc,) instead of another template (.dot).

Have questions or need help creating your own Templates? Let me help.

Still Using Word Like a Typewriter?

Old habits die hard, and for many people one of those habits is to use Word like an expensive typewriter.  This is not efficient for many reasons, but especially when you are just coming up with your rough draft.  While Word was never designed to be as flexible as laying out index cards with all of your important ideas and then sorting them to create the structure of your paper - there are some things you can do as you work with a document that you may not have thought of.  

1. Use the hidden text feature.  When you are editing a document and you have that paragraph that you might use, but you are not sure about, or even just a sentence that is useful but needs rework - don't delete it.  And don't do the other thing you always do - cutting and pasting it at the end of the document in the clutter area. Instead leave it in place, but hide it.  This is not a commonly used feature, but it is easy to use and a great way to toggle decisions when writing or editing.  
2. •  Select the text you want to hide 
     
     • Use the shortcut  CRTL+SHIFT+h to hide selected text
     • Alternatively use CRTL+D or Format > Font to bring up the font formatting dialog box and check 'hidden.' 

 

Once you have hidden the text, you can toggle between visible and hidden by using the Show / Hide Formatting button.  

When you show the formatting, hidden text appears as this:

 

2.  Try using the outlining capabilities.  Some get frustrated when using Word because the automatic formatting is not what they want.  Word will guess what you are trying to do and do it for you.  If the software recognizes that you are trying to make an outline, it will change your text into a default format outline. 

Learn to control the formatting of the outline and you'll be much happier.  Access the Styles and Formatting task pane through Format > Styles and Formatting.  The default is to only show the formatting used in the document.  At the bottom of the task pane in the Show dropdown, select 'all styles.'

Now at the top of the list in the task pane will be two basic outline formats.  Select the one which is closest to what you want, and modify until you have what you want.  

3.  Use a different program.  I know, this seems like cheating, but there are two ways of creating documents that you should be aware of.  First, try Powerpoint.  While most people are familiar with the common view of slides, not everyone realizes that PowerPoint is building an outline that contains all of the slide titles and text.  It is easy to reorder your information by dragging and dropping your slides in the slide sorter.  Get it the way you want and then save it as a Word document by selecting File > Send To > Microsoft Office Word.

Data Validation in Excel Enforces Consistency

Have you ever sorted or filtered a spreadsheet only to find that some of the entries have the state of Florida listed as FL whereas others used FLA and some even typed the whole F-L-O-R-I-D-A?  Whether it is a project name, dates, or simply an inconsistent labeling, these types of poor practice happen all the time, especially when multiple people use the same spreadsheet. 

Part of the problem is that Excel really is not a great database and does not by default ask the spreadsheet user to declare what type of data each cell will contain.  Also, people tend to use the office suite only to the extent that they are comfortable with each application - have you ever received a letter from someone in Excel instead of Word?!!  We really should be asking ourselves if the document we are working on is in the appropriate format.  A good rule of thumb is to ask whether any calculations are being performed.  So in other words, are we adding column D + E and then dividing by column F and storing the results in column G?  Are we using any formulas at all?  If not, then we probably should be using Access instead of Excel.  If it is a contact list - that's an Access project, not Excel.

Assuming that we must use Excel for the task, let's look at ways to make sure we have everyone in lock-step.  For our example, we're going to think of a project-based firm that has customer names and project numbers.  First, we should make sure that we are conceptually thinking about how we set up the spreadsheet.  Think of a painter with a palette full of different colors of paint in one hand, and a canvas that is going to have the paint applied to it in the other.  Instead of colors, we're dealing with information, and instead of palette and canvas we have different worksheets.  Our palette will be named "Names+Formulas" and the canvas will be named "Data."  

 (Note I have colored the "Names" tab grey)

In the Names worksheet create two lists in columns A.  Label the column in Row 1 as Customer_Name.  Never use Spaces in headers.  For this example add fictitious customers: Zoe, Blackberry, Trump, and Newcastle.  Now that we have our current customer list we can name this range.  Let's assume that we expect to have some more customers in the near future so we will include a couple of blanks in the range. 

 

1. Select A1:A11 making sure to include the header name as well as the blanks for future names. 
2. On the insert menu select Names > Create
3. Ensure that the default Top Row is selected and click OK
   

This has trained Excel that in this worksheet, the list Customer_Name = worksheet (names+formulas) range A2:A11.  Now switch over to the Data sheet.  Lets assume we have a nicely formatted document set up that we are using to track our customer orders.  Column B will have the customer name.  To add a pulldown:

1. Select column B then off the Data menu select Data > Validation...
2. In the settings tab under Allow, select List
3. In the source field type: =Customer_Name
4. To prevent people from entering anything not on the customer list, uncheck the box beside Ignore Blank.

If you click OK and click in cell B3, a drop down arrow will appear in the corner of the cell.  Click this arrow to drill down and show the list of customer names to choose from.  A user can either select from this list or type in the name directly.  If you have unchecked Ignore Blank, then only the names from the list can be entered.  If you have checked Ignore Blank then the drop down is only a convenient reference instead of an absolute restriction.

The other two tabs in the Data Validation dialog box allow you set an error and input message.  If you are not ignoring blanks then it is good practice to set an error message explaining what to do if the user needs to add a name to the list.  Much like when you are using a program and a message pops up saying 'you do not have access to this location, contact your system admistrator' we can inform our users about what the proper method to add a new customer will be.  If everyone who has access to the spreadsheet should be allowed to add to the customer list it is simple.  Just reference the list on 'names+formulas' something like below:

Simple enough?  Great.  Repeat the same process for project numbers or other important items that can no longer be entered willy nilly.  In a future post I'll show you how to set it so that if you select customer A, then under project number only the projects (or order numbers) of that particular customer are available.  Now that's using data validation!!!

Now for some caveats:

• Don't overuse your new favorite feature.  Remember that simpler is usually better, and overconstraining anything is annoying for the users.
• Think hard about whether you need to use the input message or not. Most of the time it will be pretty obvious what needs to go where. And remember what is helpful the first time can rapidly become uneccesary and annoying if it is frequently used.
• Constrain only as far as is necessary and consider all your options. In the introduction I mentioned the example of state names. Instead of creating a list in Names+Formulas of all 50 states or their abbreviations consider some of the other validation options like text length instead of list. If you set the textlength | equal to | 2 it will have the same effect as having a long list in a dropdown of abbreviations.

• Remember to update your named ranges. If you add a customer name beyond A11 it will not appear in the drop down, and will trigger the error message until the named range is updated. Be proactive and maintain your spreadsheets when other people have to use them. Most people are not going to care why it doesn't work, they just want it fixed!
• Consider who should have access to setting up standards and maintaining lists. I typically will set up the names+formulas sheet, and then either hide it or protect it with a password. Your situation and both the number and skills of your users will inform this decision for you.
  • Test, test, and then test some more before you release your fancy new workbooks. Instead of assuming that you have all the angles, try actually inputting 20 or 30 records and see:
    
  • Have I overconstrained to where it is impossible to enter a record?  
  • Is it annoying or unintuitive to use?  
• Get help when you need it. 

Conditional Formatting in Excel

The bigger your spreadsheet gets, the harder it is to highlight the really important information or, indeed, to even find it.  For example you might have a long column of percentages and maybe they change frequently as you update the supporting information.  Wouldn't it be great to be able to track these changes at a glance?

Or, maybe you've even gone down a list of information and highlighted positive numbers with a green background and maybe negative numbers with red.  How would you feel if Excel would do that for you, and update it instantly if the numbers changed?  It can using a tool called conditional formatting... (and it’s easier to use than you might think!) 

Let’s get started with an example and then expand on some of the possible uses.  Look at the simple table that compares fiscal year 2007 to 2008 below:

 

Here we're interested in comparing this year’s results to the previous year.  Again, keeping it simple, our results can be up, down, or level. 

1. Select cells D2:D6
2. Clicking Format > Conditional Formatting… brings up the following dialog box:

Any changes we make will apply to all of the selected cells. 
For the first condition make the following changes:

Cell value is, greater than, 0

Note that when ‘greater than’ is selected in the second drop down Excel deletes the 4th field. 

Now that we’ve identified the first condition (regions that improved) we need to tell Excel how we want these to look.  Click the button to bring up the familiar formatting choices that we typically use for formatting cells.  Click the patterns tab and select a green color of your preference, then click OK.  Back in the Conditional Formatting dialog notice that the preview of condition 1 now shows a green background. 

Click the Add>> button in the Conditional Formatting box to add a second condition.  Following the same logic, change the values to:

Cell value is, less than, 0 and format the pattern to be red.  This time click on the fonts tab and change the font color to white.  Click OK and review your preview to make sure it looks right.

Click OK and Excel evaluates whether the cells meet either condition 1 or condition 2. 

Notice that the Mid-Atlantic region did not change year over year so D3 is unaffected by conditional formatting because it does not meet condition 1 or 2.  You may be tempted to add an ‘equal to’ 0 as a third condition but this is unnecessary.  You’ll quickly find that there are only 3 available conditions in conditional formatting which can limit some of your fancier ideas.  In another post I'll demonstrate some really advanced examples and even how to edge out a 4th condition. 

For now, we’ll add just one more level of complexity.  You notice that the first pulldown in the condition definition gives you the options of ‘cell value is’ and ‘formula is.’  If we want to highlight the entire row of the region that had the biggest increase then we use ‘formula is’.  And that’s the way to think about it.  If you want to format the cell that you are evaluating like we did in column D then use ‘cell value is.’  Any time that you want a cell to change based on the information contained in a different cell, use a formula.

I have to admit though, as nice as this is, it really does get a bit more complicated, and usually requires a couple of tries to get perfect.  And that’s why we’ll leave this example for our next post...

Using Headers to Create a Table of Contents in Microsoft Word

In Microsoft Word, open up Format > Styles and Formatting.  This will bring up a window on the right hand side.  Listed will be each different style used in the document.  We're interested in Heading 1, 2, and 3.

In our example, let's say that we have the document divided by chapters, and the chapters divided by sections.  Select the CHAPTER 1 label in the document, and click Heading 1 in the Styles and Formatting window.  Notice that the font size and style of the CHAPTER 1 text will update to look just like the Heading 1 text appears in the Styles and Formatting window.  You can change the appearance of this later if needed.

Now go through the document and select section labels and change them to Heading 2.  Again, these will be updated to match the settings for Heading 2.  The default Heading 2 is Arial 14 pt italicized.  This can continue to Heading 3, 4, etc. if needed for your document.  Once you have formatted the headings of your document it's time to let Word build a Table of Contents.

Scroll to where you would like the Table of Contents inserted and click Insert > Reference > Index and Tables.  Notice the tabs on the dialog box that open up.  Select the second tab "Table of Contents".  It's fairly straightforward to customize the options in this tab, but I recommend just using the default settings which are pretty basic.  You can always change these later if you want.

You'll get a default that looks something like this:

In addition to giving a professional look and helpful directory for the readers of your document, Tables of Contents include hyperlinks by default.  In a large document, you may know the section heading that you are looking for but perhaps not the page number.  Instead of scrolling through a long document, just go to the Table of Contents, hold CTRL and click on the the section you want to see.  This will take you to that precise location in the document.  Convenient!

Now, it might take a while to format a large document in order to prepare it for the Table of Contents, but once you see how easy it is to format your headings, you can format them as you work.

As you revise the document, you may occasionally find that you need to update the headings and page numbers in the Table of Contents.  To do so, just right click anywhere in the table.  Select "update entire field" and Click OK.  Voila ~ the Table is updated to reflect your changes.