Learning the Fundamentals of Excel Worksheets

In Excel, each file is called a workbook, and each workbook can contain one or more worksheets. You may find it helpful to think of an Excel workbook as a notebook and worksheets as pages in the notebook. As with a notebook, you can view a particular sheet, add new sheets, remove sheets, and copy sheets.

The following sections describe the operations that you can perform with worksheets.

Working with Excel’s windows

An Excel workbook file can hold any number of sheets, and these sheets can be either worksheets (sheets consisting of rows and columns) or chart sheets (sheets that hold a single chart). A worksheet is what people usually think of when they think of a spreadsheet. You can open as many Excel workbooks as necessary at the same time.

Figure 14-1 shows Excel with four workbooks open, each in a separate window. One of the windows is minimized and appears near the lower-left corner of the screen. (When a workbook is minimized, only its title bar is visible.) Worksheet windows can overlap, and the title bar of one window is a different color. That’s the window that contains the active workbook.

The workbook windows that Excel uses work much like the windows in any other Windows program. Each window has three buttons at the right side of its title bar. From left to right, they are Minimize, Maximize (or Restore), and Close. When a workbook window is maximized, the three buttons appear directly below Excel’s title bar.

Excel’s windows can be in one of the following states:

• Maximized: Fills Excel’s entire workspace. A maximized window doesn’t have a title bar, and the workbook’s name appears in Excel’s title bar. To maximize a window, click its Maximize button.

• Minimized: Appears as a small window with only a title bar. To minimize a window, click its Minimize button.

• 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 know how to move, resize, and switch among the workbook windows.

Moving and resizing windows

To move a window, make sure that it’s not maximized. Then drag its title bar with your mouse.

To resize a window, 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 drag to resize the window. To resize a window horizontally and vertically at the same time, drag any of its corners.

If you want all 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. Choosing View Window Arrange All displays the Arrange Windows dialog box, shown in Figure 14-2. This dialog box has four window-arrangement options. Just select the one that you want and click OK. Windows that are minimized aren’t affected by this change.

Switching among windows

At any given time, one (and only one) workbook window is the active window. The active window accepts your input and is the window in 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. You can make a different window the active workbook in several ways:

• Click another window, if it’s visible. The window you click moves to the top and becomes the active window. This method isn’t possible if the current window is maximized.

• 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.

• Choose View Window Switch Windows and select the window that you want from the drop-down list (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).

• Click the icon for the window in the Windows taskbar. This technique is available only if the Show All Windows In The Taskbar option is turned on. You can control this setting by clicking the Advanced choice in the Excel Options dialog box (the setting is in the Display section).

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.

Closing windows

If you have multiple windows open, you may want to close those windows that you no longer need. Excel offers several ways to close the active window:

• Choose Office Button Close.

• Click the Close button (the X button) on the workbook 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.

• Press Ctrl+W.

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.

Activating a worksheet

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

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

Changing the Default 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, you can easily add new sheets if and when they’re needed. To change the default number of worksheets: 1. Select Office Button Excel Options to display the Excel Options dialog box. 2. In the Excel Options dialog box, click Popular in the list at the left. 3. Change the value for the Include This Many Sheets setting and click OK. Making this change affects all new workbooks but has no effect on existing workbooks.

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 individually, you may 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:

• Click the Insert Worksheet control, which is located to the right of the last sheet tab. This method inserts the new sheet after the last sheet in the workbook.

• Press Shift+F11. This method inserts the new sheet before the active sheet.

• Right-click a sheet tab, choose Insert from the shortcut menu, and click the General tab of the Insert dialog box. Then click the Worksheet icon and click OK. This method inserts the new sheet before the active sheet.

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:

• Right-click the sheet tab and choose Delete from the shortcut menu.

• Choose Home Cells Delete Delete Sheet. If the worksheet contains any data, Excel asks you to confirm that you want to delete the sheet. If you’ve never used the worksheet, Excel deletes it immediately without asking for confirmation.

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, remembering where to find things in multiple-sheet workbooks can be a bit difficult. That’s why providing more meaningful names for your worksheets is often a good idea.

To change a sheet’s name, double-click the sheet tab. Excel highlights the name on the sheet tab so that you can edit the name or replace it with a new name.

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 on the screen. Therefore, if you use lengthy sheet names, you won’t be able to see very many sheet tabs without scrolling the tab list.

Changing a sheet tab’s color

Excel allows you to change the color of your worksheet tabs. For example, you may prefer to color-code the sheet tabs to make identifying the worksheet’s contents easier.

To change the color of a sheet tab, right-click the tab and choose Tab Color. Then select the color from the color selector 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 may 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:

• Right-click the sheet tab and choose Move or Copy to display the Move or Copy dialog box (see Figure 14-4). Use this dialog box to specify the operation and the location for the sheet.

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

  

• To move a worksheet, drag the worksheet tab to the desired location (either in the same workbook or in a different workbook). When you drag, the mouse pointer changes to a small sheet, and a small arrow guides you.

• To copy a worksheet, press and hold Ctrl while dragging 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.

Preventing Sheet Actions To prevent others from unhiding hidden sheets, inserting new sheets, renaming sheets, copying sheets, or deleting sheets, protect the workbook’s structure: 1. Choose Review Changes Protect Workbook. 2. In the Protect Workbook dialog box, click the Structure option. 3. Provide a password, if you like. After performing these steps, several commands will no longer be available when you right-click a sheet tab: Insert, Delete, Rename, Move or Copy, Hide, and Unhide. Be aware, however, that this is a very weak security measure. Cracking Excel’s protection features is relatively easy. You can also make a sheet “very hidden.” A sheet that is very hidden doesn’t appear in the Unhide dialog box. To make a sheet very hidden: 1. Activate the worksheet. 2. Choose Developer Controls Properties. The Properties dialog box, shown in the following figure, appears. (If the Developer tab isn’t available, you can turn it on from the Popular category in the Excel Options dialog box.) 3. In the Properties box, select the Visible option and choose 2 - xlSheetVeryHidden. [View full size image] After performing these steps, the worksheet is hidden and doesn’t 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 aren’t able to select the sheet! In fact, the only way to unhide such a sheet is to use a VBA macro. For example, this VBA statement unhides Sheet1 in the active workbook: ActiveWorkbook.Worksheets("Sheet1").Visible = True

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).

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. You can’t hide all the sheets in a workbook, so at least one sheet must remain visible.

To hide a worksheet, right-click its sheet tab and choose Hide. The active worksheet (or selected worksheets) will be hidden from view.

To unhide a hidden worksheet, right-click any sheet tab and choose 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 command for each sheet that you want to unhide.