Move or Copy Ranges

About move or copy ranges

Using move or copy range you can 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 destination range.
• Copy a range to another range.
• Move a range of cells to another location.

When you move or copy ranges, by default Excel moves or copies all data that they contain, including contents, formulas, comments, formats, and hidden cells. If the cell or range contains a formula, the cell references in the copied formulas are changed automatically to be relative to their new destination, however, the new contents might display the #REF! error value. If that happens, you can adjust the references manually.

The primary difference between move and copy ranges 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.

When you move or copy ranges, Excel displays an animated moving border around cells. To cancel a moving border, press Esc.

Move or copy ranges by using Ribbon commands

1. Select a cell or range that you want to move or copy.
2. Do one of the following:
   • To move ranges, choose Home > Clipboard group > Cut.
   • To copy ranges, choose Home > Clipboard group > Copy.
3. Select the destination cell.
4. Choose Home > Clipboard group > Paste button.
   Tip

   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.

   Tip

   When you paste information, Excel overwrites any cells that get in the way without warning you. If you find that pasting overwrote some essential cells, choose Undo from the Quick Access Toolbar (or press Ctrl+Z).

Move or copy ranges by using shortcut menu commands

1. Right-click the range and choose Copy (or Cut) from the shortcut menu to copy the selected cells to the Clipboard.
2. Right-click and choose Paste button (or other Paste Options Buttons) from the shortcut menu that appears to paste the Clipboard contents to the selected cell or range.
   

Move or copy ranges by using shortcut keys

1. Select a cell or range that you want to move or copy.
2. Do one of the following:
   • To move ranges, press Ctrl+X.
   • To copy ranges, press Ctrl+C.
3. Select the destination cell.
4. Press Ctrl+V.
   Tip

   Instead of Ctrl+V, 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.

Move or copy ranges by using the mouse

1. Select a cell or range that you want to move or copy.
2. Do one of the following:
   • Cut and replace: Point to the border of the selection. When the pointer becomes a move pointer , drag the cell or range to another location. Excel warns you if you are going to overwrite existing cell contents. Press Cancel to avoid replacing.
     
   • Copy and replace: Hold down Ctrl while you point to the border of the selection. When the pointer becomes a copy pointer , drag the cell or range to another location. Excel doesn't warn you if you are going to overwrite existing cell contents. Press Ctrl+Z if you don't want to replace.
   • Cut and insert: Hold down Shift while you point to the border of the selection. When the pointer becomes a move pointer , drag the rows or columns to another location.
   • Copy and insert: Hold down Shift and Ctrl while you point to the border of the selection. When the pointer becomes a move pointer , drag the rows or columns to another location.

Copy to adjacent cells

To an adjacent range

1. Activate the cell that you're copying.
2. Extend the cell selection to include the cells to which you're copying.
3. Do one of the following:
   • Home > Editing > Fill > Down (or Ctrl+D) copy the cell to the selected range below.
     
   • Home > Editing > Fill > Right (or Ctrl+R) copy the cell to the selected range to the right.
   • Home > Editing > Fill > Up copy the cell to the selected range above.
   • Home > Editing > Fill > Left copy the cell to the selected range to the left.

To an adjacent cell

• Select an adjacent right cell, press Ctrl+R copy all data from cell left.
• Select an adjacent below cell, do one of the following:
  • Ctrl+D copy all data from cell above.
  • Ctrl+' copy formula from cell above.
  • Ctrl+Shift+" copy value from cell above.

Copy a range to multiple sheets

Copy to the same location by using the Across Worksheets command

If you want to copy a range to the same location on multiple sheets, do the following:

1. Select the range to copy.
2. Hold down 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.
3. Choose Home > Editing > Fill > Across Worksheets.
   
4. A Fill Across Worksheets dialog box appears to ask you what you want to copy (All, Contents, or Formats).
   
5. Make your choice and then click OK.

Copy to any location

If you want to copy a range to the any location on multiple sheets, For example: if you want to copy Range A1:B5 of Sheet1 to Range E1 of Sheet2, Sheet4, and Sheet5, do the following:

1. Select the range A1:B5 of Sheet1.
2. Press Ctrl+C.
3. Click a sheet tab (Sheet2) and hold down Ctrl and click the other sheet tabs (Sheet4 and Sheet5) for the worksheets to which you want to copy the information. Excel displays group in the workbook's title bar.
4. Release the Ctrl when you are done selecting.
5. Select the destination cell (Cell E1 of any selected sheet).
6. Press Ctrl+V.

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 information 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.

Open the Office Clipboard task pane

Choose Home > Clipboard group > click the dialog box launcher on the bottom right to toggle the Clipboard task pane on and off.

Use the Clipboard task pane to copy and paste multiple items

1. Open the Office Clipboard task pane.
2. Copy information from Excel, web pages, Word... and repeat this process.
3. As soon as you copy the information, the Office Clipboard task pane shows you the items that you've copied and a brief description in the lower right corner of Excel (up to 24 items).
   
4. Select the cell which you want to paste information. 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.
5. You can clear the contents of the Office Clipboard by clicking the Clear All button. Clearing the Office Clipboard also clears the Windows Clipboard.

Paste special

When pasting copied data, you may not always want to copy everything in the source range to the destination range. For example, you may only want to paste values, cell formatting, or formulas.

To control what is copied to the destination range, choose Home > Clipboard > Paste and use the drop-down menus shown below.

When you hover over an icon, you'll see a preview of the pasted information within the destination range. Click the icon to use the selected paste option.

Using the Paste dropdown menu

1. Select a cell or range that you want to move or copy.
2. On the Home tab, in the Clipboard group, click Copy or press Ctrl+C.
3. Select the upper-left cell of the paste area or the cell where you want to paste the value, cell format, or formula.
4. On the Home tab, in the Clipboard group, click the arrow below Paste, and then do one of the following:
   • Paste (P): Paste all cell contents including contents, formulas, formatting and data validation.
   • Formulas (F): Pastes formulas only.
   • 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, all formatting 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): Copied image with a link to the original cells (if you make any changes to the original cells those changes are reflected in the pasted image).
   • Paste Special: Displays the Paste Special dialog box.

Using the Paste Special dialog box

To display the Paste Special dialog box, you need to copy a cell or range (Choosing Home > Clipboard > Cut doesn't work.), then do one of the following:

• Choose Home > Clipboard > Paste > Paste Special.
• Right-click and choose Paste Special from the shortcut menu.
• Press Ctrl+Alt+V.

The Paste Special dialog box options

You can also specify a mathematical operation to apply to the copied data.

The following example shows that copying A2:A8 to B2:B8 and skipping blanks results in C3:C8:

The following example shows that copying A1:C3 to A6:C8 with the transpose option: