You use Excel for analyzing data, getting data from the Web, building formulas and functions, and even creating calendars. So why don't you know about these tips I'm about to bestow upon you? Well, never mind that now; after this column, you'll have no excuse for not being closer to Excel nirvana.
Skip through your worksheets with a shortcut
When I'm working on many programs at once (yes, of course I'm a multitasker — what did you expect?), I use the keyboard shortcut ALT+TAB to move between open programs. (If you haven't tried this, do so now — it's a great Microsoft Windows® trick.)
However, this doesn't work when you're moving between worksheets in the same workbook. Don't despair; there are a couple of neat little shortcuts that handle the job quite nicely.
To move one worksheet to the right
To move one worksheet to the left
Now you can skip through those worksheets with lightning speed and amaze your friends and coworkers (if they're the type to be amazed by that sort of thing, of course).
Stretch out: Insert a line break in a cell
In most other Office programs, to move the cursor to the next line, you press the ENTER key. However, in Excel — the program that dances to its own tune, and doesn't give a whit about lines or paragraphs — you end up in the cell below when you press ENTER. This is the cause of much frustration in my readers, so I thought I'd let you in on the not-so-secret secret.
To insert a line break in a cell
- Press ALT+ENTER to start a new line while you're typing or editing data.
Ahhh...now you have room to breathe in that tiny cell.
Make a style statement with worksheet tabs
If you have a workbook with loads of worksheets and you're having trouble remembering which one is which, you can customize the little tab at the bottom of each worksheet to identify it in a special way, with a new name or color. It's like getting to design your own nametag instead of having to wear the standard, white Hi My Name is..... tag.
Note You must be using Microsoft Office Excel 2003 or Excel 2002 to color a worksheet tab.
To color one sheet
- Right-click the tab you want to color, and then click Tab Color on the shortcut menu.
- Go wild.
To color all sheets in the workbook
Right-click any worksheet tab, and then click Select All Sheets on the shortcut menu.
- Right-click the tab again, and then click Tab Color on the shortcut menu.
Again, go wild.
To rename a worksheet
What would Westerns and action films be like today if Marion Michael Morrison hadn't changed his name?
- Right-click a tab, and then click Rename on the shortcut menu.
P.S. John Wayne would thank you for assigning an appropriate name to your macho worksheet.
A change of scenery: Move or copy a worksheet
You may know that you can just drag and drop a worksheet to a different spot in the workbook. But for you folks who get joy from dialog boxes (and truthfully, this one gives you more options), here you go:
To move or copy a worksheet
- Right-click a tab, and then click Move or Copy on the shortcut menu.
- In the Move or Copy dialog box, you have options:
- To move the worksheet to a spot within its own workbook, select a sheet in the Before sheet box.
- To move the worksheet to another open workbook, select the workbook in the To book list and then click OK.
- To keep a copy of the worksheet you're moving in this workbook (rather than just moving it), select the Create a copy box.
Double-click your way to fast formatting
By default, when you copy text from a different program (such as Microsoft Word, Microsoft PowerPoint®, or even Microsoft Internet Explorer) and paste it into an Excel cell, the original formatting comes along for the ride. Sometimes this leaves you with a tiny little cell with GIGANTIC FORMATTING that you need to fix by hand.
The Paste Options button that looks like this:
pops up, and you can use it to keep the original formatting or to match the destination formatting (that is, the formatting you've chosen for your cell). However, if you're like me (or want to be like me — aren't you sweet), that is just too many steps if you know that you want to match the formatting of the cell. This is especially true if you have a lot of cutting and pasting to do.
To paste text from another program and keep your cell's formatting
- Select the text you want to pop into your Excel worksheet.
- Press CTRL+C.
- Switch back to Excel.
Tip Hey! Now's a good time to use that shortcut I told you about in the first tip!
- Double-click in the cell, and press CTRL+V.
It's like magic in a click (or two).
And so you have it. Five decent little tips to boost your skill level and keep your work humming along. So start the music!