Outlining Your Data

Large worksheets and long lists of data can quickly become unmanageable, but there's a Microsoft Excel tool that can help you get control. Outlining lets you group data in a worksheet into different levels and hide or display the levels as you choose. You can take a high-level view of the final totals of your calculations or drill down to view the data in more detail. We'll take a look at how to create an Excel outline, and we'll also explain how to use the Subtotals command to produce outlines automatically for some worksheets.

To understand outlining, consider a monthly budget with expenses listed down the left side of the worksheet. The months of the year appear across the top, with each group of three months followed by a quarterly subtotal. The final column contains the totals for the year.

This worksheet contains at least 18 columns of data and totals, so it will occupy multiple screens horizontally. By using an outline, you can collapse and hide everything except the area you're working on, which lets you focus your attention on the figures and their results.

To outline the worksheet, select the columns for January to March and choose Data | Group and Outline | Group. This groups the three columns so they can be displayed or hidden, using the expand and collapse indicators that appear above the worksheet.

Repeat this process with the months April to June, July to September, and October to December. When you do this, + and keys appear above the groups so you can expand and collapse each one individually. Click on the level buttons above the top left corner of the worksheet to show or hide all grouped data in one step.

You can group the groups to create a higher level of summarization. To do this, display and select all the columns from January to the fourth-quarter summary, and again choose Data | Group and Outline | Group. You'll now see three level buttons above the top left corner of the worksheet, instead of the two that there were there before.

Click on button 1—the highest level—to summarize the worksheet to show only the annual total. Click on 2 to show the four quarter subtotals and the annual total, and click on 3 to expand the worksheet to show all the data. The + and buttons let you show or hide the individual groups. You could, for example, display data for January to March and the quarter and annual totals while you study January's figures. This makes the worksheet more manageable both for editing and for analysis.

Outlining can be even easier. If your worksheet is arranged in a hierarchical format with subtotal formulas, there's a good chance Excel can do the job for you automatically. Just choose Data | Group and Outline | Auto Outline and see what happens. If this doesn't give you what you need, you can create the outline manually.

Depending on the data you're working with, you can take advantage of the outlining behavior of the Subtotals command to create subtotals and an outline in one step. This works well on the kind of list where in one or more columns there are repeated entries that you want to group together.

For example, a worksheet recording the daily sales details for a number of stores for a given month will include multiple entries for each date—one for each store open on a certain day. A particular store name will appear multiple times—one for each day the store is open. This worksheet could be grouped by date or by store.

Start by sorting the data on the column you want to focus on. To total the month's results for each store, for instance, sort into store order. Then select the table and choose Data | Subtotals. In the Subtotal dialog choose the column to group by—in this case, the one containing the store name. Then choose the function to calculate—in our case it would be Sum. From the Add subtotal to drop-down list choose the field to sum, which in our case would be the day's sales. Enable the Summary below data checkbox and click on OK.

Excel groups and outlines the data automatically and adds subtotals for each group and an overall total. You can collapse and expand the data exactly as if you had created the outline manually. If you sort the data again, the subtotals and outlining disappear, but, as you can see, repeating the process is very simple.

Handy Keyboard Shortcuts in Microsoft Office

If you're a Microsoft Office user who touch-types, you might prefer the keyboard to the mouse for entering commands. Odds are, though, that you wind up using the mouse, because Windows and Office keyboard shortcuts are difficult to remember, and trying to find them is a little like searching for the hidden goodies in an adventure program. Once found, though, shortcuts can prove invaluable time-savers.

This is not a list of every shortcut in Windows and Office. We've concentrated on those we've found most helpful. We won't delve into the shortcuts you probably know already, such as those that copy (Ctrl-C), cut (Ctrl-X), or paste (Ctrl-V) objects.

Don't try to learn all these key combinations at once. Pick a few that fit the way you work and use them for a while until they're automatic, then try a few more. To help you remember, we've added mnemonics where we could, along with notes where appropriate. All these shortcuts work in Windows 95, 98, Me, NT 4.0, and 2000, except where noted. Some require a keyboard with a Windows key (the key with a flying Windows icon on it). Windows-key shortcuts are among the most useful—and most overlooked.

Moving Around Windows and the Desktop

Shortcuts that let you move from one window to another easily, get to the Windows desktop, or move around the desktop can speed you up considerably.

Switch among applications: Windows-Tab or Alt-Tab.

