Moving Data Within a Workbook

You can move to a specific cell in lots of ways, but the most direct method is to click the cell to which you want to move. The cell you click will be outlined in black, and its contents, if any, will appear in the formula bar. When a cell is outlined, it is the active cell, meaning that you can modify its contents. You use a similar method to select multiple cells (referred to as a cell range)-just click the first cell in the range and drag the mouse pointer over the remaining cells you want to select. After you select the cell or cells you want to work with, you can cut, copy, delete, or change the format of the contents of the cell or cells. For instance, Gregory Weber, the Northwestern Distribution Center Manager, might want to copy the cells that contain a set of column labels to a new page that summarizes similar data.

Important 

If you select a group of cells, the first cell you click is designated the active cell.

You’re not limited to selecting cells individually or as part of a range. For example, you might need to move a column of price data one column to the right to make room for a column of headings that indicate to which service category (ground, three-day express, two-day express, overnight, or priority overnight) a set of numbers belongs. To move an entire column (or entire columns) of data at a time, you click the column’s header, located at the top of the worksheet. Clicking a column header highlights every cell in that column and enables you to copy or cut the column and paste it elsewhere in the workbook.

The Paste Options button appears next to data you copy from a cell and paste into another cell. Clicking the Paste Options button displays a list of actions that Excel 2007 can take regarding the pasted cells.

The options in the list are summarized in the following table.

Open table as spreadsheet

Option	Action
Use Destination Theme	Pastes the contents of the Clipboard (which holds the last information selected via Cut or Copy) into the target cells and formats the data using the theme applied to the target workbook.
Match Destination Formatting	Pastes the contents of the Clipboard into the target cells and formats the data using the existing format in the target cells, regardless of the workbook’s theme.
Keep Source Formatting	Pastes a column of cells into the target column; applies the format of the copied column to the new column.
Values Only	Pastes the values from the copied column into the destination column without applying any formatting.
Values and Number Formatting	Pastes the contents of the Clipboard into the target cells, keeping any numeric formats.
Values and Source Formatting	Pastes the contents of the Clipboard into the target cells, retaining all the source cells’ formatting.
Keep Source Column Widths	Pastes the contents of the Clipboard into the target cells and resizes the columns of the target cells to match the widths of the columns of the source cells.
Formatting Only	Applies the format of the source cells to the target cells, but does not copy the contents of the source cells.

Troubleshooting 

If the Paste Options button doesn’t appear, you can turn the feature on by clicking the Microsoft Office Button and then clicking Excel Options to display the Excel Options dialog box. In the Excel Options dialog box, click the Advanced category and then, in the Cut, copy, and paste section, select the Show Paste Options buttons check box. Click OK to close the dialog box and save your setting.

In this exercise, you will copy a set of column headers to another worksheet, move a column of data within a worksheet, and select paste options for copied data.

Use the 2007Q1ShipmentsByCategory workbook from the Data and Data Tables folder under SBS_Office2007.

Open the 2007Q1ShipmentsByCategory workbook.

1. On the Count worksheet, select cells B2:D2.

2. On the Home tab, in the Clipboard group, click the Copy button.

Excel 2007 copies the contents of cells B2:D2 to the Clipboard.

3. On the tab bar, click the Sales sheet tab.

The Sales worksheet appears.

4. Select cell B2.

5. On the Home tab, in the Clipboard group, click Paste.

Excel 2007 pastes the header values into cells B2:D2.

6. Click the Paste Options smart tag and then click Keep Source Formatting.

Excel 2007 retains the cells’ original formatting.

7. Right-click the column header of column I and then click Cut.

Excel 2007 outlines column I with a marquee.

8. Right-click the header of column E and then click Paste.

Excel 2007 pastes the contents of column I into column E.

Close the 2007Q1ShipmentsByCategory workbook.