There are several different ways to copy and paste data in Excel. Becoming familiar with and utilizing all the different shortcuts and methods of copying and pasting in Excel will enable the user to save both time and effort during daily work.

This page provides a brief overview of a simple Copy & Paste, as well as the Paste Special command. We also provide a description of the Excel Copy-Paste shortcuts CTRLDCTRLR, and CTRLV.

Simple Excel Copy and Paste

The most simple Excel copy & paste is done by the following steps:

  1. Select a cell, or range of cells.
  2. Copy the cell(s) by either:

    • Right clicking with the mouse and selecting Copy from this menu;
    • Selecting Copy from the home tab (or the Edit menu in older versions of Excel);
    • Using the keyboard shortcut, CTRLC.
  3. Click on the location where you want to paste the copied cell(s).
  4. Paste the copied cell(s) by either:

    • Right clicking with the mouse and selecting Paste from this menu;
    • Selecting Paste from the home tab (or the Edit menu in older versions of Excel);
    • Using the keyboard shortcut, CTRLV.

Note: There are a few Excel copy and paste rules that you should be aware of:

  • When you copy cells containing formulas, the cell references within the formulas will be altered, unless they are made absolute by placing the $ symbol before the column or row reference – see the pages on Absolute and Relative Cell References for more details of this.
  • If you copy more than one cell, and then select a paste location that would result in the copied cells going past the end of the spreadsheet, Excel will flag up an error and will not paste the copied data. This will occur if you copy a whole row or column and then attempt to paste this into a internal cell in the spreadsheet.

Paste Special

excel-cut-paste01Normally when users perform a copy and paste, all information from the copied cells is pasted into the new cells. This includes any formulas or other cell contents, and the cell formatting.

However, there are times when you might want to only paste one part of the original copied cells, e.g., you might want to paste the values but not the formatting. You can do this using the Paste Special command, which is found in the menu that appears when you right click the mouse.

Paste Special Values Example

A simple paste special example is shown in the image at right. This examples shows the set of cells A2-A6, formatted as dollar values. If we want to copy the values, but not the formatting of cells A2-A6, into cells B2-B6, we would first select and copy cells A2-A6, then select cell B2 (or cells B2-B6) and click on Paste Special. You will then see the Paste Special options box shown in the image. Select the option Values from this box and click OK.

Beginning with Excel 2010 , you don’t need to enter the full Paste Special menu; you can paste values only by using the Paste Special Values shortcut,  Paste Special Values Shortcut.

Paste Special Transpose

The Values option is just one of several options linked to the Excel Paste Special feature.

As well as pasting selected attributes of the copied cells, the Paste Special command can be used to perform simple transformations. In the example below, the transpose option has been used to copy cells A1 – A6 and paste these into the cell range C1 – H1.

excel-cut-paste03

Use Paste Special to Perform Arithmetic Operations

The Paste Special command can also be used to perform a simple arithmetic operation on the contents of the target cells. The values in the copied cells are added to, subtracted from, multiplied by or used to divide the target cells.

In the example below, column A and Column B both contain numerical values and the Paste Special command is used to subtract the values in column A from the values in column B. This is done by copying column A, clicking on column B, and then selecting Paste Special, with the Subtract option.

  A B C
1 54 62  
2 32 97  
3 45 74  
4 33 96  
5 58 70  
6 43 74  
 
  A B C
1 54 8  
2 32 65  
3 45 29  
4 33 63  
5 58 12  
6 43 31  
Before Paste Special Subtract   Paste Special Subtract Result

Note: In the above example, instead of subtracting every cell in column A from column B, a single cell of column A could be subtracted from every cell of column B. Simply copy a single cell to start with, instead of a range of cells, then click on column B and select the Paste SpecialSubtract option.

Copy Using CTRL-D or CTRL-R

A very convenient way to copy the values from one or more cells into the cells below is to use the CTRLD or CTRLR shortcuts.

CTRL-D

The CTRLD shortcut copies the contents of a cell or row into the cell(s) below. There are two ways in which this shortcut works:

  1. If you select one or more cells in a single row and press CTRLD, the contents from the row above the selected row are copied into the selected row.

    excel-cut-paste04

    Pressing CTRL-D copies the contents of cells A1 – C1 into cells A2 – C2

  2. If you select cells in more than one row and press CTRLD, the contents from the cells in the top row of the selected range are copied down to all the other rows in the selected range.

    excel-cut-paste05

    Pressing CTRL-D copies the contents of cells A1 – C1 into cells A2 – C5

CTRL-R

The CTRLR shortcut copies the contents of a cell or column into the cell(s) to the right. Again, there are two ways in which this shortcut works:

  1. If you select one or more cells in a single column and press CTRLR, the contents from the row to the left of the selected column are copied into the selected column.

    Pressing CTRL-R copies the contents of cells A1 – A3 into cells B1 – B3

  2. If you select cells in more than one column and press CTRL-R, the contents from the cells in the leftmost column of the selected range are copied across to all the other columns in the selected range.

    Pressing CTRL-R copies the contents of cells A1 - A3 into cells B1 - D3

    Pressing CTRL-R copies the contents of cells A1 – A3 into cells B1 – D3

Copy a Worksheet

To duplicate a worksheet in Excel do this :

  • Right-click with mouse on the tab at the bottom of the worksheet and select the option Move or Copy …

  • Click the Create a copy box and click OK.