Excel Copy And Paste

It is worth taking a few minutes to familiarise yourself with the different ways you can copy and paste data in Excel.

Until you know about all the different shortcuts and methods of copying and pasting in Excel, you will not be able to appreciate how much time and effort can be saved by introducing these into your daily working practises.

This page provides a brief overview of a Simple Excel Copy & Paste, as well as the Excel Paste Special command. We also provide a description of the ExcelCopy-Paste shortcuts CTRL-D and CTRL-R.

Simple Excel Copy and Paste

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

1. Select an Excel 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 of the Excel ribbon (or the Edit menu in older versions of Excel);
   • Using the keyboard shortcut, CTRL-C (i.e. select the CTRL key and while holding this down, press C).
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 of the Excel ribbon (or the Edit menu in older versions of Excel);
   • Using the keyboard shortcut, CTRL-V (i.e. select the CTRL key and while holding this down, press V).

Note that 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 a detailed explanation of this.
• If you copy more than one cell, and then select a paste location that would result in the copied cells 'falling off' the edge or 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

Normally when you perform an Excel copy and paste, all information from the copied cell(s) is pasted into the new cell(s). This includes any formulas or other cell contents, and the cell formatting.

However, sometimes you might want to only paste one part of the original copied cells, (for example, you might want to paste the values but not the formulas or the formatting) into the new range. You can do this using the Excel 'Paste Special' command, which is found in the menu that appears when you right click the mouse.

The 'Paste Special' command can also be accessed from the home tab of recent versions of Excel (or the Edit menu in Excel 2003).

In recent versions of Excel (Excel 2010 or Excel 2013), the right click menu and the 'Paste' menu contain 'Paste Special' shortcuts. Examples of these are shown below:

Examples of Paste Special Shortcuts:

Paste Special Values Example

A simple paste special example is shown in the two images below.

The first image shows the set of cells A1-A15, which have coloured formatting and contain data values. If we want to copy the values, but not the formatting of cells A1-A15, into cells B1-B15, you would first select and copy cells A1-A15, then select cell B1 (or cells B1-B15) and then select Paste Special (from the Excel ribbon or the mouse right-click menu). You will then be presented with the 'Paste Special' dialog box (also shown in the left image below). Select the option Values from this dialog box and click OK.

The image on the right below shows the result of the Paste Special. Note that the values from cells A1-A15 have been copied into cells B1-B15, but the formatting has not been copied across.

Paste Special Options	Paste Special Values Result

Of course, if you have one of the more recent version of Excel (Excel 2010 or later), you wouldn't need to open up the 'Paste Special' dialog box, as you can paste values only by using the Paste Special Values shortcut, .

Paste Special Transpose

You can see, from the above example, that 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. One example is the Transpose option which, in the example below, has been used to copy cells A1-A6, and paste these into the cell range C1-H1.

Before Paste Special Transpose	Paste Special Transpose Result

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.

An example of this is shown below. Columns A and B of the example spreadsheet both contain numeric 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.

Before Paste Special Subtract	Paste Special Subtract Result

Note that, in the above example, instead of subtracting every cell of column A from column B, we could have subtracted a single cell of column A from every cell of column B. To do this, simply copy a single cell to start with, instead of a range of cells. Then, as in the example above, click on column B and select thePaste Special→Subtract option.

Copy Using CTRL-D or CTRL-R

Another convenient way to copy the values from one (or more) cells into adjacent cells is to use the CTRL-D or CTRL-R shortcuts.

CTRL-D

The CTRL-D 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 CTRL-D (i.e. press the CTRL key and, while keeping this pressed down, press the d key), the contents from the row above the selected row are copied into the selected row.
   
   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 CTRL-D, 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.
   
   pressing CTRL-D copies the contents of cells A1-C1 into cells A2-C4

CTRL-R

The CTRL-R 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 CTRL-R (i.e. press the CTRL key and, while keeping this pressed down, press the r key), 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

Copy a Worksheet

Finally, it is worth mentioning how to duplicate a worksheet in Excel. To do this:

