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 let you 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 lets you 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's complete. However, Excel doesn't remove the source cells from the worksheet. Instead, it just leaves them empty. (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's the basic procedure for 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.) When you want to cut or copy only a single cell, just move to the cellyou don't actually need to select it. -
-
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, then move to the top-left corner of the area where you want to paste your selection. If you have existing data below or to the right of this cell, Excel overwrites it with the new content you're pasting. It's 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 appears in columns B, C, and D, and Excel overwrites the original content in these columns (although the original content remains in column A). -
Paste the data by selecting Home  Clipboard Paste (or press Ctrl+V or Enter on the keyboard) . This action completes your cut-and-paste or copy-and-paste operation. When you're performing a cut-and-paste, Excel removes the original data from the spreadsheet just before pasting it in the new location.
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 then cut 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 (see Figure 3-6). Sometimes these signs appear when you're copying a big number into smaller cell. 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 reappears. For a quicker solution, double-click the right edge of the column to automatically make it large enough. 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. The problem is more likely to crop up if you shrink a column afterward, or if you cut some numeric cells from a wide column and paste them into a much narrower column. To verify the source of your problem, just move to the offending cell, and then check the formula bar to see your complete number or date. There's one other situation that can cause a cell to display #######. If you create a formula that subtracts one time from another (as described in Chapter 11), and the result is a negative time value, you see the same series of number signs. But, in this case, column resizing doesn't help. |
 | Figure 3-6. Cell C4 has a wide number in an overly narrow column. You can see the mystery number only if you move to the cell and check out the formula bar (it's 10,042.01), or expand the column to a more reasonable width. | |
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 copy only 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 be able to paste only the data that was added to the clipboard most recently.
When you use the Home Clipboard Paste command (or Ctrl+V), youre using the ordinary Windows clipboard. That means you always paste the item most recently added to the clipboard. But if you fire up the Office clipboard, you can hold a lot more. Go to the Home Clipboard section of the ribbon, and then click the dialog box launcher (the small arrow-in-a-square icon in the bottom-right corner) to open the Clipboard panel. Now Excel adds all the information you copy to both the Windows clipboard and the more capacious Office clipboard. Each item that you copy appears in the Clipboard panel (Figure 3-7).  | Figure 3-7. The Clipboard panel shows a list of all the items you've copied to it since you opened it (up to 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're using multiple Office applications at the same time, you may see scraps of Word documents, PowerPoint presentations, or pictures in the clipboard along with your Excel data. The icon next to the item always tells you which program the information came from. | |
Using the Clipboard panel, 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 panel may automatically spring into action. To configure this behavior, click the Options button at the bottom of the Clipboard panel to display a menu of options. These include: -
Show Office Clipboard Automatically . If you turn on this option, the Clipboard panel automatically appears if you copy more than one piece of information to the clipboard. (Remember, without the Clipboard panel, you can access only the last piece of information you've copied.) -
Show Office Clipboard When Ctrl+C Pressed Twice . If you turn on this option, the Clipboard panel appears if you press the Ctrl+C shortcut twice in a row, without doing anything else in between. -
Collect Without Showing Office Clipboard . If you turn on this option, it overrides the previous two settings, ensuring that the Clipboard panel never appears automatically. You can still call up the Clipboard panel manually, of course. -
Show Office Clipboard Icon on Taskbar . If you turn on this option, a clipboard icon appears in the system tray at the right of the taskbar. You can double-click this icon to show the Clipboard panel 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 Windows system tray whenever you copy data. (The system tray is the set of notification icons at the bottom-right corner of your screen, in the Windows taskbar.) The icon for the Office clipboard shows a clipboard icon, and it displays a message like "4 of 24 -Item Collected" (which indicates you have just copied a fourth item to the clipboard). UP TO SPEED
Cutting or Copying Part of a Cell | | Excel's cut-and-paste and copy-and-paste features let you move 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 need to work a little differently. First, move to the cell that contains the content you want to cut or copy, and 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've 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. |
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 Home Clipboard Paste Paste Special (instead of Home Clipboard Paste). A new dialog box appears with a slew of options (Figure 3-8).  | Figure 3-8. 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. 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 textwith-out 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 in Section 23.2.1. -
Validation . This option copies only cells that use validation, and it ignores all others. You'll learn about validation in Section 22.2. -
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 in Section 5.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. Here, Excel pastes only formulas and any settings used for formatting how numbers appear. (In other words, you'll lose 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 lose 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 from 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-9 shows an example.  | Figure 3-9. With the Transpose option (from the Paste Special dialog box), Excel's pasted the table at the top and transposed it on the bottom. | |
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 contains the reference =A2. You'll learn more about cell references and formulas in Chapter 7.
Tip: Once you know your way around the different pasting options, you can often find a quicker way to get the same result. Instead of choosing Home Clipboard Paste Paste Special, you can choose one of the options in the Home Clipboard Paste menu. You dont find all the options that are in the Paste Special dialog box, but you do find commonly used options like Paste Values and Transpose.
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. (Excel nerds know this icon as a smart tag .) 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-10.  | Figure 3-10. 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 panel, the paste icon still appears, but it provides just two options: keeping the source formatting or pasting the data only.
|
![Excel 2007[c] The Missing Manual](/icons/blank_book.jpg "Excel 2007[c] The Missing Manual"){kind=icon}