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. (You move cells around, for example, when you change your mind about how to organize your data, or when you find yourself adding so much data to one table that it threatens to take over the rest of the tables on your spreadsheet.) Excel is a champion of the basic cut-and-paste feature and 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 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 or formula, 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 work 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). marquee border (Figure 3-4), so called because the border blinks like the twinkling lights around an old-style movie theater marquee. (When you use a cut operation, Excel doesn't actually empty the cells until you paste them somewhere else.) At the same time, the text "Select destination and press ENTER or choose Paste" appears in the Status bar (if it fits). Figure 3-4. In this example, cells A1 to A4 have been copied (note the marquee border, which also appears when you cut cells). 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. Odd as it may seem, Excel lets you 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). 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 (column B) winds up the same width as the source column (column A).
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. (Clicking the bottom-right corner turns the pointer into a cross and tells Excel you want to create an AutoFill, as described in Chapter 2.) 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 program open.
Note: If you want to paste Excel data into a non-Office program, you'll be able to paste only the data that you 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 may not even be aware of the additional stuff stored in the clipboard. To take a look, use the Clipboard task. | 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 program? 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, 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 programs.) 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. |
First, show the Task Pane (choose View Task Pane if its not already visible on the right side of your worksheet), then click the down arrow you see on 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. Delete All is a useful approach if you want to add more data to the Clipboard and you don't want to confuse this new information with whatever stuff 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 lets you paste that item or remove it from the clipboard.
Figure 3-5. The Clipboard task window shows a list of all the items you've copied to it since you last started Excel (up to 24 items). The icon next to the item always indicates which Office program the information came from: Excel, Word, Power Point, and so on.
Note: If you use other Office programs at the same time you use Excel, 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 indicates which program the information came from.
Depending on your settings, the Clipboard task window may spring into view automatically. To configure this behavior, click the Options button at the bottom of the Clipboard task window to display a menu of options. These include: Show Office Clipboard Automatically. If you turn on this option, the Clipboard task window automatically appears if you copy more than one piece of information to the clipboard. (Remember, without the Clipboard task window, you can only access the last piece of information you've copied.) Show Office Clipboard When Ctrl+C Pressed Twice. If you turn on this option, the Clipboard task window 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. Turning on this option overrides the previous two settings, ensuring that the Clipboard task window will never appear automatically. You can still call it up manually, of course, as described earlier in this section. Show Office Clipboard Icon on Taskbar. If you turn on this option, a clipboard icon will appear in the Windows system tray on the bottom of your screen, at the right of the taskbar. You can double-click this icon to show the Clipboard task window while working in any Office program. You can also right-click this icon to change clipboard settings or 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've just copied a fourth item to the clipboard).
| 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, potentially confusing the heck out of you and anyone else who sees the spreadsheet. Fortunately, it's easy to solve this problem: just 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 narrower column. |
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 lets you choose exactly what Excel will paste, and it also lets you apply a few other settings. Skip the Operation section; the Paste section is by far the most useful. These options are divided into two main groups: Paste and Operation. The Paste settings determine what content Excel pastes. Paste 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. (Comments are blocks of free-form "extra" text you can assign to a cell without actually modifying the cell's contents. To assign a comment to a cell, first click the cell to activate it, and then select Insert Comment. In the yellow-sticky-pad-looking text box that appears, type your comment text. When youre finished, tell Excel to close the text box by clicking any worksheet cell.) Validation. This option copies only cells that use validation, and it ignores all others. (Data validation refers to rules you can create that help prevent folksco-workers, family members, even yourself a month or two down the roadfrom typing the wrong data in a cell. For example, you can create a data-validation rule that restricts a cell (or range of cells) to numeric values only; when someone tries to type in a text value, a pop-up warning message appears. The data validation process (which you begin by selecting Data Validation) is fairly advanced, and so isnt covered in this book. For specifics, you may want to check out Excel: The Missing Manual.) 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 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 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, columns-to-rows and rows-to-columns, on the right. Finally, you can use the Paste Link button on the Paste Special dialog box 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. When is pasting a link useful? Well, suppose you want to record your year-to-date income in a single cell of your spreadsheet and create different budget scenarios (canned tuna vs. caviar) elsewhere on your spreadsheet. Linking your budget scenarios to your income cell means that, as you update your income every payday, your budget scenarios always stay up to datewith no extra work on your part. In fact, if you use the Paste Link button to copy and paste some cells and then take a closer look at the pasted 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
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 window, the paste icon still appears, but it provides just two options: keeping the source formatting or pasting the data only.
|