<< . .

. 10
( : 51)

. . >>

• Restored: A nonmaximized size. To restore a maximized or minimized window,
click its Restore button.
If you work with more than one workbook simultaneously (which is quite common), you
have to learn how to move, resize, and switch among the workbook windows.
Chapter 4 ¦ Essential Excel Worksheet Operations 83

Moving and resizing windows
To move a window, make sure that it™s not maximized. Then click and drag its title bar with
your mouse.
To resize a window, click and drag any of its borders until it™s the size that you want it to
be. When you position the mouse pointer on a window™s border, the mouse pointer changes
to a double-sided arrow, which lets you know that you can now click and drag to resize the
window. To resize a window horizontally and vertically at the same time, click and drag
any of its corners.

You cannot move or resize a workbook window if it is maximized. You can move a minimized
window, but doing so has no effect on its position when it is subsequently restored.

If you want all of your workbook windows to be visible (that is, not obscured by another
window), you can move and resize the windows manually, or you can let Excel do it for you.
The Window_Arrange command displays the Arrange Windows dialog box, as shown in
Figure 4-2. This dialog box has four window-arrangement options. Just select the one that
you want and click OK.

Figure 4-2: Use the Arrange Windows dialog box to quickly arrange all open workbook

Switching among windows
At any given time, one (and only one) workbook window is the active window. This is the
window that accepts your input, and it is the window on which your commands work. The
active window™s title bar is a different color, and the window appears at the top of the stack of
windows. To work in a different window, you need to make that window active. There are
several ways to make a different window the active workbook:
• Click another window, if it™s visible. The window you click moves to the top and
becomes the active window.
• Press Ctrl+Tab (or Ctrl+F6) to cycle through all open windows until the window that
you want to work with appears on top as the active window. Shift+Ctrl+Tab (or
Shift+Ctrl+F6) cycles through the windows in the opposite direction.
Part I ¦ Getting Functional with Office 2003

• Click the workbook icon in the Windows Taskbar. If you don™t see workbook icons
in your Windows Taskbar, activate the Options dialog box, select the View tab, and
put a checkmark next to Windows in Taskbar.
• Click the Window menu and select the window that you want from the bottom part
of the pull-down menu (the active window has a check mark next to it). This menu
can display up to nine windows. If you have more than nine workbook windows
open, choose More Windows (which appears below the nine window names).

Most people prefer to do most of their work with maximized workbook windows. This enables
you to see more cells and eliminates the distraction of other workbook windows getting in the

When you maximize one window, all the other windows are maximized, too (even though
you don™t see them). Therefore, if the active window is maximized and you activate a
different window, the new active window is also maximized. If the active workbook window
is maximized, you can™t select another window by clicking it (because other windows aren™t
visible). You must use either Ctrl+Tab, the Windows taskbar, or the Window menu to activate
another window.

You also can display a single workbook in more than one window. For example, if you have a
Tip workbook with two worksheets, you may want to display each worksheet in a separate window.
All the window-manipulation procedures described previously still apply. You use the
Window_New Window command to open a new window in the active workbook.

Closing windows
If you have multiple windows open, you may want to close those windows that you no longer
need. To close a window, select File_Close or simply click the Close button (the X icon) on
the worksheet window™s title bar. If the workbook window is maximized, its title bar is not
visible, so its Close button appears directly below Excel™s Close button
When you close a workbook window, Excel checks whether you have made any changes
since the last time you saved the file. If not, the window closes without a prompt from Excel.
If you™ve made any changes, Excel prompts you to save the file before it closes the window.

Making a worksheet the active sheet
At any given time, one workbook is the active workbook, and one sheet is the active sheet in
the active workbook. To activate a different sheet, just click its sheet tab, located at the
bottom of the workbook window. You also can use the following shortcut keys to activate a
different sheet:
• Ctrl+PgUp: Activates the previous sheet, if one exists
• Ctrl+PgDn: Activates the next sheet, if one exists
Chapter 4 ¦ Essential Excel Worksheet Operations 85

If your workbook has many sheets, all of its tabs may not be visible. You can use the tab-
scrolling buttons (see Figure 4-3) to scroll the sheet tabs. The sheet tabs share space with the
worksheet™s horizontal scrollbar. You also can drag the tab split box to display more or fewer
tabs. Dragging the tab split box simultaneously changes the number of tabs and the size of the
horizontal scrollbar.

