As you create a worksheet, you may find it necessary to copy or move information from
one location to another. Excel makes copying or moving ranges of cells easy. Here are some
common things that you might do:
- Copy a cell to another location.
- Copy a cell to a range of cells. The source cell is copied to every cell in the destina-
tion range. - Copy a range to another range.
- Move a range of cells to another location
The primary difference between copying and moving a range is the effect of the operation
on the source range. When you copy a range, the source range is unaffected. When you
move a range, the contents are removed from the source range.
Copying or moving consists of two steps (although shortcut methods are available):
- Select the cell or range to copy (the source range), and copy it to the Clipboard. To
move the range instead of copying it, cut the range instead of copying it - Select the cell or range that will hold the copy (the destination range), and paste
the Clipboard contents.
Because copying (or moving) is used so often, Excel provides many different methods. We
discuss each method in the following sections. Copying and moving are similar operations,
so we point out only important differences between the two.
Copying by using Ribbon commands –
Choosing Home ➪ Clipboard ➪ Copy transfers a copy of the selected cell or range to the
Windows Clipboard and the Office Clipboard. After performing the copy part of this opera-
tion, select the destination cell and choose Home ➪ Clipboard ➪ Paste.
Instead of choosing Home ➪ Clipboard ➪ Paste, you can just activate the destination cell
and press Enter. If you use this technique, Excel removes the copied information from the
Clipboard so that it can’t be pasted again.
If you’re copying a range, you don’t need to select an entire same-sized range before you
click the Paste button. You only need to activate the upper-left cell in the destination
range.
Copying by using shortcut menu commands –
If you prefer, you can use the following shortcut menu commands for copying and pasting.
- Right-click the range and choose Copy (or Cut) from the shortcut menu to copy the
selected cells to the Clipboard. - Right-click and choose Paste from the shortcut menu that appears to paste the
Clipboard contents to the selected cell or range.
For more control over how the pasted information appears, right-click the destination cell
and use one of the Paste icons in the shortcut menu (see Figure below).
Instead of using Paste, you can just activate the destination cell and press Enter. If you use
this technique, Excel removes the copied information from the Clipboard so that it can’t be
pasted again.
Copying by using shortcut keys –
The copy and paste operations also have shortcut keys associated with them:
- Ctrl+C copies the selected cells to both the Windows Clipboard and the Office
Clipboard. - Ctrl+X cuts the selected cells to both the Windows Clipboard and the Office
Clipboard. - Ctrl+V pastes the Windows Clipboard contents to the selected cell or range.
Copying or moving by using drag-and-drop –
Excel also enables you to copy or move a cell or range by dragging. Unlike other methods of
copying and moving, dragging and dropping does not place any information on either the
Windows Clipboard or the Office Clipboard.
To copy using drag-and-drop, select the cell or range that you want to copy, press Ctrl, and
move the mouse to one of the selection’s borders. (The mouse pointer is augmented with
a small plus sign.) Then drag the selection to its new location while you continue to hold
down the Ctrl key. The original selection remains behind, and Excel makes a new copy
when you release the mouse button.
To move a range using drag-and-drop, don’t press Ctrl while dragging the border.
Copying to adjacent cells –
Often, you need to copy a cell to an adjacent cell or range. This type of copying is quite
common when you’re working with formulas. For example, if you’re working on a budget,
you might create a formula to add the values in column B. You can use the same formula to
add the values in the other columns. Rather than re-enter the formula, you can copy it to
the adjacent cells.
Excel provides additional options for copying to adjacent cells. To use these commands,
activate the cell that you’re copying and extend the cell selection to include the cells to
which you’re copying. Then issue the appropriate command from the following list for one-
step copying
- Home ➪ Editing ➪ Fill ➪ Down (or Ctrl+D) copies the cell to the selected range
below. - Home ➪ Editing ➪ Fill ➪ Right (or Ctrl+R) copies the cell to the selected range to
the right. - Home ➪ Editing ➪ Fill ➪ Up copies the cell to the selected range above.
- Home ➪ Editing ➪ Fill ➪ Left copies the cell to the selected range to the left
None of these commands places information on either the Windows Clipboard or the Office
Clipboard.
Copying a range to other sheets –
You can use the copy procedures described previously to copy a cell or range to another
worksheet, even if the worksheet is in a different workbook. You must, of course, activate
the other worksheet before you select the location to which you want to copy.
Excel offers a quicker way to copy a cell or range and paste it to other worksheets in the
same workbook.
- Select the range to copy.
- Press Ctrl and click the sheet tabs for the worksheets to which you want to
copy the information. Excel displays Group in the workbook’s title bar. - Choose Home ➪ Editing ➪ Fill ➪ Across Worksheets. A dialog box appears to ask
you what you want to copy (All, Contents, or Formats). - Make your choice and then click OK. Excel copies the selected range to the
selected worksheets; the new copy occupies the same cells in the selected work-
sheets as the original occupies in the initial worksheet.
Using the Office Clipboard to paste –
Whenever you cut or copy information in an Office program such as Excel, you can place
the data on both the Windows Clipboard and the Office Clipboard. When you copy informa-
tion to the Office Clipboard, you append the information to the Office Clipboard instead of
replacing what is already there. With multiple items stored on the Office Clipboard, you can
then paste the items either individually or as a group.
To use the Office Clipboard, you first need to open it. Use the dialog box launcher on the
bottom right of the Home ➪ Clipboard group to toggle the Clipboard task pane on and off.
After you open the Clipboard task pane, select the first cell or range that you want to copy
to the Office Clipboard and copy it by using any of the preceding techniques. Repeat this
process, selecting the next cell or range that you want to copy. As soon as you copy the
information, the Office Clipboard task pane shows you the number of items that you’ve
copied and a brief description (it will hold up to 24 items). Figure below shows the Office
Clipboard with five copied items (four from Excel and one from Word).
When you’re ready to paste information, select the cell into which you want to paste infor-
mation. To paste an individual item, click it in the Clipboard task pane. To paste all of the
items that you’ve copied, click the Paste All button (which is at the top of the Clipboard
task pane). The items are pasted, one after the other. The Paste All button is probably more
useful in Word for situations in which you copy text from various sources and then paste it
all at once.
You can clear the contents of the Office Clipboard by clicking the Clear All button.
The following items about the Office Clipboard and how it functions are worth noting:
- Excel pastes the contents of the Windows Clipboard (the last item you copied to the
Office Clipboard) when you paste by choosing Home ➪ Clipboard ➪ Paste, by press-
ing Ctrl+V, or by right-clicking and choosing Paste from the shortcut menu. - The last item that you cut or copied appears on both the Office Clipboard and the
Windows Clipboard. - Clearing the Office Clipboard also clears the Windows Clipboard.
Pasting in special ways –
You may not always want to copy everything from the source range to the destination
range. For example, you may want to copy only the formula results rather than the formu-
las themselves. Or you may want to copy the number formats from one range to another
without overwriting any existing data or formulas.
To control what is copied into the destination range, choose Home ➪ Clipboard ➪ Paste and
use the drop-down menu shown in Figure below. When you hover your mouse pointer over an icon, you’ll see a preview of the pasted information in the destination range. Click the icon
to use the selected paste option.
The paste options are as follows:
Paste (P) Pastes the cell’s contents, formula, formats, and data validation from the
Windows Clipboard.
Formulas (F) Pastes formulas but not formatting.
Formulas & Number Formatting (O) Pastes formulas and number formatting only.
Keep Source Formatting (K) Pastes formulas and all formatting.
No Borders (B) Pastes everything except borders that appear in the source range.
Keep Source Column Widths (W) Pastes formulas and duplicates the column width of the
copied cells.
Transpose (T) Changes the orientation of the copied range. Rows become columns, and
columns become rows. Any formulas in the copied range are adjusted so that they work
properly when transposed.
Merge Conditional Formatting (G) This icon is displayed only when the copied cells
contain conditional formatting. When clicked, it merges the copied conditional formatting
with any conditional formatting in the destination range.
Values (V) Pastes the results of formulas. The destination for the copy can be a new
range or the original range. In the latter case, Excel replaces the original formulas with
their current values.
Values & Number Formatting (A) Pastes the results of formulas plus the number
formatting.
Values & Source Formatting (E) Pastes the results of formulas plus all formatting.
Formatting (R) Pastes only the formatting of the source range.
Paste Link (N) Creates formulas in the destination range that refer to the cells in the
copied range.
Picture (U) Pastes the copied information as a picture.
Linked Picture (I) Pastes the copied information as a “live” picture that is updated if
the source range is changed.
Paste Special Displays the Paste Special dialog box (described in the next section).
Using the Paste Special dialog box –
For yet another pasting method, choose Home ➪ Clipboard ➪ Paste ➪ Paste Special to
display the Paste Special dialog box (see Figure below). You can also right-click and choose
Paste Special from the shortcut menu to display this dialog box. This dialog box has several options, some of which are identical to the buttons in the Paste drop-down menu. The
options that are different are explained in the following list.
Comments Copies only the cell comments from a cell or range. This option doesn’t copy
cell contents or formatting.
Validation Copies the validation criteria so that the same data validation will apply. Data
validation is applied by choosing Data ➪ Data Tools ➪ Data Validation.
All using Source theme Pastes everything but uses the formatting from the document
theme of the source. This option is relevant only if you’re pasting information from a dif-
ferent workbook, and the workbook uses a different document theme than the active
workbook.
Column widths Pastes only column width information
All merging conditional formats Merges the copied conditional formatting with any con-
ditional formatting in the destination range. This option is enabled only when you’re copy-
ing a range that contains conditional formatting.