Geoff Lilley's Blog About Stuff

Playing Professional

2009-10-12T21:51:00.000-07:00

I'm a recent convert to the show "MadMen." So far, it has not surpassed "The Wire" and "The Sopranos" as the greatest show ever, but hey, it's got all the makings of a top 10 so far.

For those of you who watch the show, I'll just say that the incident I'm about to reference to make a point is from late in Season 1. For those of you who have not seen the show, this may come too close to a spoiler for your tastes, so you might want to skip this one.

For those of you who don't plan to see the show, or know nothing about it, the key facts are these: Don Draper is a successful advertising executive at a company called Sterling Cooper. The head of a larger, more powerful agency tries to recruit him to work for another firm. The man's name is Jim Hobarth, just for reference's sake.

Hobarth refers to his own agency as "Yankee Stadium" and "playing in the pros."

Among the tactics Hobarth uses to recruit Draper include a new set of golf clubs, a membership at an exclusive country club, and recruiting Draper's wife Betty to do some modeling for his agency. Hobarth then sends Draper pictures of Mrs. Draper, in her modeling glory.

That's when Draper calls to turn Hobarth down. When Hobarth again brings up the "big leagues" metaphor, Draper quickly responds with words to the effect of, "That last move wasn't pro at all."

Yes, there was a point. Playing professional, whether you're the Draper or the Hobarth, is extremely important when you're in a recruiting relationship.

I know most of us won't be giving or getting golf clubs and exclusive gym memberships in our search to make meaningful employment relationships, but it's all offering the job and taking the job for the right reasons. Play professional, and you'll attract other professionals to play on your team.

LinkedIn - MySpace for Working Folks

2008-10-10T11:38:00.000-07:00

My last post briefly touched on Facebook as a tool for networking. Yes, I am endorsing it over MySpace, Friendster, and anything else. If that feels kind of like endorsing the Yankees, I can live with it. When I'm looking to get in touch with my old classmates, colleagues, and friends, economy of scale is a beautiful thing.

On a separate, related note - how to find and network with former and current colleagues. For this purpose (purely professional) I love LinkedIn.

Why? Granted, it's not as extensive or personal as FB, but what I like about it is the ability to recommend and get recommendations from people you worked with, or people you currently work with.

What if you were an employer and you were trying to get some background on a potential employee? Which would you rather know - someone's religious beliefs, their favorite books, and their friends names; or would you prefer to hear recommendations from people in a professional context?

That sounds like a no-brainer to me...

The answer is that the two sites can peacefully co-exist, and I hope they stay separate and distinct. While I certainly have a fair amount of colleagues as friends on Facebook, it's because I'm friendly with them outside of the normal work context. LinkedIn I reserve for people I've actually done work-related projects with. Just a personal guideline.

Then again, I'm one of those people who thinks that while you can be friendly, cordial, and respecftul to your boss or your direct reports, it's a really bad idea to be friends outside of work with your boss, or your direct reports. Again, just a personal preference. Do with it what you will.

Facebook - MySpace for Grownups

2008-10-10T11:16:00.000-07:00

I've been on the hunt for a way to get back in touch with some of my classmates from Santa Clara University (class of 92), and from De La Salle High School (class of 88). Not to mention, in this economy, and with my eclectic skill set, I've had to move jobs a few times in the last 20 years.

That's just the tip of the iceberg. But you can see where the rest of the iceberg is going - it's hard to keep in touch with people when you move around and change lifepath.

Years ago, I heard on the radio about a social networking site called "Friendster." I decided to give it a try; I figured, you know, what the hell.

Quickly I found that my inbox was stuffed with messages from women who only gave their first name, and the subject line was always the same - just the word "hey." I thought that was kinda weird.

When I went to the Friendster site to further investigate, I noticed that most of the messages either had no picture, or the picture was the same person two or three times, with different names.

You guessed it - Friendster got pornitrated. Look it up on Urban Dictionary, but it basically is a word I invented to describe when a site gets overrun by porn spammers - the "come see my webcam" kind of garbage.

What about MySpace, you ask? Oh, that was even more fun...I was told by a family friend that I could find people who went to my high school - good way to get in touch. Sounds good, right?

Remember when you get advice to always consider the source. Kristina is a smart young lady with a good head on her shoulders. But let me clarify - she's 21 years old!

So she was right. I did in fact find people who went to my high school. But most of the people who listed De La Salle as their school were still going there.

OK, that's just creepy. 38 year old guys need a different site than 18 year old ones. Bye-bye, MySpace.

And then, my best friend from high school wrote me to tell me of Facebook - there were actually people from our class on there.

And people from my office. And people from my college - who graduated with me.

And I can play late-night games of Scrabble against my boss, share recommended reading with co-workers, and get notified when my favorite musicians are playing in my area.

Don't get me wrong, it's not perfect. Still some get-rich-quick spammer scammers on there, still some people whose energy exceeds their intelligence. But on the whole, I'm pretty pleased. I've managed to find some old SCU classmates I haven't seen since I left, and some found me.

You know what's nice? When you get a friend request from someone you respect and really like. That's happened several times. After we went to my wife's high school reunion, a few of her friends sent me requests. Felt like a validation of sorts.

Sure, some people just try to artificially inflate their friend count. But I just use it to keep in touch, and hopefully network with some people.

Try it. You might like it.

Why VBA?

2008-07-08T10:39:00.000-07:00

I have the great fortune, if you want to call it that, of being the Microsoft Office expert in my office. This is yet another one of those accidental tours of duty that I never saw coming, but I sort of landed here without knowing how.

(The most likely answer, by the way, is that I'm an Office geek instead of an Office user. What's the difference? Office users use the application out of job necessity, learn new application features out of that necessity, and stop when the necessity does. Office geeks use and learn the application out of an obsessive curiosity, and never know when the hell to stop. But I digress.)

Based on having been anointed said expert, I saw it my sacred duty to beta test Office 2008, so that I could warn my peers about what was ahead for them. (I got the little key fob and the copy of Office 2008 to prove it.) Before I go any further, let me say that this entry will do nothing to slam the Beta team; Derek and the rest of the guys and gals were wonderfully receptive and supportive the whole time.

As anyone who knows Office 2008 knows, Microsoft made the business decision to remove VBA from it. And as anyone who knows me knows, I make a large part of my living using VBA in Excel.

And I'm not going to add my voice to the sound and fury in response to Eric Schweib's blog about this subject, which you can find here.

Instead, I'm going to ask a different question:
What did we need VBA in Excel for in the first place?

Speaking for myself, it's because my various and sundry applications that I have to use don't do what I want them to, so I have to use Excel to scrub and unify that data. Granted, I can do most of the aforementioned scrubbing with some rather lengthy formulas, but macros automate even the process of inserting said formula and AutoFilling it down, thereby resulting in one less step.

Ugh.

I start to think sometimes that keeping VBA in Excel up until Excel 2004 enabled people like me to create unsustainable, house-of-cards processes that were doomed to fail eventually anyway, and taking away VBA might be the best thing for us.

On the other hand, I don't have anything cross-platform to replace it with, because I work in a mixed platform shop.

But I guess what I'm really wondering is, "Where's the VSTO for the Mac?" What I really need is the ability to create custom applications that can "hook" into Microsoft Excel, and SalesForce, and other applications.

Anyone?

Leftovers, Microwaves, and Other Unlikely Inspirations

2006-11-23T12:14:00.000-08:00

I brown bag my lunches. My lunches usually consist of leftovers from the night before. I reheat those leftovers in the microwave at my office.

None of those facts are unusual, I grant you. I'm sure that previous paragraph makes me like thousands of others of office workers throughout the United States.

What I wanted to talk to you about is unusual inspirations in unexpected places.

Instead of buying lunch meat, or bread, or other traditional lunch items. I re-use what I already have. That's lesson number one: re-use existing resources as much as possible. Lesson 1.25 is to be willing to change your traditional notions to get results. Lesson 1.5 is to find resources that are inherently reusable - like plastic containers that you can wash and reuse.

