Worksheet in Excel 2003

French | Spanish | Portuguese | Italian | German | Japanese | Chinese | Korean | Russian | Arabic

Refining Your Worksheet

This article delves into the workaday world of worksheets (say that three times quickly). It explains how to edit worksheet data and move quickly here and there in a worksheet. You also discover a couple of techniques for entering data quickly, how to select cells, and how to copy and move data in cells.

Editing Worksheet Data

Not everyone enters the data correctly the first time. To edit data you have entered in a cell, do one of the following:

 -  Double-click the cell. Doing so places the cursor squarely in the cell, where you can start deleting or entering numbers and text.

 -  Click the cell and press F2. This technique also lands the cursor in the cell.

 -  Click the cell you want to edit. With this technique, you edit the data on the Formula bar. If nothing happens when you double-click, or if pressing F2 lands the cursor in the Formula bar, not a cell, somebody has been fooling with Edit settings. Choose Tools -> Options, select the Edit tab in the Options dialog box, and select the Edit Data Directly in Cell check box.

Moving around in a Worksheet

Going from place to place gets progressively more difficult as a worksheet gets larger. Luckily for you, Excel offers keyboard shortcuts for jumping around. In addition to pressing keys, you can use these techniques to get from place to place in a worksheet:

 -  Scroll bars: Use the vertical and horizontal scroll bars to move to different areas. Drag the scroll box to cover long distances. To cover long distances very quickly, hold down the Shift key as you drag the scroll box on the vertical scroll bar.

 -  IntelliMouse: If your computer is equipped with an IntelliMouse, turn the wheel to quickly scroll up and down.

 -  Name box: Enter a cell address in the Name box and press Enter to go to the cell. The name box is found to the left of the formula bar. If you named cell ranges in your worksheet you can go to a cell range by choosing its name in the Name box.

 -  The Go To command: Choose Edit -> Go To (or press Ctrl+G or F5) and, in the Go To dialog box, enter a cell address in the Reference box and click OK. Cell addresses you’ve already visited with the Go To command are already listed in the dialog box, as are cell ranges you named. Click the Special button to open the Go To Special dialog box and visit a formula, comment, or other esoteric item.

 -  The Find command: Click the Find button, choose Edit -> Find, or press Ctrl+F to open the Find dialog box. Enter the data you seek in the Find What box and click the Find Next button. To scroll to the active cell, press Ctrl+Backspace.

Getting a Better Look at the Worksheet

Especially when you’re entering data, it pays to be able to get a good look at the worksheet. You need to know which column and row you are entering data in. These pages explain techniques for changing your view of a worksheet so you always know where you are. Read on to discover how to freeze columns and rows, hide columns and rows, and zoom in and out.

Freezing and splitting columns and rows

Sometimes your adventures in a worksheet take you to a faraway cell address, such as X31 or C39. Out there in the wilderness, it’s hard to tell where to enter data because you can’t see the data labels in the first column or first row that describe what the data in the worksheet is. To see one part of a worksheet no matter how far you stray from it, you can split the worksheet or freeze columns and rows on-screen. Similarly, row 1 also appears onscreen no matter how far you scroll down the worksheet. Notice how the row numbers and column letters are interrupted i. Because the screen is split, you always know what data to enter in a cell. Freezing columns or rows on a worksheet works much like splitting except that lines instead of gray bars appear on-screen to show which columns and rows are frozen. Follow these steps to split or freeze columns and rows on-screen:

1. Click the cell directly below the row you want to freeze or split, and click in the column to the right of the column that you want to freeze or split.

2. Choose Window -> Freeze Panes or Window -> Split. Gray bars or lines are drawn on-screen to show which row and column have been frozen or split. Move where you will in the worksheet. The column and row you froze stay on-screen. The other way to split a worksheet is to grab hold of a split bar, the little division markers directly above the vertical scroll bar and directly to the right of the horizontal scroll bar. You can tell where split bars are because the pointer turns into a double arrow when it’s over a split bar. Click and drag a split bar to split the screen vertically or horizontally. Choose Window -> Unfreeze Panes or Window -> Remove Split when you no longer want a frozen or split worksheet. You can also double-click a split line to remove it from the screen. Splitting the worksheet is superior to freezing columns or rows because, for one, you can drag the gray lines to new locations when you split the worksheet, and, moreover, you can remove the horizontal or vertical split by double-clicking it. Double-click the horizontal split line to remove it and divide the worksheet between sides, or double-click the vertical split to remove it and divide the worksheet between top and bottom.

Hiding columns and rows

Another way to take the clutter out of a worksheet is to temporarily hide columns and rows:

 -  Hiding columns and rows: Click anywhere in the row or column you want to hide and choose Format -> Row -> Hide or Format -> Column -> Hide. You can hide more than one row or column by selecting them and then giving a Hide command.

 -  Unhiding columns and rows: Select columns to the right and left of the hidden column, or select rows above and below the hidden row, and choose Format -> Row -> Unhide or Format -> Column -> Unhide. To unhide all columns and rows in the worksheets, click the Select All button (or press Ctrl+A) and give an Unhide command. To unhide Row 1 or Column A, choose Edit -> Go To (or press F5) and, in the Go To dialog box, enter A1 in the Reference text box and click OK. Then give an Unhide command.

Zooming in and out

The Zoom commands come in very handy when you’re entering data. From the Zoom box on the Standard toolbar, select or enter a percentage and press the Enter key. You can also zoom in or out by choosing View -> Zoom and making a selection in the Zoom dialog box. Select the data in your worksheet, choose View -> Zoom, and select the Fit Selection option in the Zoom dialog box to see all the data in a worksheet simultaneously. If you have an IntelliMouse, the supercharged mouse manufactured by the Microsoft Corporation, you can zoom in and out by holding down the Ctrl key and turning the mouse wheel backward or forward. If you want to use the IntelliMouse strictly for zooming, not for scrolling, choose Tools -> Options, select the General tab in the Options dialog box, and select the Zoom on Roll with IntelliMouse check box.

Comments for Documenting Your Worksheet

It may happen that you return to your worksheet days or months from now and discover to your dismay that you don’t know why certain numbers or formulas are there. For that matter, someone else may inherit your worksheet and be mystified as to what the heck is going on. To take the mystery out of a worksheet, document it by entering comments here and there. Each comment is connected to a cell. You can tell where a comment is because a small red triangle appears in the corner of cells that have been commented on. Move the pointer over one of these triangles and you see the comment and the name of the person who entered the comment in a pop-up box. Here’s everything a mortal needs to know about comments:

 -  Entering a comment: Click the cell that deserves the comment, choose Insert -> Comment (or press Shift+F2), and enter your comment in the pop-up box. Click in a different cell when you’re done entering your comment.

 -  Reading a comment: Move the pointer over the small red triangle and read the comment in the pop-up box. To search for comments in a workbook, shrink your worksheet to 50 percent, choose Edit -> Go To (or press Ctrl+G), and, in the Go To dialog box, click the Special button. Then, in the Go To Special dialog box, select Comments and click OK. All comments in your worksheet are highlighted.

 -  Editing a comment: Select the cell with the comment, choose Insert -> Edit Comment, and edit the comment in the pop-up box.

 -  Deleting comments: Right-click the cell with the comment and choose Delete Comment. To delete several comments, select them and choose Edit -> Clear -> Comments. To delete all the comments in a workbook, use the Edit -> Go To command to highlight all of them; then right-click any highlighted cell with a comment and choose Delete Comment. If your name doesn’t appear in the pop-up box when you enter a comment and you want it to appear there, choose Tools -> Options, select the General tab in the Options dialog box, and enter your name in the User Name text box. You can print the comments in a worksheet. Choose File -> Page Setup and, on the Sheet tab of the Page Setup dialog box, open the Comments dropdown list and choose At End of Sheet or As Displayed on Sheet.

Selecting Cells in a Worksheet

To format, copy, move, or delete numbers or words in a worksheet, you have to select the numbers and words. Here are ways to select cells and the data inside them:

 -  A block of cells: Drag diagonally across the worksheet from one corner of the block of cells to the opposite corner. You can also click in one corner and Shift+click the opposite corner.

 -  Adjacent cells in a row or column: Drag across the cells.

 -  Cells in various places: While holding down the Ctrl key, click different cells, drag across different cells, or click row numbers and column letters.

 -  A row or rows: Click the row number to select an entire row. Click and drag down the row numbers to select several rows.

 -  A column or columns: Click the column letter to select an entire column. Click and drag across letters to select several columns.

 -  Entire worksheet: Click the Select All button, the square to the left of the heading letters and above the row numbers, press Ctrl+A, or press Ctrl+Shift+Spacebar. Press Ctrl+Spacebar to select the column that the active cell is in; press Shift+Spacebar to select the row where the active cell is. You can enter the same data item in several different cells by selecting cells and then entering the data in one cell and pressing Ctrl+Enter. This technique comes in very handy, for example, when you want to enter a placeholder 0 in several different cells.

Deleting, Copying, and Moving Data

To empty cells of their contents, select the cells and then either press the Delete key or right-click and choose Clear Contents. To copy or move data in a worksheet, use those old standbys, the Cut, Copy, and Paste commands. When you paste the data, click where you want the first cell of the block of cells you’re copying or moving to go. Be careful not to overwrite cells with data in them when you copy or move data. After you paste data, you see the Paste Options button. Click this button and choose an option from the menu to format the data in different ways. As for the drag-and-drop method of copying and moving text, you can use it as well. Move the pointer to the edge of the cell block, click when you see the arrow, and start dragging.