Getting it Right, Part Two: Microsoft Excel

It's been a while. I've been trying to wrestle this complex beast by the horns.

I have no illusions that I'll be successful. But here goes nothing.

First, from the Institute of Duh. Microsoft Excel is a spreadsheet program. So here's what Excel is:

• A powerful tool for aggregating numeric data
• An effective way to make low to mid-level financial projections
• A useful tool for scrubbing and analyzing data dumps

Here's what Excel is not:

• A database application. Say it with me - Excel is not a database application. Repeat as often as necessary.
• A project management tool. Don't ask me why anyone would try to make it into a project management tool, a task system, a scheduler, or a calendar. It's beyond me.

Now, about how to succeed with Excel. Just as I previously wrote about with Word, there are some general best practices that I've acquired from some of the greats, and am here now to present in a condensed format:

• Instead of KISS (Keep It Simple, Stupid), it's KIDD. (Keep It Dynamic, Dude.) Instead of putting 2 in A1, 2 in a2, and then "=2+2" in A3, make A3 instead say "=SUM(A1:A2)". That way, you can always change the value of A1 or A2, knowing full well that the value of your total will change instantaneously.
• Give up trying to learn every last function in Excel. Repeat - FORGET IT! The best thing to do is figure out instead what it is you want to do, and then work backwards from the desired result. Believe it or not, with some practice and some creative Googling, you'll get there eventually.
• So what if you don't find a function that does something you're planning on using all the time? OK, write your own. Easier than it sounds. Stay tuned for a future blog on some quick and dirty functions I've used.
• For you macro junkies out there (guilty, Your Honor), try storing your macros in an XLA file rather than in "PERSONAL.XLS." Too much stuff in PERSONAL.XLS can often result in it getting needlessly bogged down.
• In that future blog about macros, I promise also to talk about how to make an Excel add-in. Good way to go if you want to have individual chunks of code for individual applications.
• Fo the record, PERSONAL.XLS is in C:\Documents and Settings\[User]\Microsoft Office\Excel\XLSTART\. That's also a handy directory to have if there are any files that you want to have load at startup in Excel.
• Make an absolute religion out of templates. No different than Word in this respect; make sure that your templates have the following properties:
• The cells containing critical formulas are protected
• The cells where you want an end user to input data are not protected
• Any workbooks that will be going to an external client are free of comments
• Don't just protect the cells. Protect the worksheets. If you don't protect the sheets, people can delete those worksheets. That might be bad, what do you think?
• Speaking of templates, the ultimate template secret is in XLSTART.
• Set up a blank workbook with all the parameters you want, from margins to number of worksheets.
• Save the file as "Book.xlt"
• Save the file in C:\Documents and Settings\[User]\Microsoft Office\Excel\XLSTART\
• There'll be a "Book.xlt" file there already; when prompted, you can say "Yes" to the replacement
• When you quit and re-open Excel, your workbook becomes the default.
• Good news - the AutoCorrect settings and the Spell Check information that I discussed in the previous blog carries over to Excel. (As well as all the other Office applications. But Excel is the current area of focus here.)
• Useful, quick-and-dirty auditing tool: Hold down the CTRL key and hit the Tilde (`) key. This will allow you to toggle back and forth between formulas and values. (Yes, you can print that information.)
• If you have an enormous sheet, that bogs down Excel every time you open it, try this: Go to Tools->Options->Calculation. Temporarily change it to "Manual." Make sure you remember that you made this change. If not, you may forget later.
• In fact, spend some time and get comfortable with Tools->Options. You can change your default file save location, the default number of worksheets in a book, and default chart colors.
• This is unusual for me to recommend registry hacks, but I have to make an exception in this case. By the way, if you're not comfortable making potentially dangerous modifications to your computer, please skip to the next bullet.
• Go to Start->Run->Regedit.
• Click on the folder that says "HKEY_CURRENT_USER"
• Click on the sub-folder for "Software"
• Click on the sub-folder for "Microsoft"
• Click on the sub-folder for "Office"
• Click on the sub-folder for the highest number. I have Office XP, so that number is 10 for me.
• Click on the sub-folder for "Excel."
• Click on the sub-folder for "Options."
• On the right side, right-click, and choose "New->DWORD Value."
• The name of the value should be "UndoHistory."
• The base should be "Decimal," rather than the default, "Hexadecimal."
• Set the number to anything between 1 and 100.

A couple of final notes:

Excel documents tend to get corrupted when you put them on a network drive, and people are trying to open and re-open them. Watch out for that. If a large number of people are trying to open and change the same book over and over again, you're probably trying to treat Excel like a database. Stop it right now.

Excel is an OK application for forms. Access is way better. Don't forget - Access can do calculations, too. Access is a lot better at storing large amounts of data.

The bottom line, my friends, is that Excel is not really such a great way to store large amounts of data, but it provides some very elegant and graphically appealing ways to display a slice of data. Excel is well-suited to grab a chunk of data from an external source and analyze it, but the external source is probably better suited to store the underlying data. Division of labor, that's what it is.

The next blog will be a little bit of a rest stop for both of us - PowerPoint. Until then, be well.

Getting it Right, Part One: Microsoft Word

This blog is going to be in two parts. Whether or not I successfully join the two parts together at their junction, well, that remains to be seen. Like hanging cabinets (long story), that can prove trickier than it seems at first.

I read a lot of books about Microsoft Office. I can say with absolute, unwavering confidence that the number is in double digits. It's probably pretty close to my age. While I spent three and a half years in the Office training trenches, I obsessed with the mission of learning it all, of slaking the unquenchable thirst for Office knowledge and understanding.

I just finished reading a book by a guy called A.J. Jacobs titled The Know-it-All: One Man's Humble Quest to Become the Smartest Person in the World. This guy read the entire Encyclopedia Britannica. As in, all 32 volumes.

I know exactly how he feels.

But I fear Jacobs and I both suffer from the same problem in our quest - we may be gaining knowledge, but really lacking in understanding. In both of our bookwormish quests to fill our brains with really great tidbits of trivia, I fear there was there missing a larger understanding of the why and wherefore.

I quote David Weinberger: "Knowledge without understanding is like, well, information."

So what I want to do in the second part of this blog is shed some light on where each of the five core Office applications fit into the organizational and business scheme of things, and how to best succeed with each one. I'll do them in my own chronological order, as in, how I learned them. To save both my fingers and my readers' eyes, I'll address one application per blog. Today's discourse is going to be my least favorite of the five: Microsoft Word.

Microsoft Word is, above all other things, a word processor.

Duh.

Yeah, I know. But I have to make this overly obvious finding from the Institute of Duh first so I can talk meaningfully about what Word is and what Word isn't.
Word is:

• An effective tool for letters, memos, and reports
• A useful way to manage large, complex documents
• One of the more effective ways to send out a mass mailing (marketing letter, holiday card, etc.)

Word is not:

• A page layout or publishing application. For anything more complex than a company newsletter, I personally think Word is the pits.
• A calculation application. I kid you not, I used to get all kinds of questions about how to do calculations in tables, use of Equation Editor, and how to do charts. In one word: Excel.
• A web design studio. I know, Word has this oft ballyhooed feature called "Save as Web Page." I implore you not to use it for that. The code it produces is horrifically unwieldy.

Now, about how to succeed with Word. There are a couple of best practices that we should all keep in mind. I certainly didn't invent any of these. I just try to pass on what I've learned from the trenches:

• Anything worth doing is worth doing well. Anything worth doing well is worth making a template out of.
• As long as you're going to have a template, make sure to embed styles into that template. If nothing else, change the Normal style to something more interesting than TIMES NEW ROMAN 12, FOR THE LOVE OF GOD!
• Use styles instead of local formatting. You'll thank me later.
• Eschew the use of Format Painter. It's a wolf in sheep's clothing.
• If you have templates for the whole group, make good and sure to go to Tools->Options->File Locations, and point your "group templates" to a shared, network directory.
• If you want to kick it up a notch, export the following registry key:
  Computer\HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Common\General\SharedTemplates
  (in my case, it's 10.0, because I use Office XP; 2000 would be 9, 2003 would be 11.)
• Kick it up another notch and have your administrator "push" that key to everyone in your office, so that everyone has their Group Templates set to that specified directory.
• Use AutoCorrect to your advantage. I would very highly recommend getting rid of "Automatically capitalize first letter of sentences," ditto table cells. I would recommend adding entries of your own for your initials, your company name, and any industry lingo you use often.
• Again, if you want to share that information, ask your administrator's assistance in "pushing" out MSO1033.acl, which is usually located in C:\Documents and Settings\[user]\Application Data\Microsoft\Office\ ; that's your AutoCorrect entries.
• Don't trust Normal.DOT further than you can throw it. If your Word starts acting weird, delete NORMAL.DOT. You can hunt it down at C:\Documents and Settings\[user]\Application Data\Microsoft\Templates\.
• Add industry terms, lingo, and proper names to your custom dictionary. When you get the dreaded red squiggly, right-click on the word and choose "Add to Dictionary."
• In answer to your next question, the file is called "CUSTOM.DIC," and it's C:\Documents and Settings\[user]\Application Data\Microsoft\Proof\.
• If you're thinking of putting one person in charge of adding all the words to the custom dictionary, take heart - this is actually a file you can open and modify. So if you work in a field that has a fair amount of industry-specific terms (science, medicine, law), you might get one of your fast-fingered colleagues to create a base custom dictionary for the office.
• Remember, though, that Spell Check doesn't check for word misuse - if you say, "Linda excepted the job offer," when you should have said "Linda accepted the job offer," Word won't nail you.
• Now the good news - words you add to Spell Check are not the exclusive domain of Word. What you add in Word's spell check carries over to the other four applications, plus Project.
• Turn off Grammar Check. I, for one, think that it does litle more than give a false sense of security as to the readability of your document. This is a time to have someone just read the document, and look for consistency of voice, tone, and style.

In short, my dear readers, I would ask you to remember that Word operates on the principle of "Type first, format later." Your best bet is to get all your text down on the page, then go back and apply styles appropriately. Realize that if you can't get Word to listen to your formatting requests, you can always highlight the text, and then hit SHIFT+CTRL+N. That will set the text back to "normal."

In my next blog, I'll take on the biggest of all the beasts: Excel. Until then, be well.

Professional Looking Word Documents

I've seen a lot of Word documents in the course of my professional existence.

Problem is, most of them are poorly done. Some of the major mistakes I see:

1. Misspellings
2. Misused words, malapropisms, and inappropriate tenses ("the bird flapped it's wings" is incorrect; the correct phrasing is "the bird flapped its wings"; "disinterested" means impartial, not that you don't care)
3. Incorrect page numbers
4. Missing pages
5. Inconsistent use of fonts, sizes, and markup

The thing I find really puzzling is that most of these are so easily avoided.

So I'd like to offer a couple of best practices to make long documents really work in Microsoft Word. Some of them will sound like findings straight from the University of Duh, while, hopefully, others will be from the University of Aha!

1. Use styles consistently for your headers. Use at least two levels - the first level is the chapter name, the second level is the sub-topic under the chapter. Longer documents may require a third or fourth level of heading. You make the call - does the document justify it? I've done training for federal government and private sector organizations dealing with some fairly complex regulatory information, and four levels of header is not unheard of. Look at your subject matter.
2. Make an absolute religion out of using sections. Make your title page one section, your TOC another section, and each chapter in your document its own section. If you have an index (which you really should), make that its own section.
3. As mentioned in #2, you should have a TOC, an index, and a title page.
4. In the header for Chapter 1, go to View->Header and Footer. Then, go to Format->Borders and Shading. Put a border on the bottom, and apply it to the paragraph. Make the header contain the chapter title. In Chapter 2, and each successive chapter, go to View->Header and Footer. Click on the button that says "Same as Previous" to break the link between Chapter 2 and the previous section. The good news is that the border stays, but the chapter name can be different for each chapter.
5. In the footer, just keep the page number. Let that be consistent throughout the document, with the exception of the TOC and the title page. If you want, you can have the TOC and any other supplementary information (acknowledgements, copyright information, stuff like that) be all one section, and have the page numbering be Roman (i, ii, iii) instead of Arabic (1,2,3).
6. I would recommend using Next Page section breaks instead of Odd Page or Even Page section breaks. Odd and Even Page section breaks really only work if you're willing to have the page be COMPLETELY blank, devoid even of a page number. If you want a blank page at the end with the header and footer information, I'd use page breaks, rather than odd or even page section breaks; that way, I have more control over the document.
7. Indexes are a good idea if the reader is going to use your document primarily to look for specific terms, rather than read through a concept. If that's the case, a well-developed index will save your reader hours of time. Granted, it'll take us writers some time, but so what? We're here to serve the needs of our readers. End of discussion.
8. Small thing, but one that annoys the living daylights out of me: If you're going to use images (figures, illustrations, photos, etc) make sure they're high quality. Nothing says "I don't care what you think, Thou Reader" (thank you Walt Whitman) like a crappy image you downloaded off of someone's website and then (gasp!) enlarged. Just don't do it. Don't strain your readers eyes that way, and don't destroy your professional credibility in the first place. Get a good source image you can downsample, and go from there.
9. As far as images go, one other point - ClipArt and WordArt rarely if ever look professional and clean. You're better off not risking it.
10. Keep your markup to a minimum. Use bold and italics sparingly. Don't use sparkle text at all. Don't ever use underline, unless it's to reference a website.

Remember, above all, that your document is for the reader. Make it work for them, not for you. That's priority #1.

Grace Under Exit Sign

I never thought this day would come. I really could have seen myself working at New Horizons Computer Learning Center in Sacramento for a good long time. 3 1/2 years is a pretty good tenure at a company for people my age.

But the call I never thought would come did.

Apple.

So now, I am going to do my most personal post to date, while at the same time be faithful to my mission to keep this blog on the Office target.

If you find yourself in my situation, where you're going to be leaving your current position and/or company, I would like to offer some best practices:

1. NEVER delete projects, files, or documentation that another person will need. Files that you create on a company computer on company time are company property. Same for email.
2. Also on email - I would suggest to you that it's unethical at best to take the names and phone numbers of your clients from your current company and try to solicit business from them in your new capacity.
3. Put the power of Word to work to make extensive documentation of any programs, processes, or procedures where you are the go-to guru. If you're like me, and you have to make a lot of documentation where you have to take screen shots, check out a program called ScreenHunter Free, which is from Wisdom-Soft (http://www.wisdom-soft.com); it's a great little tool to grab some screen, and paste it into Word. Check for a future blog on best documentation practices in Word.
4. If you've password protected any Word or Excel forms, make sure to give an administrator those passwords. Remember, they can get around your passwords if they have to, but make it easy, and don't make them do it. Same goes if you administered or created an Access database - be sure to give someone else administrative rights to the database so they can pick up where you left off.
5. If you created any custom VBA code for any of your in-house documents, make sure to painstakingly comment it, so that, again, another person can pick up where you left off.
6. If you have any clients to whom you have made promises, be sure to create an agreement with that client on how that promise can be fulfilled in your absence.
7. Make sure that if you're close to any of your colleagues, to tell them how much you've appreciated working with them, and give them a reason to want to stay in touch with you.
8. Never badmouth the company you're leaving; you never know when you may have to go back and ask for a job.
9. In you resignation letter, be brief and to the point. Make sure to end on a positive, encouraging note; that will contribute significantly to how people will remember you.
10. Most of all, remember that you are unique, valuable, and important. But you're neither indispensable nor replaceable. Act accordingly.

Outlook tasks vs. Project

Which is better? For those of you who read my post on Excel versus Access, then you probably know what the next part is - "it depends." You should have seen that one coming.

So, more to the point: I use Outlook when I need only manage my own tasks, and I'm not billing someone else for my time. I can use Outlook to assign tasks to my co-workers, as long as I'm not concerned about how Pete's finish date affects Mel's start date, and that finish date doesn't adversely affect the project's ability to finish on time. Outlook is effective for managing independent tasks.

Project is a little different ball game. (to put it mildly!) I use Project when I have Bill and Russell hanging drywall in my kitchen, and Willie the electrician has to re-wire the outlets before the drywall is complete, and then Dave the plumber can't do the piping under the sink until Russell tears out the cabinet under the sink. Project is very effective for managing interdependent tasks.

Outlook tasks are a good way to manage deadlines - I train some law firms who use Outlook tasks for filing dates, and then the Calendar for their hearings. Remember, the filing of a document is a date by which it has to be done, whereas a Calendar appointment is a finite start and end period on the clock.

Project tasks don't really get so much into the question of, "From 8:00 AM to 1:30 PM, I have to be in court." Project is more concerned with, "Willie has to install the ceiling fan in the kitchen after Bill and Russell tear out the cabinets above the sink." Further, Project is very effective at calculating the number of days ahead. In other words, if I say that a task is going to take 5 days, and I can start it on the 21st, then Project helps calculate what the end date will be.

Outlook is good about filling in the holidays on my Calendar, like Thanksgiving and Christmas. Project says, "Tell me what days Pete can't work, what days Mel can work overtime, and what days the office is closed altogether."

Last point - Outlook is a better quick-and-dirty solution; Project is a better large-scale solution that requires more of a time investment at the outset. Project is extremely effective at managing resources, both work and material. Work is people like Russell and Bill; materials are stuff like drywall, nails, studs, and paint. Project can itemize those costs and summarize them on a report for a client.

Outlook can't do all that, but if you don't care, Outlook is a much simpler program, ounce for ounce.

So consider both options; like Excel and Access, they are far from mutually exclusive. Act accordingly.

Effective VBA

I love VBA. VBA, if you've never heard the term before, stands for Visual Basic for Applications.

What that means, simply is that it's a programming language - a way to automate processes in Microsoft Office, and make your existing processes go faster.

Before I go any further, though, in talking about when to use VBA, I want to talk about when not to use VBA:

1. To make a program act like another. For example, if I'm using Excel VBA to automatically add a number to a row, or to require data in a field, or to aggregate data from a form, I might as well use Access.
2. To replicate existing functionality of a program. For example, if Excel already has a button/command to do Paste Special->Values, then there's no need for me to make a macro to do that. (Oops, too late, already did.) But there's a case where if you go to Tools->Customize in your program (like Excel), then you might find that the functionality you want already exists. In the case of Paste Special, Values, the button did exist; I just had to go to Tools->Customize, and look under "Edit," and there it was.

Now, having said both of those things, let me offer a disclaimer: As far as #1 goes, I've given people advice on the Excel Community forum at Microsoft's web site on how to automatically generate a new invoice number every time a person opens a workbook template. In many cases, Access can do it better, but there's no one at the office who can support Access, and/or there's a license issue. OK, that makes VBA the best choice. Fair enough.

I digress. When to use VBA is also two part:

1. To make one step out of six or seven steps. For instance if I want to do three or four steps over and over again, I would record those steps in a macro. As anyone who has ever taken Excel Level 3 with me will tell you, I usually have people record the steps of giving a cell a blue background, white text, center aligned, Arial font, size 9. (I sometimes mix it up how many of those steps I have people do.) In this case, all you do is just have the program (in this case, Excel) follow you through the steps, so that next time, all you'll have to do to replicate them is to run the macro.
2. To build a better mousetrap. This is where you not only record the steps, but then add some conditional logic and/or control constructs to it. The conditional logic part is to say, for example, "if the workbook is blank, don't do anything to it," while a control construct might be, "execute this code once for every worksheet in the book" kind of thing.

Frankly, I do most of my VBA in Excel, because it's what I use the most. I've done VBA in Word, but I'm still having a hell of a time learning the object model. The object model means how the entities are related; in Excel, for example, the Workbook is an object, which contains Worksheets, and those contain cells, which can be selected. Word isn't that consistent.

One can do an extensive amount of VBA in Access. I have barely scratched the surface of its power. Remember, macros in Access are different than macros in Excel. A macro in Access means assembling steps using the Macro editor. To do VBA in Access means creating what's called a module. Either way, you can't have Access record the steps. In Excel, Word, or PowerPoint, you can have the program record your steps and play them back later.

Back to the example I brought up earlier about automating the process of changing the background color, font, and stuff like that. I had my class the other day go into a certain file called "OfficeSupplies" (which can be downloaded from the New Horizons website, by clicking this link, which is to a Zip file. What I have the class do is start with the "Australian" worksheet, and then record the steps of various formatting, like currency formatting the dollars, bolding the title, and stuff like that. The macro that I have the folks create initally looks like this:

Sub formatWorksheetLongWay()
' formatWorksheetLongWay Macro
' Macro recorded 6/20/2005 by Geoff Lilley
Range("A1:E1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection.Interior
.ColorIndex = 5
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("A3:A7,B3:E3").Select
Range("B3").Activate
Selection.Font.Bold = True
Range("B4:E9").Select
Selection.Style = "Currency"
Range("B9:E9").Select
Selection.Font.Bold = True
End Sub

This code suffers from two problems. Number one, I have to manually apply the formatting to each individual worksheet, in turn. Two, it's ridiculously bloated, which is going to slow down my processing time long-term.

I re-wrote the code to be more efficient; this code loops through all the books, and then does all the same formatting to each one, except "Chart1."

Sub ColorRegions()
'variable to figure out what sheet number we're on
Dim SheetNum As Byte
'variable to figure out how many sheets are in the book
Dim SheetsCount As Byte
'move the chart worksheet to the end
Sheets("Chart1").Select
Sheets("Chart1").Move After:=Sheets(ActiveWorkbook.Sheets.Count)
'start with the first sheet
SheetNum = 1
'do this stuff to every sheet but "Chart1" by looping through the sheets
SheetsCount = ActiveWorkbook.Sheets.Count
Do Until SheetNum = SheetsCount
'select each sheet in turn, starting with the first
ActiveWorkbook.Sheets(SheetNum).Select
'take the title, make it bold and centered, white text, blue background
Range("A1:E1").Select
With Selection
.Interior.ColorIndex = 5
.HorizontalAlignment = xlCenter
.MergeCells = True
.Font.Name = "Arial"
.Font.Size = 14
.Font.ColorIndex = 2
.Font.Bold = True
End With
'bold the titles on the left and top
Range("A3:A7", "B3:E3").Select
Selection.Font.Bold = True
Range("B4:E7", "B9:E9").Select
'currency format the numeric cells, including the totals
Selection.Style = "Currency"
'go to the next sheet
SheetNum = SheetNum + 1
Loop
End Sub

In essence, I just cut down the code to the necessary pieces, and then wrapped the whole thing in a "Do" loop. The loop just says, "do this stuff once to each sheet in the book, until you get to the end."

This is an example of VBA for quick and dirty fixes. I highly recommend it as a solution. If you live in the Sacramento area, and you want to learn more about Excel and macros, call the New Horizons main line at (916) 641-8500. As it stands right now, we have to generate enough interest from people to get a full VBA class together. If we get the interest, either me or one of my colleagues can teach it. But we need the demand first. So let people know it can be done!

Best Way to Make Forms in Office

I get this question a lot. What's the best way to make a form to fill out?

I have several different answers to this question, each with their own relative merits and drawbacks. The guiding question is always, "What do you want to do with the form when the person is done filling it out?"

1. Create the form in Word. If you want to go this route, there are two tools that will serve you well - the Forms toolbar, and tables. Put the label for the data (like the word "name" or the word "address" in the left column of the row, and the form field where you want the person to put the name, or address, or phone number, in the right column of the row.
   
   Use this method if you want to create a quick and dirty form that will be printed out and mailed to you, or emailed to you.
   
   Don't use this method if you're concerned about maximum number of characters in a field, and/or you're worried about populating this information into a central data location later.
2. Create the form in Excel. If you want to go this route, the two tools you'll find most useful are validation and protection. Use Data->Validation to restrict what kind of values a person can put into a cell, like minimum and maximum numeric values, and in-cell drop-down lists, using Data->Validation, with the "allow" property being set to "list."
   
   Use this method if your primary concern is getting valid data within a given threshold. Compared to Word, still pretty quick and dirty, and still perfectly serviceable for printout and/or email.
   
   Don't use this method if you have to populate the data into a central data location later. You can do it, but it's clunky and inelegant. But better than Word in this regard.
3. Create an Access database. This will require first creating the table(s) to hold the data, then the forms to enter the data. Probably, you'll want to implement some kind of security, so that your form users don't see your back-end functionality.
   
   Use this method if you're only dealing with internal clients (people in your office, or people who work for your company in other locations who can access the same server(s) you can.) This method is the best of the Office programs for validating data to ensure its compliance with your standards, and has the best built-in method for making sure that required data gets filled in.
   
   Don't use this method if you have to make your form available to people outside your company.

Other solutions to the problem that I've heard batted around, going outside the traditional confines of Office, also come with ad and disadvantages:

1. InfoPath. This is a new product for Office 2003. It is a very elegant, easy-to-use forms designer.
   
   Use this method if you have a SharePoint website for your company where people can go and fill out your form on the team and/or company SharePoint site. You can also use this method if you already have an Access database created, and you want to make the InfoPath form talk to the Access database.
   
   Don't use this method if you have to publish your form to external parties; their likelihood of having the tools needed is pretty low.
2. Acrobat forms. Acrobat 6 and 7 both have pretty robust, usable tools to create radio buttons, check boxes, and drop-down menus. If you know your way around Acrobat JavaScript, you can extend the functionality of the forms quite a bit. With 7, there's a separate Form Designer product (comes with the Professional version of Acrobat, I believe) that makes form design a pretty painless process.
   
   Use this method if you have to publish your form for use by the broader general public, and you want to give that general public the chance to fill in the data in a form. You can then either have them postal mail it to you, or submit it to a database.
   
   Don't use this method unless you have a respectable knowledge of Acrobat and of basic web design; the way to make certain functionality work in Acrobat forms requires a little of each.
3. Web forms. You can put a form on a website that a person can fill out, right there on your website, and have that form data go to a database, get emailed to you, or any combination thereof.
   
   Use this method to make your form most widely available to the general public. This method requires you either are or know someone who can do both web programming and database design. This method is very effective at allowing you to aggregate data into a database. To make it work, it requires that you have a web site that can support a web language, like ASP, PHP, JSP, CGI, or ColdFusion. (There are many others; those are just the first few I can think of.)
   
   Don't use this method if you don't need to aggregate your data into a database; it's kind of overkill, and comes with a certain human-hour cost. Don't use this method if you don't have access to competent web design, programming, and database design resources, or don't have the time to learn them.

Personally, given my choice, I'll usually use a web form that uses ColdFusion to talk to a database, and fill that database with the form values. Then, again, using ColdFusion, I can write code to email me a notification that a new form submission has occurred, and the person who submitted it can get a "thank you" email. I say ColdFusion because that's what I was trained in, not necessarily because it's better than ASP, PHP, JSP, or CGI.

More than anything, look to see what others have done, including cases of what you do and don't like. Hopefully, that'll help guide your process better.

Why doesn't everyone use templates?

This is something I've never understood. Why don't more people use templates in their office?

Templates are a way to standardize what comes in and what goes out. Templates are a way to make sure we're all on the same sheet of music, when creating such standard documents as purchase orders, cover letters, confirmation letters, presentations about our company, and expense reports.

I've heard people say that standardization can suppress creativity. OK, fair enough. But the way I see it, this is not the kind of stuff that we want to spend our creative resources on; this is the kind of stuff where the compelling interest to project a unified front is far more important than the compelling interest to be creative.

Save creativity for new ideas about how to manage resources, how to promote and position products, how to sell and market services.

Here's a couple of ways to think about how to use templates in the various Office applications:

1. In Word, templates are a really effective way to store styles and macros. So if you create styles that reflect the company look and feel, you can check the box that says "add to template," and then the template becomes a container for those styles - the two go together. As far as macros go, if the automation provided by the aforementioned macros is specific to the template, why not?
2. In Excel, create templates, like an expense report, where the expense types are fully spelled out and known quantities. Protect the cells that have the formulas (total miles travelled; reimbursable rate times miles travelled). Use Data->Validation to establish upper and lower thresholds for values put into those cells. Use Data->Validation->List to ensure that a person has to choose their name from a drop-down that's in another set of cells.
3. In PowerPoint, create a template with your company logo and colors. Put standard company fonts into the Slide Master and/or Title Master, so that everyone begins with the same look and feel.
4. In Outlook, you can use templates to create a standard message, like a confirmation of purchase, or a request for payment.

In all four cases, create the look you want in the file. That means everything - margins, headers, footers, backgrounds, fonts, colors, the whole bit.

Also, in all four cases, File->Save As. Give the file a meaningful name ("Expense Report" or "Confirmation Message," for example.)

Make sure to go to the bottom, where it says, "Save As Type." Change the type to "template." In Word, it's "Document Template;" in Excel and Outlook, it's just "Template;" in PowerPoint, it's "Design Template."

Once you click on the option for the program in question, you will find yourself taken to a whole new folder, called just "templates." That folder, by default, is in "C:\Documents and Settings\user\Application Data\Microsoft\Templates." This is normal; don't be alarmed. Go ahead and let the program save the template there. Hit "Save," and then close the template file.

Pause. How can you modify your templates directory so that other people can share your templates? Easy. First, open Word. Go to Tools, Options, File Locations. What I generally recommend to people is to change the setting called "Workgroup Templates." In my case, I changed my "Workgroup Templates" on my own computer to a folder called "NHCLC Templates," with NHCLC standing for New Horizons Computer Learning Center, where I work.

For the first three programs mentioned, the way you access the template to use it is to go to your "File" menu, and choose "New." I'll describe Outlook's method shortly. If you use Office 2000 or previous, you'll get a window that says "Templates." Yours will be listed under the tab called "General."

If I had modified my "Workgroup Templates" setting in Word, then any templates I save in the "NHCLC Templates" folder will also show up in the tab called "General."

To get the templates that I created to show up in a separate tab, what I would do is put sub-folders under "NHCLC Templates," like "Reports," and "Personnel." If I do that, then the names of the folders show up as tabs in the "Templates" window. That would be a sound idea if you have a fairly extensive array of templates.

If you want to ensure that everyone in your office uses your templates, the best thing to do is get buy-in at the top levels. If you do, then you can get your administrator to copy the registry key that tells people where their workgroup templates are located. The key, by the way, is called "SharedTemplates," and it's under "My Computer\HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Common\General."

Remember, the number "10" in my case refers to the fact that I run Office XP. For 2003, it's "11," and for 2000, it's going to be "9," and so forth. But, if in doubt, you can do a registry key search for "SharedTemplates," and you're good to go.

Once you've found the one you want, just double-click on it. Remember, the best part of all is that you're not overwriting the template when you use this method. You're starting a new document based on it. It's a very elegant, scalable solution.

Back to Outlook. The way you create and use an Outlook template is slightly different. Before you start composing your template message, go to Tools->Options->Mail Format, and make sure that "Use Microsoft Word to edit e-mail messages" is turned off. Personally, I think this so-called "feature" sucks, but that's another story.

Once you've hit "OK" on that change, start a new mail message. Put in that message any information that will be constant for every time you send it. I might create a message addressed to my training group, subject "Timesheets are Due," with the message text being, "Timesheets are due this Friday." That's keeping it nice and generic; it's not date-specific.

Once that's in place, in the message, go to "File," and choose "Save As." Again, as above, save the file as a template.

Close the message. When it asks "Do you want to save changes?" you can say no.

To use the template, you go to your "File" menu, and click on "Choose Form" (towards the bottom.) At the top, where it says "Look In," choose "User Templates in File System." That's where you'll find yours. Click "Open," and you're good to go.

Templates, my friends, will save you infinite amounts of doing and re-doing, and making sure everyone has the most current version of this or that. Make templates not just a policy at your office. Make them a religion. Amen!

Word Tables

I hate Word tables.

I have never had much luck getting Word tables to cooperate with me. Either they fall completely off the page, or the column width gets all jacked up.

I have, however, found a few useful workarounds for this problem:

1) Convert the table to text. Get anywhere in the table, and click on the Table menu. Choose Convert, and then "Table to Text." That will often take care of the problem; you can use tabs to get the text looking how you want. Once you've done that, reverse the steps: Table->Convert->Text to Table. Make sure you use "Tabs" as your delimeter. That might work.
2) Instead of two tables on the same page with different column widths, experiment with splitting cells. On a row where you need more cells, right-click in one of the cells, and choose "Split Cells." You get prompted whether you want to create multiple columns, rows, or both.
3) Use #2 in conjunction with merged cells. Same deal; highlight the two or more cells, right-click, and choose "merge."
4) When all else fails, highlight the table, cut it, and paste the damn thing into Excel. Get it just right there. Then paste back into Word.

None of those solutions are perfect by any means. But the splitting and merging cells is the best I've found so far. Sure beats setting tab stops for stuff like phone lists, resumes, and any other document where structured data is at a premium.

Excel versus Access

OK. Let me begin by saying that I am like all the other 3,999,999 Office users out there, in thinking that Excel is about as cool as cool gets. Excel is friendly, intuitive, "come on in, the water's fine." Excel is familiar.

Access, by contrast, seems cold, dark, distant, and mysterious, "don't go in there, you might get hurt..." kind of a sense. Access doesn't understand Excel's Undo. Access feels foreboding.

Access talks in strange terms, like "referential integrity" and "bound column," while Excel uses friendly, familiar terms like "column" and "row."

But here's the secret, friends: they're both useful. Both have their place and their good.

Excel is useful for two reasons: number one, because it's relatively easy to get in there and start working. Reason number two, because it's great for financial modeling.

Access is also useful for two reasons: number one, because it handles immensely complex, interrelated data with relative ease. Number two, because it make your finished product look like a stand-alone application, with much of the dark underside of functionality hidden from the user.

If I had to keep a list of customers, with names, phone numbers, and addresses, I'd just use Excel.

If I had to interrelate those customers, their orders, the products ordered, the shippers who sent the orders, and the employees who took the orders, I'd go with Access.

I just wanted to give a shout out of warning to those who try to avoid having to use Access by doing complex, processor-intensive VLOOOKUP, INDEX, MATCH, and OFFSET functions to get a result. The biggest problem you'll run into is that you're needlessly replicating the functionality that Access gives you right out of the box.

Let it.