When I put my food in the microwave the other day, I was struck by an unusual characteristic of that microwave's interface - instead of having to punch in "2" then "00," then "start," to get the microwave to cook my food for two minutes, all I had to do was push the number "2," and my food started cooking. That's lesson number two: look for ways to make your interface easier to use.

Let's put all of those lessons together now in the context of Excel, which has become my bread and butter of late.

Lesson 1
The first lesson, as you may recall, was "re-use existing resources." The implicit lesson there is that we have to find ways to create re-usable resources in the first place.

Now, a lot of people out there will say a sentence like this: "I have a template that I can email you to use to fill out your expenses."

Tell me how many of those templates end in "xls." If they do, they are not a template. They're a workbook in template's clothing.

A true template should be saved in "xlt" format, for starters. In addition, make sure that your templates have the following:

If there's a formula in a cell, make sure it's protected.
If you require a certain type of input in a cell, consider validation, or a userform. (I primarily develop and use a Macintosh right now, and as most of my colleagues and community members who use Mac Excel will tell you, most ActiveX controls choke on a Mac. So know your audience's platform.)
Consider a hidden worksheet to contain all of your lookups.

The most critical thing is the "XLT" part. The rest is gravy. Furthermore, if you use Windows, consider this idea: Put your re-usable template on the company's Intranet, particularly in a location where it's readily available to your colleagues. Furthermore, you can put the file in a WinZip executable, and set the file to be extracted to C:\Documents and Settings\[user]\Application Data\Microsoft\Templates\.

Lesson 2
Lesson 2 was ease of use. Remember that with my microwave example, I needed push only one button to get my desired result. Ways you can make that happen in your Excel environment include, but are not limited to:

Put some code to fire on the Auto_Open() event of the workbook. For example, I've written code to load a toolbar button when the workbook opens. In other cases, I have the code just automatically run on the Auto_Open() event. In still other cases, I check to see if a cell which is out of the print area and out of sight has a value.
Seek to eliminate the amount of manual input, jumping through hoops, and tweaking that your end user has to do. If your VBA code can figure out the answer for them, don't make them figure it out. If you need the current date inserted in a particular cell, do something like this:
Private Sub Auto_Open()
Range("A5").activate
If len(A5)>0 Then
Activecell.Value=format(Now(),"mm/dd/yyyy")
End If
Simple stuff, but it helps.
Don't restrict your thinking to just VBA and Excel; see if there are other resources available in your company to get the data to your end user without any manual intervention on their part - the "other resources" in my case are AppleScript to open a page, download the Excel file on that page, and then a Unix shell script to copy that file to a specific folder.

Insert this idea into your thought process, too: What happens if your user accidentally deletes a file that your code depends on? What happens if they enter in a value you can't use? In other words, what happens if they kick the legs out from under one of your assumptions? How are you going to handle it? In some cases, you can prevent it from happening in the first place; in others, you're going to have to write error handling code.

Remember, our main charge as the Microsoft Office experts in our office is to make our customers' lives easier, not to make our jobs more secure. Act accordingly.

Next time we meet, I'll surprise us both. Until then, be well.

Getting it Right, Part 4.1: Microsoft Outlook (Email)

2006-08-20T01:15:00.000-07:00

Keeping with the previously established tradition, let's get it out on the table what Microsoft Outlook is good for:

Sending, receiving, storing, and managing email. That sounds pretty obvious. But I'm including it here anyway, just to gain agreement with my readers on the boundaries here.
Scheduling meetings and events. More on that later, but if you work in an office that runs Microsoft Exchange Server, Outlook is about all you need to make sure that meeting announcements get to the right people.
To-do lists.

Again, to be consistent with my ongoing tradition, what is Outlook not good for? A few things jump right out at me:

Outlook sucks as a project management tool. I can remember one very specific job I had a few years ago as a programmer. My Project Manager would use Outlook to assign tasks to people. Sounds good on the surface, but the reality was, Dave had nowhere he could look to see the big picture; how what I was doing was related to Brad, or Steve, or Frank. That was the hugest problem. Not to mention, if we had to bill a client for services, there really wasn't any way to create meaningful invoices. In answer to your next question, Microsoft Project.
Outlook is mediocre as a mail merge data source. I've found, after multiple trials and errors, that when I have my contacts stored in Outlook, and I try to use that contact database as a data source in Word, then I don't have access to all the contact fields. Port it into Excel, and all is well. (For my money, though, I'd probably choose Access. That'll be my final Getting it Right topic, which will probably be a while.)
Outlook is weak for making organization-wide forms, for such functions as time off requests, expense reports, and the like. The scripting language behind Outlook is kind of weird, and not very intuitive. For my money, making forms that people can use could be either done via an Access client-server application, or via a company Intranet, with web forms.

Now, for the best Outlook practices:

Let's start with e-mail. E-mail is all about folders. I would recommend that folders should have a logical, intuitive structure. I have a client who works for an engineering firm; I recommend to people like her to put emails in folders by client name; in the case of my boss, he has one folder for each of the techs he supervises. Those are both logical, intuitive strategies.
E-mail part 2 - when you get an e-mail whose sole purpose is to say, "Hi Geoff, here's the attachment you asked for," you have a ready-made way to save space on your account. Right-click on the attachment, and save that attachment to the proper folder on your hard drive. That's part one. Part two, delete the email. Otherwise, you're hogging up server space needlessly.
Emails should be brief and to the point. If you have a long, involved process to describe, you're probably better off sending the process as an attachment (like a Word document) rather than let the email do the work.
On emails - grammar and spelling still matter, I don't care what anyone says. And yeah, spell check is all well and good, but it won't prevent malapropisms and grammatical errors. One of the most grievous malapropisms I ever saw was when a manager a few years ago happily reported that "Linda excepted a position..." Yeah, spell check let that one go through. As well it should have. But the person sending it should not have.
On email options in Tools->Options that make sense:

Tools->Options->Spelling and Grammar - check "Always check spelling before sending." As mentioned earlier, this won't solve everything, but a good place to start.
Tools->Options->Mail Format. Make at least two signatures - one internal and one external. My internal signature includes my cube number, my AIM address, and my extension. My external signature is pretty much prescribed to me by company policy. But that's OK.

On email attachments - don't just double-click on them to open them. Bad idea. Instead, right-click on the attachment, and then save the attachment somewhere useful, like My Documents. Problem is, if you just open the document, it gets saved in your TEMP directory, and is almost impossible to find later.
On email templates - some of you may know that you can create a template in Word, or Excel, or PowerPoint. But what about Outlook? Can do. Create a message with a subject line, intended recipient(s), and body. Do not include attachments. Trust me, those don't work.
Once you have the skeletal framework in place, in the message, go to File->Save As. Choose the file type "*.oft."
Put the file where prompted - probably C:\Documents and Settings\[user]\Application Data\Microsoft\Templates. That's OK.
To use that template, click on the "File" menu from Outlook, and click on "Choose Form." Click on "Personal Forms Library." You'll see your template.

More on Outlook next time; we'll talk Calendar. Until then, be well.

Bernanke vs. Bush

2006-07-01T14:47:00.000-07:00

This is not a supreme court case - fear not.

This is a catch phrase that I've developed to describe the situation that folks like me, who are known in our office as the person most knowledgeable in Excel, or Office, find ourselves in often.

Back up - Ben Bernanke is the chief of the Federal Reserve. By Bush, I am referring to our President.

Put politics aside for a second, and ponder this: For those of us who are expert at the art of numbers crunching, data analysis, and reporting (Access/Excel/both), we will often be called upon to answer a question like this: "Does it make sense that home sales mean more than gas prices in this report?"

There's a fine line we walk there - is it really ours to answer that question?

Usually, the answer is no.

Most of the time, when we get asked that question, the best way we can answer is the way I answered a colleague of mine who asked a similar question. (The details are confidential, and the questioner shall remain anonymous.) But when this person asked me, in essence, "Does it make sense that when looking at our economic stability, to put more emphasis on home sales than gas prices?" the way I answered was this: "This is where Bush meets Bernanke."

The analysis of what the numbers say is our job. The conclusion of what the numbers mean is a job for the policy makers.

To be a truly effective analyst of data, I feel it my personal duty to maintain neutrality on the issue of what the numbers mean. I concentrate on what the numbers are.

By saying that, I am not shedding the responsibility of reporting the numbers fairly and accurately. But our responsibility as analysts is to show what is there; the policy makers' job is to show what is possible.

We can get in on the policy game if we wish, but we necessarily leave behind the objectivity and remove that made us good at data analysis. In other words, Alan Greenspan would make a lousy president.

Getting It Right, Part Three: PowerPoint

2006-06-11T16:23:00.000-07:00

This is probably going to be a shorter blog entry than some of the others.

In keeping with the tradition I've started, let's get it out on the table what PowerPoint is good for:

Presentations. Big surprise there. That's what people Have been using Powerpoint for since its inception.
Signs. No kidding. If I want to make a sign that has some pizzazz, via borders, themes, and font markup, PowerPoint is actually pretty good at that.

Now, what is PowerPoint not so good at? Interesting question. I guess the best answer to that is "everything else." But seriously, folks, where I would advise against using PowerPoint:

Making charts. Hold that thought, but for now, let's just say that Excel is better.
Large, complex tables of information. I'd probably go Excel for calculations, Word for pure display of data.

Where would a Getting it Right blog entry be without the best practices? Well, glad you asked. Here are my best practices for PowerPoint. These will concentrate both on the technical aspects of putting together a presentation, and the tactical aspects of giving the presentation:

Don't go too crazy on the animation, including slide transitions. My advice would be to use one transition style, and use that transition only in the beginning and end of your presentation.
In a long presentation, use the Title style of slide to signify the beginning of a chapter. That's an appopriate place to use transitions, too.
Using music in presentations is dangerous at best. Remember - if the music is more interesting than you are, you've got trouble.
Three bullet points per slide is best. If you have to break up five bullet points, then put three on the first slide, two on the second. If you have only four bullets, you can get away with it.
If you're going to have one indented bullet point

Like this
Then make sure to have at least two. One looks silly.

Capitalize at least the first word of the bullet point.
Speaking of capitalization, I want to transition into case. Title Case is Best for the Title of a Slide, while Sentence case is best for bullet points and body text. Notice the difference?
Two fonts for a whole presentation is about right. One font for the title text, one font for the body text.
Which two fonts? I like a serif font, like Times New Roman or Georgia for my titles, with a Sans Serif font like Arial or Verdana for my body text. Notice that all the choices listed are simple, fairly straightforward fonts. That's the way I like them.
Using graphics is a good idea, as long as they're high-quality. Low-quality graphics make you look low-quality, too.
Slide masters are your best friend. Deviate from them only when absolutely necessary. Slide masters create consistency and cleanliness.
If you have to put a chart into your presentation, create the chart first in Excel, and then paste the chart into PowerPoint. That way, you can get all the data that underlies the chart nailed down. Make sure to get the colors right in the chart first, before copying it.
Speaking of colors, here's a little tip for you - if you have a company logo, or specific set of colors that you have to match against, go to http://www.download.com, and download a color picker application. I would just say search for a free one appropriate for your OS, and go from there. That way, you can get the hexadecimal values for any color on that picture, whatever it may be.
What good is that, you might ask? Well, as we all know, PowerPoint allows us to change the color of any element on the slide, from the AutoShapes to the background. When you first see the list of colors, it'll be the usual 56 suspects. But click "More Colors," and you get two choices. Choice number one is to use one of 256 Web-safe colors, which are located on the "Standard" tab. Good news is that the colors are easy to pick out. Bad news is, if you're trying to match to a specific color, it's painfully tedious. That's when you go for choice number two - "Custom." When you click on this tab, you can choose a color by its hexadecimal value instead. Make sure that the color picker application of your choosing can pick a color in terms that PowerPoint understands. Bonus feature would be if you can copy the hexadecimal values to the Clipboard.
Spell check your presentation before you save it. PERIOD!
Go to Tools, Options, Edit. Change the maximum number of undos to 99. You're welcome.

Now, as promised, a few words on mechanics:

Always arrive at your presentation location one hour before presenting.
Know where restrooms, electrical outlets, eateries, and water are located.
Know who your contact is if you have technical problems. This is especially important if it's not your equipment.
If you're bringing your own projector, bring a spare bulb as well. The only thing more expensive than a spare bulb is a lost presentation.
Check your teeth, your clothes, and your shoes before you present. And after lunch.
I don't condemn people who smoke. But I'd recommend against it the day of the presentation. With all due respect, smoke on your clothes keeps you from being approachable.
Print out an outline. Keep it on the lectern with you. Refer to it, but don't read it.
Most importantly of all - if you forget absolutely, positively, everything else I tell you, remember this. DO NOT READ DIRECTLY FROM YOUR BULLET POINTS!!!!!

Bullet points are for reference, for you and the audience. For you, they cue you what you're going to say next. For the audience, they give you a sense of what's coming next.

When closing your presentation, end with a title slide. That title slide should have one of several things on it:

A humorous quote. I like Mark Twain better than anyone else for this. Who else could say "Outside of a dog, a book is a man's best friend. Inside a dog, it's too damn hard to read."
An inspirational quote. My personal choice is Dr. Martin Luther King, Jr.; too many to name, but start with "Everyone can be great, because everyone can serve."
A quote from a person respected in your profession, or that your common group can agree on. If you're presenting before a religious group, then a quote or verse from your belief system is a good way to go; if you all work for the same company, then a quote from the founder or the CEO would be appropriate.
Sidenote on that - if you are a religious person, I would advise against a religious quote, unless your crowd is entirely of your belief system. Otherwise, you risk offending someone who is not. Same goes for political quotes. If I'm going to quote a politician, I usually go for someone respected on both sides of the aisle, like JFK, Franklin D. Roosevelt, or Churchill.
A call to action is effective, if your presentation is action-oriented. Buy my product, join my club, take on my point of view. Again, as appropriate.
If you're absolutely out of ideas, the best way to end a presentation is with a title slide that just has your contact information, and something to the effect of, "Please feel free to contact me with any questions." That works.

The bottom line, my friends, is that PowerPoint is probably the easiest application in the Microsoft Office suite to learn. It also comes with the greatest pitfalls. It's easy to get caught up in the animation, transitions, and fanciness. Don't. Get it out of your system before you present, or you'll learn to regret it. PowerPoint gives you the best chance to put yourself in public, but also, the best chance to make a fool out of yourself in public. Don't let it. You're better than that.

The next blog will be on Outlook, which will be a little bit of many pieces. Myths will be dispelled, and truths will be revealed. Until then, be well.

Getting it Right, Part Two: Microsoft Excel

2005-10-03T22:53:00.000-07:00

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

2005-08-20T15:20:00.000-07:00

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

2005-08-06T20:20:00.000-07:00

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:

Misspellings
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)
Incorrect page numbers
Missing pages
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!

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.
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.
As mentioned in #2, you should have a TOC, an index, and a title page.
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.
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).
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.
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.
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.
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.
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

2005-07-23T15:21:00.000-07:00

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:

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.
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.
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.
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.
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.
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.
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.
Never badmouth the company you're leaving; you never know when you may have to go back and ask for a job.
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.
Most of all, remember that you are unique, valuable, and important. But you're neither indispensable nor replaceable. Act accordingly.

Outlook tasks vs. Project

2005-07-07T20:44:00.000-07:00

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

2005-06-19T23:24:00.000-07:00

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:

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.
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:

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

2005-06-16T23:01:00.000-07:00

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?"

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.
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.
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:

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.
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.
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?

2005-06-15T22:10:00.000-07:00

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:

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?
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.
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.
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

2005-06-14T21:17:00.000-07:00

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

2005-06-13T20:33:00.000-07:00

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.