Leftovers, Microwaves, and Other Unlikely Inspirations

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)

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

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

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.