The Windows-Tab combination cycles through the taskbar buttons. When the program you want is selected, hit Enter to switch to that window. If more than one program is running, Alt-Tab brings up the task-switching window. Immediately releasing the keys switches you to the previous application. To jump to one of the other running programs instead, release Tab, but keep Alt held down. Each succeeding press of Tab moves the program selection box to the next application. Releasing the Alt key switches you to the selected program. If only two applications are running, Alt-tab toggles between them.

Open the Start menu: Ctrl-Esc or Windows.

Either shortcut will open the Start menu. Use the Up and Down Arrow keys to move through the menu, and the Enter key to choose an item. The two shortcuts vary slightly depending on your version of Windows. Ctrl-Esc, Esc leaves the Start button selected but not pressed. In Windows 95, 98, and NT 4.0 (but not Windows 2000 and Me), Windows, Esc will return you to the window or desktop selection you were working with previously.

Go to the Quick Launch toolbar and launch a program: Ctrl-Esc, Esc, Tab, select with Arrow keys, Enter.

Minimize all open Windows and reveal the desktop: Windows-M or Windows-D.
Mnemonics: This one's easy to remember; M for Minimize all and D for desktop. Note that Windows-D doesn't work under Windows 95, but Windows-M does.

Restore all Windows you previously minimized with Windows-M or Windows-D: Shift-Windows-M or repeat Windows-D: Shift-Windows-M or repeat Windows-D.

Move within the Windows Desktop and select items: Tab, Arrow, Enter.

Once at the desktop, use the Tab key to cycle through the Start button, Quick Launch toolbar, other toolbars, taskbar button area, and icons on the desktop (and those in the system tray in Windows 2000). Use the Arrow keys to move around within any of these areas of the desktop or taskbar. Use the Enter key to select items. You must, for example, select the Start button to open the Start menu.

Windows Housekeeping Chores

These shortcuts can help you with day-to-day housekeeping in Windows.

Open the Run dialog box: Windows-R.
Mnemonic: The Windows Run dialog.

Start Windows Explorer: Windows-E.
Mnemonic: Windows Explorer.

Find a File (from the Windows desktop): Windows-F or F3.
Mnemonic: Windows Find file dialog box.

Open the System Properties dialog box: Windows-Break.
Mnemonic: Windows is broken; check the system properties.

Rename the selected object: F2.
Mnemonic: This is the same command Excel uses for editing the currently selected cell. This command and the next one work within programs, too. For example, you can use these commands when working in the File | Open dialog box in Word or Excel. Note that after you select text in Word, F2 begins the operation of moving the current selection. You then move the insertion cursor to the desired location and hit Enter.

Delete selected objects without sending them to the Recycle Bin: Shift-Delete.
Mnemonic: A slightly shifted version of what happens when you hit Delete, which sends the objects to the Recycle Bin. Be careful with this command. Under some conditions, it will delete a file without asking for confirmation first.

Bypass the CD-ROM AutoRun feature: Hold down the Shift key while you insert the disk.
This is an invaluable tool when you have to remove a disk during installation (to read the CD key, for example), then reinsert the disk.

View the Properties dialog for a selected object: Alt-Enter.
This works for such disparate objects as icons on the desktop, printers, hard drives, and the taskbar.

Windows-Wide Shortcuts

The shortcuts in this category work not only in Windows itself, but also in most Windows applications. Some of these shortcuts relate to the windows of a particular application. Others relate to features you'll find in almost any Windows program, such as drop-down list boxes.

Restore, Move, Size, Minimize, Maximize, or Close the main window of the currently selected program: Alt-Spacebar, letter key (from those underlined above), or Alt-Spacebar, Arrow, Enter.

Alt-Spacebar opens the System menu, which will appear on-screen even if the application window is mostly off-screen. You can move the window back to a workable position using the Arrow keys and then hit Enter to set it down.

Restore, Move, Size, Minimize, Maximize, or Close the currently selected window within a program: Alt-hyphen, letter key, or Alt-hyphen, Arrow, Enter.

Open a context menu: Shift-F10, letter key, or Shift-F10, Arrow, Enter.

This is particularly useful in a program like Word when, for example, you want to call up the editing context menu but don't want to take your fingers from the keys to right-click.

Open a drop-down list box: Alt-Down Arrow.
Mnemonic: Down box, Down Arrow. This is especially helpful when you're filling in database forms (in Microsoft Access, for example). It's also useful when working in a dialog box.

Cycle through the tabs in a dialog box: Ctrl-Tab and Ctrl-Shift-Tab.
Mnemonic: Control your way from tab to tab. Ctrl-Tab goes from left to right, Ctrl-Shift-Tab moves from right to left.

