Crabby Office Lady

Let's take a peek into the organized mind of a Microsoft software tester and see if we can't glean a few good Excel tips from him. When it comes to efficiency and creativity, this guy has it down.

Maybe it's these hot days of summer that occur so infrequently here in the Pacific Northwest. Maybe it's the sound of that tiny downy woodpecker frantically trying to poke a hole in my front door. Whatever (or whoever) it is, something about this season just screams, "More cool Excel tips!"

Before I commence, I'd like to introduce you to the fine young fellow who came up with these tips for me: James Elder, my main man, my trusty teammate, and the Microsoft Office software test lead who works down the hall from me. Walk into James's office any day of the week (okay, maybe not Sunday — he has a wife and twin baby boys who like to see him once in a while), and you'll see him gleefully pounding away at several computers at one time. He uses Microsoft Excel a lot, and he knows what's what with regard to getting things done fast.

James? Tips, please.

Leave no cell blank

You know how it is: You want to move a cell (or even a whole row) of data to another spot on your worksheet. So you cut it, paste it, and then go back to where it used to be and clean up the big hole it left. Who needs all that housekeeping? There is an easier, more efficient way.

To move a cell and not leave a blank one

1. Select the cell or row you want to move.
• If you are using Excel 2003 or Excel 2002: Rest the pointer over the cell until the white pointer with a four-headed arrow appears:
• If you are using Excel 2000 or Excel 97: Rest the pointer over the cell until the white pointer, by itself, appears.
  
2. Hold down the SHIFT key and drag the cell to where you want it.
3. Let go of the SHIFT key. (Do I have to remind you of everything?)
4. Jump up out of your chair, ecstatic and delighted.

Copy and paste has its place (but not here)

If you have a bunch of cells that all need to have the same data in them, I'll bet you accomplish this task in one of two ways. You either:

• Type the content into one cell, and then copy and paste your way, cell-by-cell, to a repetitive stress injury.
• Type the content into one cell, copy it, select all the other cells where you want the content to be, and paste. Less possibility for injury, but still an ungraceful way to accomplish the task at hand.

Shall we learn a better way? Oh, let's do.

To do a bulk enter and save your hands

1. Select the range of cells that you want to contain the same content.
2. Start typing. (Don't select any particular cell — just type what you want.)
3. Press CTRL+ENTER.
4. Jump up out of your chair, shocked and amazed.

Equality is always best

Excel is smart — so smart, in fact, that it can be a regular know-it-all by trying to anticipate what you want. For example, if you type an equal sign (=) in a cell, Excel automatically assumes that you're about to enter a formula. This is a logical assumption, since that's how all formulas begin. And frankly, Excel has no real-life experience that would clue it in to the fact that some people in this world actually want to use an equal sign in their cell. And so, there is a miscommunication, and you end up screaming at an inanimate object on your desk (and everyone ends up feeling bad).

How about giving Excel a clue that tells it you're not about to create a formula?

To use an equal (=) sign that isn't part of a formula

1. Before you type the equal sign, type an apostrophe: '
2. Then type your equal sign: =
   So right now, the content in your cell will look like this: '=
   
   Note   If you want to add anything after your equal sign, you can do that too.)
3. Press ENTER. (Note that the apostrophe disappears
4. Jump up out of your chair, entranced and energized.

Drop-down boxes: Salvation for long lists and tired fingers

Let's say you're creating a dinner party journal, a worksheet that will note the dietary restrictions/preferences/annoyances of each of your guests. In column A, you have your long and illustrious list of guests (those who have had the good sense to R.S.V.P., a dying rule of etiquette). In column B, you have the list of the various dietary restrictions that apply to one or more of your guests.

If you have a long guest list, imagine how tedious it becomes to type each one's preferences, cell by cell. All you need to do is type the guest list in column A, and, in column B, type, just once, the various dietary restrictions that apply to the first few guests.

Now, instead of having to type one (or more) of the many dietary restriction types next to the remaining guests' names, you can follow James's handy little tip:

To show a drop-down list of content previously typed in a column

1. Select the cell.
2. Press ALT+DOWN ARROW. A list of all previously typed entries appears.
3. Release ALT, and use the UP ARROW or DOWN ARROW key to select the entry you want to apply to that cell.
4. Press ENTER.
5. Jump up out of your chair, celebratory and satiated.

Now that we have that straight, let's all meet at a restaurant....

"Believe one who has proved it. Believe an expert." — Virgil

About the author

Annik Stahl, the Crabby Office Lady columnist, takes all of your complaints, compliments, and knee-jerk reactions to heart. Therefore, she graciously asks that you let her know whether this column was useful to you — or not — by entering your feedback using the Was this information helpful? tool below. And remember: If you don't vote, you can't complain.