- The Clipboard Viewer
- Editing with the Keyboard
- Creating a Data Series
- Creating a Custom Fill Series
- Creating a Custom Fill to Define Linear or Growth Trends
- Creating a Custom List or Series
- Replacing Data
- Tracking Changes Made by Multiple Users
- Using Comments to Explain Cell Content
- Linking Excel Data
- Troubleshooting
- Excel in Practice
Editing with the Keyboard
When you click a cell that already contains data, pressing virtually any key on your keyboard temporarily replaces your content with the character or code associated with the key pressed. In your use of Excel, you'll find that you can use this feature to your own benefit (for intentional replacements) or in error (by striking a key accidentally). To familiarize yourself with the keys that will edit your cell content, review the following:
Alphanumeric keys (all the letters of the alphabet, the numbers 0 through 9, and all symbols and punctuation) will, when pressed, replace your selected cell content.
The numeric keypad (active if Num Lock is on) will replace your content with numbers and mathematical symbols (/ * - + .).
The spacebar will replace selected cell content with a space.
CAUTION
Pressing the spacebar in a cell doesn't delete the cell's contents—it replaces them with a space. To delete a cell's contents, select the cell and press the Delete key.
TIP
If a cell containing data is accidentally edited by your pressing a key on the keyboard, press the Esc key immediately to return to the cell's original content. If you have already pressed Enter, click the Undo button, choose Edit, Undo, or press Ctrl+Z to undo the changes.
Editing a Group of Cells
You also can edit a group of selected cells, whether in a single block or a group of two or more noncontiguous selections. This enables you to edit several cells quickly, saving you the time of selecting and editing each cell individually.
To edit a group of selected cells, follow these steps:
Select the group of cells you want to edit. If the cells are noncontiguous, hold down the Ctrl key as you gather them into the selection.
Starting with the cell that remains white (the first cell in your most recent selection), type your corrected data and/or make your corrections using the Formula bar.
-
Press the Enter key to move to the next cell in the block (see Figure 3.2). If you prefer to move left to right through your selection, use the Tab key rather than Enter. Shift+Enter moves backward vertically; Shift+Tab moves backward horizontally.
Continue editing the cells in your selection, pressing Enter or Tab after each cell's edits are complete.
When you finish editing the cells, click any cell or use one of the arrow keys to deselect your block(s) of cells.
){kind=link}
Figure 3.2 Excel moves down the selected block's columns and then starts at the top of the next column as you press the Enter key to complete each cell's edits.
NOTE
If you want to place the same entry in multiple cells, select the cells (contiguous or noncontiguous), type the entry in one of the cells or in the Formula bar, and press Ctrl+Enter to place the entry in all the selected cells.
CAUTION
If you don't click outside the selected cells or use an arrow key at the end of your editing session, you will cycle through the selected cells again. This risks re-editing the first cells you edited in the current session.
Editing Using the Formula Bar
The Formula bar obviously displays more than formulas. If a selected cell contains text or a number that isn't the result of a formula, this content also appears in the Formula bar. Figure 3.3 shows the Formula bar content for a selected cell.
){kind=link}
Figure 3.3 The Formula bar gives you room to edit long titles, as well as complex numeric or formula content.
To use the Formula bar to edit cell content, follow these steps:
Click the cell you want to edit. The cell's content appears in the Formula bar.
-
In the Formula bar, use your mouse to select some or all of the cell's content (see Figure 3.4). You can select individual characters, words, or any portion of the cell content.
Type the replacement content—this will simultaneously remove and replace the Formula bar's selected content.
Press Enter to place your edited content in the cell.
){kind=link}
Figure 3.4 In lieu of the mouse, you can use the Shift and arrow keys to select content on your Formula bar.
TIP
Use your Backspace or Delete keys to remove a cell's content, one character at a time, on the Formula bar.
If your selected cell contains a formula, the result of the formula appears in the cell, but the formula itself appears in the Formula bar, as shown in Figure 3.5. To edit the formula, click in the Formula bar and delete/replace the numbers, operators, and cell addresses you need, and then press Enter. The formula is recalculated, and the new result appears in the cell.
){kind=link}
Figure 3.5 Always check the Formula bar to see whether the cell's content is a number that was typed directly into the cell or whether the number is the result of a formula.
Moving Cell Content with Drag and Drop
You can drag any selection from one place to another by following these steps:
Select the cell or cells you want to move.
Point to the edge of the cell or block of cells, and look for your mouse to turn into a left-pointing arrow with four small black arrows attached.
Click and drag the cell(s) to the desired location on the worksheet.
Release the mouse to drop the selection in its new location.
NOTE
You cannot move (or copy) noncontiguous selections with drag and drop.
If you attempt to drop the moved cell(s) where data already exists, you'll be prompted to confirm your desire to replace the contents of the destination cells.
TIP
If drag and drop doesn't seem to work, choose Tools, Options, click the Edit tab, and check Allow Cell Drag and Drop.
TIP
If you don't get the confirmation prompt after you drag and drop cells, the prompt is turned off. To turn it back on, choose Tools, Options, click the Edit tab, and check Alert Before Overwriting Cells.
CAUTION
Although you can drag the selected cell(s) to a spot outside the currently visible portion of the worksheet, you might find it difficult to control scrolling so that you arrive at your exact desired location. You might find that the sheet scrolls too fast.
CAUTION
There's an informal name for this unintended fast scrolling: the Roadrunner Effect. To counteract it, rein in how far off the grid you move your mouse. When dragging and dropping downward, for example, if your mouse pointer is in the sheet tab area, scrolling is relatively slow. But if you move the mouse pointer into the status bar, scrolling speeds up significantly. So, don't just slam your mouse downward—keep it just off the edge of the grid.
If you need to move or copy cells between worksheets or workbooks, you can choose Window, Arrange, Tile or Cascade to position windows so that you can use drag and drop between them. If you prefer, you can use the Clipboard to move (cut) and copy.
NOTE
You can drag and drop onto other worksheets in your workbook by holding down the Alt key and dragging the selected cells to the other worksheet's tab, which causes that worksheet to open. Drag the selected cells where you want them.
Copying Cell Content with Drag and Drop
Moving cell content implies that it isn't in the right place or that it's no longer needed in its current location. Copying, on the other hand, allows you to leave the original cell and its content right where it is, and place a duplicate elsewhere on the worksheet. Just like moving, you can use drag and drop to copy a cell or cells from one place to another on your worksheet—again, it's a task best performed within a small area.
To copy a cell or group of contiguous cells to another location on the active worksheet, follow these steps:
Select the cell or cells you want to copy.
Point to the edge of the selected cell(s). Your mouse pointer turns into a left-pointing arrow with four small black arrows attached.
Press and hold the Ctrl key.
-
Drag your selected cells to another location, keeping the Ctrl key depressed as you drag. Figure 3.6 shows a block of cells being copied to another location.
When your desired location is reached, release the mouse.
Release the Ctrl key.
){kind=link}
Figure 3.6 Because you're dragging a copy, a small plus sign (+) follows your mouse pointer to the target location.
CAUTION
If you release the Ctrl key prior to releasing the mouse, your copy procedure turns into a move, and the selected cells will be taken from their original location and moved to the spot where you released the mouse. Be sure to release the Ctrl key after the cells' duplicates have been deposited in their new location.
When you attempt to move a cell's content to a cell that already contains data, you're prompted to confirm that this is your intention. Be aware that no such prompt appears when you drag and drop to copy a cell or cells, thus making it easy to accidentally overwrite data while copying.
CAUTION
You should always release the mouse first, then the keyboard. It's a Windows standard rule.