• Right-click with mouse on the tab at the bottom of the worksheet and select the option Move or Copy ...
• Tick the Create a copy box and click OK.

Excel Data Input

There are several different ways that you can input data into an Excel spreadsheet. Spending just a few minutes learning the different methods of data input, could save hours in the future, as you become much more efficient in your day-to-day work.

The most basic way to input data into Excel is to simply select a cell and type directly into it. However, if you want to go back and alter the contents of that cell (without deleting the existing cell contents), you need to put the cell into edit mode. To do this, you can either:

• Double click on the cell, or
• Select the cell you want to edit and then either:
  • Click in the formula bar, or
  • Press F2.

When a cell is in edit mode, a cursor appears (either in the cell or in the formula bar), and when you type or paste data into the cell, it appears alongside the existing cell contents. Also, when a cell is in edit mode, you can move your cursor through the cell contents, using either the mouse, or the left, right, up and down arrow keys on your keyboard.

Entering a Single Value Into a Range of Cells

Highlight cells and type required text or value

<CTRL>-Enter populates all cells

If you want to enter the same value into several cells, you can quickly do this by:

• Highlighting all the cells you want to populate;
• Typing the value or text (this should be displayed in the active cell only at this time);
• Pressing <CTRL>-Enter (i.e. press the Ctrl key, and while holding this down, press Enter).

This copies the text you have typed into all of the cells in the selected range.

Inputting a Series of Values

Note that you could also use the Excel Autofill to enter a constant value into several cells. However, the Excel Autofill can do much more than this, allowing you to automatically enter a series of values into your worksheet. See the Excel Autofill page for details.

Importing Data Into Excel

If you have data in a different file format, you can import this into Excel. For example, to import data from a text file:

• In Current Versions of Excel (2007 and later): Select the Data tab and from within this menu, select the From Text option

or

• In Excel 2003: Select the Data tab, and from within this menu, select Import External Data and then ImportData...

Use the browser to locate the text file with your data in it. Excel then takes you through a series of steps in which you can specifiy how the data in the text file is to be split into separate columns, how the data is formatted in the columns, and whereabouts in your spreadsheet you want to paste the imported data.

Excel Autofill

There are several different ways that you can input data into an Excel spreadsheet. Spending just a few minutes learning the different methods of data input, could save hours in the future, as you become much more efficient in your day-to-day work.

The most basic way to input data into Excel is to simply select a cell and type directly into it. However, if you want to go back and alter the contents of that cell (without deleting the existing cell contents), you need to put the cell into edit mode. To do this, you can either:

• Double click on the cell, or
• Select the cell you want to edit and then either:
  • Click in the formula bar, or
  • Press F2.

When a cell is in edit mode, a cursor appears (either in the cell or in the formula bar), and when you type or paste data into the cell, it appears alongside the existing cell contents. Also, when a cell is in edit mode, you can move your cursor through the cell contents, using either the mouse, or the left, right, up and down arrow keys on your keyboard.

Entering a Single Value Into a Range of Cells

Highlight cells and type required text or value

<CTRL>-Enter populates all cells

If you want to enter the same value into several cells, you can quickly do this by:

• Highlighting all the cells you want to populate;
• Typing the value or text (this should be displayed in the active cell only at this time);
• Pressing <CTRL>-Enter (i.e. press the Ctrl key, and while holding this down, press Enter).

This copies the text you have typed into all of the cells in the selected range.

Inputting a Series of Values

Note that you could also use the Excel Autofill to enter a constant value into several cells. However, the Excel Autofill can do much more than this, allowing you to automatically enter a series of values into your worksheet. See the Excel Autofill page for details.

Importing Data Into Excel

If you have data in a different file format, you can import this into Excel. For example, to import data from a text file:

• In Current Versions of Excel (2007 and later): Select the Data tab and from within this menu, select the From Text option

or

• In Excel 2003: Select the Data tab, and from within this menu, select Import External Data and then ImportData...

Use the browser to locate the text file with your data in it. Excel then takes you through a series of steps in which you can specifiy how the data in the text file is to be split into separate columns, how the data is formatted in the columns, and whereabouts in your spreadsheet you want to paste the imported data.