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

Select a cell or range that you want to move or copy.
Do one of the following:
- To move ranges, choose Home > Clipboard group > Cut.
- To copy ranges, choose Home > Clipboard group > Copy.
Select the destination cell.
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

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

Select a cell or range that you want to move or copy.
Do one of the following:
- To move ranges, press Ctrl+X.
- To copy ranges, press Ctrl+C.
Select the destination cell.
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

Select a cell or range that you want to move or copy.
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.

Tip

You cannot move or copy nonadjacent ranges by using the mouse.

Tip

If the mouse pointer doesn't turn into an arrow when you point to the border of a cell or range, you need to make a change to your settings. Choose File > Options > Advanced tab > Editing options > Tick Enable fill handle and cell drag-and-drop.

Copy to adjacent cells

To an adjacent range

Activate the cell that you're copying.
Extend the cell selection to include the cells to which you're copying.
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.

Tip

You also can use AutoFill to copy to adjacent cells by dragging the selection's fill handle (the small square in the bottom-right corner of the selected cell or range). Excel copies the original selection to the cells that you highlight while dragging. For more information, see Entering data with Auto Fill and Flash Fill.

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:

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

Tips

Be careful with the Home > Editing > Fill > Across Worksheets command because Excel doesn't warn you when the destination cells contain information. You can quickly overwrite lots of cells with this command and not even realize it. So make sure that you check your work, and use Undo (Ctrl+Z) if the result isn't what you expected.

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:

Select the range A1:B5 of Sheet1.
Press Ctrl+C.
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.
Release the Ctrl when you are done selecting.
Select the destination cell (Cell E1 of any selected sheet).
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.

Tip

To make the Clipboard task pane open automatically, click the Options button near the bottom of the Clipboard task pane and choose the Show Office Clipboard Automatically option.

Use the Clipboard task pane to copy and paste multiple items

Open the Office Clipboard task pane.
Copy information from Excel, web pages, Word... and repeat this process.
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).
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.
You can clear the contents of the Office Clipboard by clicking the Clear All button. Clearing the Office Clipboard also clears the Windows Clipboard.

Tip

If you copy a range that contains formulas, the formulas are not transferred when you paste from the Clipboard task pane to a different range. Only the values are pasted.

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

Select a cell or range that you want to move or copy.
On the Home tab, in the Clipboard group, click Copy or press Ctrl+C.
Select the upper-left cell of the paste area or the cell where you want to paste the value, cell format, or formula.
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.

Tip

Once pasted, you'll have another chance to change your mind. The Paste Options drop-down list appears in the lower right corner of the pasted range. Click it (or press Ctrl) and you'll see the Paste Options icon again.

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

Paste option	Action
All	Pastes all cell contents and formatting of the copied data.
Formulas	Pastes only the formulas of the copied data as entered in the formula bar.
Values	Pastes only the values of the copied data as displayed in the cells.
Formats	Pastes only cell formatting of the copied data.
Comments and Notes	Pastes only comments and notes attached to the copied cell.
Validation	Pastes data validation rules for the copied cells to the paste area.
All using Source theme	Pastes all cell contents in the document theme formatting that is applied to the copied data.
All except borders	Pastes all cell contents and formatting applied to the copied cell except borders.
Column widths	Pastes the width of one copied column or range of columns to another column or range of columns.
Formulas and number formats	Pastes only formulas and all number formatting options from the copied cells.
Values and number formats	Pastes only values and all number formatting options from the copied cells.
All merging conditional formats	Pastes the contents and conditional formatting options from the copied cells.

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

Operation	Action
None	Specifies that no mathematical operation will be applied to the copied data.
Add	Adds the copied data to the data in the destination cell or range of cells.
Subtract	Subtracts the copied data from the data in the destination cell or range of cells.
Multiply	Multiplies the copied data with the data in the destination cell or range of cells.
Divide	Divides the copied data by the data in the destination cell or range of cells.

Other options	Action
Skip blanks	Avoids replacing values in your paste area when blank cells occur in the copy area when you select this check box.
Transpose	Changes columns of copied data to rows and vice versa when you select this check box.
Paste Link	Click to create a link to the copied cell(s).

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: