3.2. Moving Cells Around One of the most common reasons to select groups of cells on a worksheet is to copy or move them from one place to another. Excel is a champion of the basic cut-and-paste feature, and it also gives you worthwhile enhancements that allow you to do things like drag-and-drop blocks of cells and copy multiple selections to the clipboard at the same time. Before you get started shuffling data from one place to another, here are a few points to keep in mind: -
Excel allows you to cut or copy a single cell or a continuous range of cells. When you cut or copy a cell , everything goes with it, including the data and the current formatting. -
When you paste cells onto your worksheet, you have two basic choices. You can paste the cells into a new, blank area of the worksheet, or, you can paste the cells in a place that already contains data. In this second case, Excel overwrites the existing cells with the new pasted data. -
Cutting and copying cells works almost exactly the same way. The only difference you'll see is that when you perform a cut-and-paste operation (as opposed to a copy-and-paste operation), Excel erases the source data once the operation is complete. However, Excel doesn't remove the source cells from the worksheet. Instead, it just leaves them blank. (The next section shows you what to do if you do want to remove or insert cells, not just the data they contain.) 3.2.1. A Simple Cut-and-Paste or Copy-and-Paste Here is the basic process you'll follow in any cut-and-paste or copy-and-paste operation. -
Select the cells you want to cut or copy. You can use any of the tricks you learned in the previous section to highlight a continuous range of cells. (You can't cut-and-paste non-contiguous selections.) If you only want to cut or copy a single cell, just move to the cellyou don't actually need to select it. -
If you want to cut your selection, choose Edit  Cut (Ctrl+X). If you want to copy your selection, choose Edit Copy (Ctrl+C). Excel highlights your selection with a marquee border (Figure 3-4), so-called because the border blinks like the twinkling lights around an old-style movie theater marquee. At the same time, the text "Select destination and press ENTER or choose Paste" appears in the Status bar (if it fits). -
Move to the new location in the spreadsheet where you want to paste the cells. If you selected one cell, move to the new cell where you want to place the data. If you selected multiple cells, move to the top-left corner where you want to paste your selection. If there's existing data below or to the right of this cell, Excel will overwrite it with the new content you're pasting.  | Figure 3-4. In this example, cells A1 to A4 have been copied . The next step is to move to the place where you want to paste the cells and press Enter to complete the operation. Excel treats cut and copy operations in the same way. In both cases, the selection remains on the spreadsheet, surrounded by the marquee border. When you use a cut operation, Excel doesn't empty the cells until you paste them somewhere else. | | -
It is valid to paste over part of the data you're copying. For example, you could make a selection that consists of columns A, B, and C, and paste that selection starting at column B. In this case, the pasted data will appear in columns B, C, and D, and Excel will overwrite the original content in these columns (although the original content will remain in column A). -
Paste the data by selecting Edit Paste (Ctrl+V or Enter on the keyboard). This action completes your cut-and-paste or copy-and-paste operation. If you're performing a cut-and-paste, Excel removes the original data from the spreadsheet just before pasting it in the new location. | Up To Speed Cutting or Copying Part of a Cell | | Excel's cut-and-paste and copy-and-paste features allow you to move the data in one or more cells. But what if you simply want to take a snippet of text from a cell, and transfer it to another cell or even another application? Excel makes this operation possible, but you'll need to work a little differently. First, move to the cell that contains the content you want to cut or copy, then place it in edit mode by double-clicking it with the mouse or pressing F2. You can now scroll through the cell content using the arrow keys. Move to the position where you want to start chopping or copying, hold down Shift, and then arrow over to the right. Keep moving until you have selected all the text you want to cut or copy. Then, hit Ctrl+C to copy the text, or Ctrl+X to cut it. (When you cut text, it disappears immediately, just like in other Windows applications.) Hit F2 or Enter to exit edit mode once you're finished. The final step is to paste your text somewhere else. You can move to another cell that has data in it already, press F2 to enter edit mode again, move to the correct position in that cell, and then press Ctrl+V. However, you can also paste the text directly into a cell by just moving to the cell and pressing Ctrl+V without placing it into edit mode. In this case, the data you paste overwrites the current content in the cell. |
Tip: Instead of cutting or copying a block of cells, you can also move the entire column or row that contains the cells. Begin by highlighting one or more columns or rows (by selecting the column or row headers). For example, you could select column A by clicking the column header and cutting it. You could then right-click the column B header, and choose Paste to move the column A values into column B. When you copy entire columns, Excel automatically adjusts the column widths as part of the copy operation, so the destination column winds up the same width as the source column.
FREQUENTLY ASKED QUESTION
The Mysterious Number Signs | | What does it mean when I see ####### in a cell?  A series of number (or pound ) signs is Excel's way of telling you that a column isn't wide enough to display the number or date that it contains. Excel doesn't use the number signs with text cellsif those cells aren't large enough to hold their data, the words simply spill over to the adjacent cell (if it's blank), or become truncated (if the adjacent cell has some content). This behavior wouldn't be acceptable with numbers , because if Excel cut off a portion of a number, it would appear to be a completely different number. Fortunately, it's easy to solve this problemjust position the mouse pointer at the right edge of the cell header, and then drag it to the right to enlarge the column. Provided you've made the column large enough, the missing number will reappear. This error doesn't usually occur while you're entering information for the first time, because Excel automatically resizes columns to accommodate any numbers you type in. This problem is more likely if you shrink a column afterward, or if you cut some numeric cells from a wide column and paste them into a much more narrow column. To verify the source of your problem, just move to the offending cell, then check the Formula bar for your complete number or date. |
3.2.2. A Fancy Cut-and-Paste or Copy-and-Paste If you want a really quick way to cut and paste data, you can use Excel's drag-and-drop feature. It works like this: -
Select the cells you want to move. Just drag your pointer over the block of cells you want to select. -
Click the border of the selection box, and don't release the mouse button. You'll know that you're in the right place when the mouse pointer changes to a four-way arrow. You can click any edge, but don't click in the corner. -
Drag the selection box to its new location. If you want to copy (not move) the text, hold down the Ctrl key while you drag. As you drag, a light gray box shows you where Excel will paste the cells. -
Release the mouse button to move the cells. If you drop the cells into a region that overlaps with other data, Excel prompts you to make sure that you want to overwrite the existing cells. This convenience isn't provided with ordinary cut-and-paste operations. (Excel uses it for drag-and-drop operations because it's all too easy to inadvertently drop your cells in the wrong place, especially while you're still getting used to this feature.) 3.2.3. The Clipboard In Windows' early days, you could only copy a single piece of information at a time. If you copied two pieces of data, only the most recent item you copied would remain in the clipboard, a necessary way of life in the memory-starved computing days of yore. But nowadays, Excel boasts the ability to hold 24 separate cell selections in the Office clipboard. This information remains available as long as you have at least one Office application open .
Note: Even though the Office clipboard holds 24 pieces of information, you won't be able to access all this information in Windows applications that aren't part of the Office suite. If you want to paste Excel data into a non-Office application, you'll only be able to paste the data that was added to the clipboard most recently.
When you use the Edit Paste command (or Ctrl+V), you always paste the item most recently added to the clipboard. In fact, you might not even be aware of the additional stuff stored in the clipboard. To take a look, use the Clipboard task. First, show the Task Pane (choose View Task Pane if it's not already visible), then click the title bar of the Task Pane window, and choose Clipboard. Using the Clipboard task window (see Figure 3-5), you can perform the following actions: -
Click Paste All to paste all the selections into your worksheet. Excel pastes the first selection into the current cell, and then begins pasting the next selection starting in the first row underneath that, and so on. As with all paste operations, the pasted cells overwrite any existing content in your worksheet. -
Click Delete All to remove all the selections from the clipboard. This is a useful approach if you want to add more data to the Clipboard, and you don't want to confuse this information with whatever selection you previously copied. -
Click a selection in the list to paste it into the current location in the worksheet. -
Click the drop-down arrow at the right of a selection item to show a menu that allows you to paste that item or remove it from the clipboard. Depending on your settings, the Clipboard task may spring into view automatically. To configure this behavior, click the Options button at the bottom of the Clipboard task to display a menu of options. These include: -
Show Office Clipboard Automatically . If you turn on this option, the Clipboard task will automatically appear if you copy more than one piece of information to the clipboard. (Remember, without the Clipboard task, you can only access the last piece of information you've copied.)  | Figure 3-5. The Clipboard task shows a list of all the items you've copied to it since you last started Excel (with a limit of 24 items). Each item shows the combined content for all the cells in the selection. For example, the first item in this list includes four cells: the Price column title followed by the three prices. If you are using multiple Office applications at the same time, you might see scraps of Word documents, PowerPoint presentations, or pictures in the clipboard along with your Excel data. The icon next to the item always indicates which program the information came from. | |
-
Show Office Clipboard When Ctrl+C Pressed Twice . If you turn on this option, the Clipboard task will appear if you press the Ctrl+C shortcut twice in a row, without doing anything else in between. This option is only available in Excel 2003. -
Collect Without Showing Office Clipboard . If you turn on this option, it overrides the previous two settings, ensuring that the Clipboard will never appear automatically. You can still call up the Clipboard manually, of course. -
Show Office Clipboard Icon on Taskbar . If you turn on this option, a clipboard icon will appear in the system tray at the right of the taskbar. You can double-click this icon to show the Clipboard while working in any Office application. You can also right-click this icon to change clipboard settings or to tell the Office clipboard to stop collecting data. -
Show Status Near Taskbar When Copying . If you turn on this option, you'll see a tooltip near the system tray whenever you copy data. This tooltip will show a clipboard icon, and it will have text like "4 of 24 - Item Collected" (which indicates you have just copied a fourth item to the clipboard). 3.2.4. Special Pasting When you copy cells, everything comes along for the ride, including text, numbers, and formatting. For example, if you copy a column that has one cell filled with bold text and several other cells filled with dollar amounts (including the dollar sign), when you paste this column into its new location, the numbers will still have the dollar sign and the text will still have bold formatting. If you want to change this behavior, you can use the Paste Special command. It works like this. First, copy your cells in the normal way. (Don't cut them, or the Paste Special feature won't work.) Then, move to where you want to paste the information, and choose Edit Paste Special from the menu (instead of Edit Paste). A new dialog box appears with a slew of options (Figure 3-6).  | Figure 3-6. The Paste Special window allows you to choose exactly what Excel will paste, and it also lets you apply a few other settings. In this example, Excel will paste the cell values but not the formatting. | |
These options are divided into two main groups: Paste and Operation. The Paste settings determine what content Excel pastes. This is the most useful part of the window. These settings include: -
All . This option is the same as a normal paste operation, and it pastes both formatting and numbers. -
Formulas . This option pastes only cell contentnumbers, dates, and textwithout any formatting. If your source range includes any formulas, Excel also copies the formulas. -
Values . This option pastes only cell contentnumbers, dates, and textwithout any formatting. If your source range includes any formulas, Excel pastes the result of that formula (the calculated number) but not the actual formula. -
Formats . This option applies the formatting from the source selection, but it doesn't actually copy any data. -
Comments . This option copies only the comments that you've added to cells. You'll learn about comments on Section 21.2. -
Validation . This option copies only cells that use validation, and it ignores all others. You'll learn about validation on Section 15.4.1. -
All Except Borders . This option is the same as All, except it ignores any borders that you've applied to the cell. Border formatting is described on Section 4.2.3. -
Column Widths . This option is the same as All, and it also adjusts the columns in the paste region so that they have the same widths as the source columns. -
Formulas and Number Formats . This option doesn't paste any data. It only pastes formulas and any settings used for formatting how numbers appear. (In other words, you'll loose format settings that control the font, cell fill color , and borders.) -
Values and Number Formats . This option pastes everything without any formatting, except for the formatting used to configure how numbers appear. (In other words, you'll loose format settings that control the font, cell fill color, and borders.) The Operation settings are a little wackythey allow you to combine the cells you're pasting with the contents of the cells you're pasting into, either by adding, subtracting, multiplying, or dividing the two sets of numbers. It's an intriguing idea, but few people use these settings because they're not intuitive. Further down the Paste Special dialog box, the "Skip blanks" checkbox tells Excel not to overwrite a cell if the cell you're pasting is empty. The Transpose checkbox inverts your information before it pastes it, so that all the columns become rows and the rows become columns. Figure 3-7 shows an example.  | Figure 3-7. By using the Transpose option (from the Paste Special dialog box), the table on the left has been pasted and transposed on the right. | |
Finally, you can use the Paste Link button to paste a link that refers to the original data instead of a duplicate copy of the content. That means that if you modify the source cells, Excel automatically modifies the copies. In fact, if you take a closer look at the copied cells in the Formula bar, you'll find that they don't contain the actual data. Instead, they contain a formula that points to the source cell. For example, if you paste cell A2 as a link into cell B4, the cell B4 will contain the reference =A2. You'll learn more about cell references and formulas in Chapter 7. Even if you don't use the Paste Special command, you can still control some basic paste settings. After you paste any data in Excel, a paste icon appears near the bottom-right corner of the pasted region. If you click this icon, you'll see a drop-down menu that includes the most important options from the Paste Special dialog box, as shown in Figure 3-8.  | Figure 3-8. The paste icon appears following the completion of every paste operation, letting you control a number of options, including whether the formatting matches the source or destination cells. If you choose "Values and number formatting," Excel copies the cell content and the number formats but ignores other formatting information like font and cell color. The number format determines how the number is displayed (for example, how many decimal places and whether a dollar sign is used). Chapter 4 covers formatting. | |
Note: The paste icon appears only after a copy-and-paste operation, not a cut-and-paste operation. If you paste cells from the Clipboard Task, the paste icon still appears, but it provides just two options: keeping the source formatting or pasting the data only.
|
