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 pressing 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 empty the cell—it replaces the cell’s contents with a space. To delete a cell’s contents, select the cell and press the Delete key.
Tip From
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:
1. | Select the group of cells you want to edit. If the cells are noncontiguous, hold down the Ctrl key as you drag over them to add them to the selection. |
2. | |
3. | Press the Enter key to move to the next cell in the block (see Figure 3.2). Hold down the Enter key to scroll through the information more quickly. 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. 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.
|
4. | Continue editing the cells in your selection, pressing Enter or Tab after each cell’s edits are complete. |
5. |
Note
If you want to place the same entry in multiple cells, select the cells (contiguous or non-contiguous), type the entry in one of the cells or in the Formula bar, and press Ctrl+Enter.
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.
The Formula bar obviously displays more than formulas. If an active 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.
To use the Formula bar to edit cell content, follow these steps:
1. | |
2. | 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. Figure 3.4. Press the Shift+arrow keys to select cell content.
Tip From Use your Backspace or Delete keys to remove a cell’s content, one character at a time, on the Formula bar. |
3. | Type the replacement content—this will simultaneously remove and replace the Formula bar’s selected content. |
4. | Press Enter to place your edited content in the cell. |
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 with the formula syntax tip showing, 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.
You can drag any selection from one place to another by following these steps:
1. | Select the cell or cells you want to move. |
2. | |
3. | |
4. | Release the mouse button to drop the selection in its new location. |
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 From
If drag and drop doesn’t seem to work, choose Tools, Options, click the Edit tab, and check Allow Cell Drag and Drop.
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.
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.
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:
1. | Select the cell or cells you want to copy. |
2. | Point your cursor to the edge of the selected cell(s). Your mouse pointer turns into a left-pointing arrow with four small black arrows attached. |
3. | |
4. | 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. Figure 3.6. Select a range and hold Ctrl to copy cells to a new location.
|
5. | When your desired location is reached, release the mouse button. |
6. | Release the Ctrl key. |
Caution
If you release the Ctrl key prior to releasing the mouse button, 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 button. Be sure to release the Ctrl key after the cells’ duplicates have been deposited in their new location.
You should always release the mouse button first, then the keyboard. It’s a Windows standard rule.
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.