Switch from window to window within the same program: Alt-F6.

This won't work with all windows. It will, for example, toggle between a Find window and a document window in Microsoft Word, but it won't toggle between one document window and another. The command in Word for cycling through the open document windows is Ctrl-F6.

Basic font formatting for bold, underline, italic: Ctrl-B, Ctrl-U, Ctrl-I.

You probably know these work in the Office programs you use, but try them in other programs as well; they may work.

Undo: Ctrl-Z.
Mnemonic: Zap that. Again, this works throughout Windows, if not in every program.

Microsoft Word Shortcuts

Word offers over 300 shortcuts as shipped, not including the menu shortcuts. This adds up to more shortcuts than any reasonable person would be willing to memorize. There are a few, however, that we've found are worth the effort.

Expand an autotext entry: F3.
If you use autotext very often, you'll get the hang of this one. Simply type the abbreviation (such as your initials for your address) and hit F3 to replace the abbreviation with the fill entry.

Change the case of letters: Shift-F3.
The effect of this shortcut depends on the selected text. The basic behavior cycles through all caps, all lowercase, and title capitalization (the first letter of each word capitalized). If the selection includes a sentence break, the shortcut cycles through all caps, all lowercase, and capitalization of the first word in each sentence.

Check spelling: F7.
F7 by itself performs a spell-check on the entire document. If you select a word or section first, however, F7 checks the spelling of only that word or selection. If you have the Check Spelling As You Type feature turned on, this shortcut probably won't interest you. If you find that feature distracting and keep it off, you might find this shortcut quite useful.

Check the thesaurus: Shift-F7.
Mnemonic: There's not really a good mnemonic, but remembering that the F7 key is associated with both spelling and thesaurus commands might help. Shift-F7 checks the thesaurus for the word the cursor is on or just past.

Update fields: F9.
Mnemonic: Again, there's not really a good mnemonic. All shortcuts dealing with fields use the F9 function key, though. Remember that much and you can, at the very least, experiment with various keystroke combinations. F9 by itself updates the selected fields, if any, or just the field the cursor is in.

Switch between the field code and field result for selected fields: Shift-F9.
Mnemonic: Shift between selected field codes and field results.

Switch between showing all field codes and their results: Alt-F9.
Mnemonic: Show all (Alt) codes or results.

Insert field markers: Ctrl-F9.
Mnemonic: Control what goes into the field. If you know the field codes you need to enter, use Ctrl-F9 to insert both open and close markers for the field quickly, then type your codes between them. Hit F9 to calculate the field result.

Unlink a field: Shift-Ctrl-F9.
Mnemonic: Shift control from the field to the field result. This converts a calculated field result into permanent text or a permanent graphic.

Insert Date field: Alt-Shift-D.
Mnemonic: Date. To insert the date as text, type Alt-Shift-D, Backspace, Ctrl-Shift-F9.

Insert Time field: Alt-Shift-T.
Mnemonic: Time. Again, to make the insertion as text, follow the shortcut with Ctrl-Shift-F9.

Move to the Style drop-down box to pick a style: Ctrl-Shift-S.
Mnemonic: Take Control to shift the Style. Once you get to the Style text box, you can open the drop-down list with Alt-Down Arrow.

Format characters as superscript: Ctrl-+.

Format characters as subscript: Ctrl-= (equal sign).
Mnemonic: Superscript is +. Subscript is the unshifted form of +.

Return font to the default format for the style: Ctrl-Spacebar.
If you depend on styles for formatting, this is one of the most important shortcuts to learn—particularly if you collaborate with others who may not be familiar with styles.

Toggle between showing and hiding nonprinting characters: Ctrl-Shift-* (asterisk).
This is equivalent to clicking on the Show/Hide icon in the standard toolbar.

Move the paragraph up: Alt-Shift-Up Arrow.

Move the paragraph down: Alt-Shift-Down Arrow.
This moves the paragraph containing the cursor or selection one paragraph marker at a time.

Cycle the selected paragraph through all formats in the Style list: Alt-Shift-Right (or Left) Arrow.

Microsoft Access And Excel Shortcuts

Here are two handy shortcuts that work for both Access and Excel. See the sidebar for more.

Enter time: Ctrl-: (colon).

Enter date: Ctrl-; (semicolon).
Mnemonic: You separate hours and minutes with a colon, so use a colon for time. The date shortcut is the unshifted colon key.