Figure 4-3: Use the tab controls to activate a different worksheet or to see additional
worksheet tabs.

When you right-click any of the tab-scrolling buttons to the left of the worksheet tabs, Excel
displays a list of all sheets in the workbook. You can quickly activate a sheet by selecting it from
the list.

Adding a new worksheet to your workbook
Worksheets can be an excellent organizational tool. Instead of placing everything on a single
worksheet, you can use additional worksheets in a workbook to separate various workbook
elements logically. For example, if you have several products whose sales you track individu-
ally, you might want to assign each product to its own worksheet and then use another
worksheet to consolidate your results.
The following are three ways to add a new worksheet to a workbook:
• Select the Insert_Worksheet command.
• Press Shift+F11.
• Right-click a sheet tab, choose the Insert command from the shortcut menu, select
Worksheet from the Insert dialog box, and then click OK.
When you add a new worksheet to the workbook, Excel inserts the new worksheet before the
active worksheet, and the new worksheet becomes the active worksheet.

To insert more than one worksheet at a time, hold down the Shift key and click a range of
worksheet tabs. When you issue the command to insert a worksheet, Excel will add as many
worksheets as the number of worksheet tabs you selected before issuing the command.
Part I ¦ Getting Functional with Office 2003

Deleting a worksheet you no longer need
If you no longer need a worksheet, or if you want to get rid of an empty worksheet in a
workbook, you can delete it in either of two ways:
• Select the Edit_Delete Sheet command.
• Right-click the sheet tab and choose the Delete command from the shortcut menu.
If the worksheet contains any data, Excel asks you to confirm that you want to delete the
sheet. If you have never used the worksheet, Excel deletes it immediately without asking for

You can delete multiple sheets with a single command by selecting the sheets that you want to
delete. To select multiple sheets, press Ctrl while you click the sheet tabs that you want to
delete. To select a group of contiguous sheets, click the first sheet tab, press Shift, and then
click the last sheet tab. Then use either method to delete the selected sheets.

When you delete a worksheet, it™s gone for good. This is one of the few operations in Excel that
can™t be undone.

Changing the Number of Sheets in Your Workbooks

By default, Excel automatically creates three worksheets in each new workbook. You can change
this default behavior. For example, I prefer to start each new workbook with a single worksheet.
After all, it™s easy enough to add new sheets if and when they are needed. To change the default
number of worksheets:
1. Select Tools_Options.
2. In the Options dialog box, click the General tab.
3. Change the value for the Sheets in New Workbook Setting and click OK.
Making this change will affect all new workbooks but will have no effect on existing workbooks.

Changing the name of a worksheet
The default names Excel uses for worksheets ” Sheet1, Sheet2, and so on ” aren™t very
descriptive. If you don™t change the worksheet names, it can be a bit hard to remember where
to find things in multiple-sheet workbooks. That™s why providing more meaningful names for
your worksheets is often a good idea.
Chapter 4 ¦ Essential Excel Worksheet Operations 87

To change a sheet™s name, use any of the following methods to begin:
• Choose Format _Sheet_Rename.
• Double-click the sheet tab.
• Right-click the sheet tab and choose the Rename command from the shortcut menu.
After you have done one of the above actions, Excel highlights the name on the sheet tab so
that you can edit the name or replace it with a new name.

To edit the worksheet name rather than to replace it completely, it™s usually easiest to double-
click the sheet tab and then click within the name where you want to make a change.

Sheet names can be up to 31 characters, and spaces are allowed. However, you can™t use the
following characters in sheet names:
: colon
/ slash
\ backslash
? question mark
* asterisk
Keep in mind that a longer worksheet name results in a wider tab, which takes up more space
onscreen. Therefore, if you use lengthy sheet names, you won™t be able to see very many
sheet tabs without having to scroll the tab list.

Changing a sheet tab™s color
Excel allows you to change the color of one or more of your worksheet tabs. For example,
you may prefer to color-code the sheet tabs to make it easier to identify the worksheet™s
To change the color of a sheet tab, right-click the tab and choose Tab Color. Then select the
color in the Format Tab Color dialog box.

Rearranging your worksheets
You may want to rearrange the order of worksheets in a workbook. If you have a separate
worksheet for each sales region, for example, arranging the worksheets in alphabetical order
or by total sales might be helpful. You may want to move a worksheet from one workbook to
another. (To move a worksheet to a different workbook, both workbooks must be open.) You
can also create copies of worksheets.
You can move or copy a worksheet in the following ways:
Part I ¦ Getting Functional with Office 2003

• Select the Edit_Move or Copy Sheet command to display the Move or Copy
dialog box.
• Right-click the sheet tab and select the Move or Copy command. (This also displays
the same Move or Copy dialog box.)
• To move a worksheet, click the worksheet tab and drag it to its desired location
(either in the same workbook or in a different workbook) to move the worksheet.
When you drag, the mouse pointer changes to a small sheet, and a small arrow
guides you.
• To copy a worksheet, click the worksheet tab, press Ctrl, and drag the tab to its
desired location (either in the same workbook or in a different workbook). When
you drag, the mouse pointer changes to a small sheet with a plus sign on it.

You can move or copy multiple sheets simultaneously. First select the sheets by clicking their
sheet tabs while holding down the Ctrl key. Then you can move or copy the set of sheets by
using the methods just described.

Dragging is usually the easiest method, but if the workbook has many sheets, you may prefer
to use the Move or Copy dialog box. This dialog box is shown in Figure 4-4, and it enables
you to select the workbook and the new location.

Figure 4-4: Use the Move or Copy dialog box to move or copy worksheets in the same or
another workbook.

If you move or copy a worksheet to a workbook that already has a sheet with the same name,
Excel changes the name to make it unique. For example, Sheet1 becomes Sheet1 (2).

When you move or copy a worksheet to a different workbook, any defined names and custom
formats also get copied to the new workbook.
Chapter 4 ¦ Essential Excel Worksheet Operations 89

Hiding and unhiding a worksheet
In some situations, you may want to hide one or more worksheets. Hiding a sheet may be
useful if you don™t want others to see it, or if you just want to get it out of the way. When a
sheet is hidden, its sheet tab is also hidden. At least one sheet must remain visible. (You can™t
hide all the sheets in a workbook.)
To hide a worksheet, choose Format_Sheet_Hide. The active worksheet (or selected
worksheets) will be hidden from view.
To unhide a hidden worksheet, choose Format _Sheet_Unhide. Excel opens its Unhide
dialog box that lists all hidden sheets. Choose the sheet that you want to redisplay and click
OK. You can™t select multiple sheets from this dialog box, so you need to repeat the com-
mand for each sheet that you want to redisplay.

To more fully protect a workbook from unauthorized changes, use the Tools_Protection menu
commands. These commands give you several options in deciding how much access other
users will have to the worksheets in your workbooks. Be aware that this is a very weak security
measure. It is relatively easy to crack Excel™s protection features.

Making a Sheet Very Hidden
It™s also possible to make a sheet “very hidden.” A sheet that is very hidden does not appear in the
Unhide dialog box. To make a sheet very hidden:
1. Activate the worksheet.
2. Select View_Toolbars_Control Toolbox. This displays the Control Toolbox toolbar.
3. Click the Properties button on the Control Toolbox toolbar. This displays the Properties box,
shown in the following figure.
4. In the Properties box, select the Visible option, and choose 2 - xlSheetVeryHidden.

Part I ¦ Getting Functional with Office 2003

After performing these steps, the worksheet will be hidden, and it will not appear in the Unhide
dialog box.
Be careful. After you make a sheet very hidden, you can™t use the Properties box to unhide it
because you won™t be able to select the sheet! In fact, the only way to unhide such a sheet is to use
a VBA macro. (See Part VI for more information about VBA.) This VBA statement will unhide Sheet1
in the active workbook:
ActiveWorkbook.Worksheets(“Sheet1”).Visible = True

Controlling the Worksheet View
As you add more information to a worksheet, you may find that it gets more difficult to
navigate and locate what you want. Excel includes a few options that enable you to view your
sheet, and sometimes multiple sheets, more efficiently. This section discusses a few additional
worksheet options at your disposal.

Viewing a worksheet in multiple windows
Sometimes, you may want to view two different parts of a worksheet simultaneously ”
perhaps to make it easier to reference a distant cell in a formula. Or you may want to
examine more than one sheet in the same workbook simultaneously. You can accomplish
either of these actions by opening a new view to the workbook, using one or more
additional windows.
To create and display a new view of the active workbook, choose Window_New Window.

If the workbook is maximized when you create a new window, you may not even notice that
Excel has created the new window; but if you look at the Excel title bar, you™ll see that the
workbook title now has :2 appended to the name. Select Window_Arrange and choose one
of the options in the Arrange Windows dialog box to display the open windows.

Excel displays a new window for the active workbook, similar to the one shown in Figure 4-
5. In this case, each window shows a different worksheet in the workbook. Notice the text in
the windows™ title bars: climate data.xls:1 and climate data.xls:2. To help
you keep track of the windows, Excel appends a colon and a number to each window.
Chapter 4 ¦ Essential Excel Worksheet Operations 91

Figure 4-5: Use multiple windows to view different sections of the workbook at the same

A single workbook can have as many views (that is, separate windows) as you want. Each
window is independent of the others. In other words, scrolling to a new location in one
window doesn™t cause scrolling in the other window(s).
You can close these additional windows when you no longer need them. For example,
clicking the Close button on the active window™s title bar closes the active window but
doesn™t close the other windows.

Multiple windows make it easier to copy information from one worksheet to another. You can
use Excel™s drag-and-drop procedures to do this. In addition, multiple windows are useful when
examining formulas.

Comparing sheets side by side
The Compare Side by Side feature is new to Excel 2003.

In some situations, you may want to compare two worksheets that are in different windows.
A new feature in Excel 2003 makes this task a bit easier. The sheets can be in the same
workbook or in different workbooks.
First, make sure that the two sheets are displayed in separate windows. If you want to
compare two sheets in the same workbook, use the Window_New Window command to
create a new window for the active workbook. Activate the first window; then choose
Window_Compare Side by Side With. If more than two windows are open, you™ll see a
dialog box that lets you select the window for the comparison.
Part I ¦ Getting Functional with Office 2003

The two windows will be tiled horizontally, not really “side by side.” If you prefer a true
side-by-side arrangement, select Window_Arrange, and select Vertical in the Arrange
Windows dialog box.
When using the Compare Side by Side feature, you™ll find that scrolling in one of the
windows also scrolls the other window. When you use this command, the Compare Side by
Side toolbar is displayed. This toolbar contains the following buttons:
• Synchronous Scrolling: Toggles automatic windows scrolling on and off.
• Reset Window Position: If you have rearranged or moved the windows, clicking
this button puts them back in the initial horizontal arrangement.
• Close Side by Side: Breaks out of side-by-side mode and returns to the previous
window positions. You can also use the Window_Break Side by Side command
for this.
Keep in mind that this feature is for manual comparison only. Unfortunately, Excel does not
provide a way to show you the differences between two sheets.

Splitting the worksheet window into panes
If you prefer not to clutter your screen with additional windows, Excel provides another
option for viewing multiple parts of the same worksheet. The Window_Split command
splits the active worksheet into two or four separate panes. The split occurs at the location
of the cell pointer. You can use the mouse to drag the individual panes to resize them.
Figure 4-6 shows a worksheet split into two panes. Notice that row numbers aren™t
continuous. In other words, splitting panes enables you to display in a single window
widely separated areas of a worksheet. To remove the split panes, choose
Window_Remove Split.

Figure 4-6: You can also split the worksheet window to view different areas of the
worksheet at the same time.
Chapter 4 ¦ Essential Excel Worksheet Operations 93

Another way to split and unsplit panes is to drag either the vertical or horizontal split bar.
These bars are the small rectangles that normally appear just above the top of the vertical
scrollbar and just to the right of the horizontal scrollbar. When you move the mouse pointer
over a split bar, the mouse pointer changes to a pair of parallel lines with arrows pointing
outward from each line. To remove split panes by using the mouse, drag the pane separator all
the way to the edge of the window or just double-click it.

Keeping the titles in view by freezing panes
If you set up a worksheet with row or column headings, it™s easy to lose track of just where
you are when you scroll to a different location in the worksheet. Excel provides a handy
solution to this problem: freezing panes. This keeps the headings visible while you are
scrolling through the worksheet.
To freeze panes, start by moving the cell pointer to the cell below the row that you want to
remain visible as you scroll and to the right of the column that you want to remain visible as
you scroll. Then, select Window_Freeze Panes. Excel inserts dark lines to indicate the frozen
rows and columns. You™ll find that the frozen row and column remain visible as you scroll
throughout the worksheet. To remove the frozen panes, select Window_Unfreeze Panes.
Figure 4-7 shows a worksheet with frozen panes. In this case, rows 1:3 and column A are
frozen in place. This allows you to scroll down and to the right to locate some information
while keeping the column titles and the column A entries visible.

<< . .

. 10
( : 51)

. . >>