**Excel’s pasting function YOUR OBJECTIVE**

To record the first-quarter revenues from your company’s business locations, you created several spreadsheets in an *Excel* workbook (file). To be able to see the data more clearly, you recorded the revenues for each month on a separate worksheet. That is, there is a worksheet for January, another for February and a third for March.

Now, in another table, on an additional worksheet, you want to display the rows with the totals from each month and add them together, as well.

To create the new table, you might be tempted just to copy the *total* rows from each month’s worksheet and paste them into the new table. But that would not produce the results you want, because, when using the normal **excel *** Pasting * function, the entire contents of the source cells would be pasted. That is: the SUM functions in the months’ total rows, along with their cell references, would be pasted. But since the

*cell references*in those functions refer only to cells on their own worksheets, when pasted onto another worksheet, the cell references would definitely not point back to the original source data cells, but rather to positions on the new worksheet that definitely do not contain the original source data. In other words, using the normal excel

*Pasting function*after copying a cell that contains simple

*cell references*from one worksheet, will result in erroneous results when simply pasted into another worksheet.

To obtain the desired results after copying the cells of interest, it is necessary to use Excel’s *Pasting Special* function to paste just the values and not the formulas behind them .

Similarly, using *Paste Special*, it is also possible to paste just the copied cells’ formats to other cells.

This learning module “ Excel Pasting function” will show you how to use Excel’s *Paste Special* function to paste values, formulas and formats.

**Excel’s pasting function HOW DOES IT WORK?**

When pasting the contents of cells whose displayed values result from the use of *cell references* to another worksheet or workbook, you must use the *Paste Special…* function. The reason for this is that *Excel* cannot be properly resolve such cell references on other worksheets. To copy and paste cell contents in this fashion, first select, in the customary fashion, the range of cells you want to copy. Then click the ** Copy **button.

**Excel’s pasting function KEY COMBINATION:**

You can also use the **[Ctrl]+[C]** key combination to copy cells.

Then go to the the worksheet where you want to paste the copied data. With a click, position the cursor on the first cell where you want to paste the data.

Now go to the *Clipboard* group, on the *Home* tab. There you should click the small arrow on the ** Paste ** button. In the selection catalog that appears, click the

*Paste Special…*command.

Excel’s pasting function: The *Paste Special…* dialog box offers several options for pasting and processing data. This learning module discusses some of the ways to paste data. Another learning module – *Combining data: Pasting with mathematical operations* – discusses how to combine data using *Paste Special* with mathematical operations.

The left column of the *Paste* section includes the following options:

Paste the *entire contents* of the copied cell(s), including formats.

Paste any *formulas* from the copied cell(s) (visible in formula bar) into the target cell.

Paste only *displayed values* from the copied cell(s) into the target cell(s).

Paste only formatting information from the copied cell(s) onto the target cell(s).

Paste only attached comments, if any, from the copied cell(s) into the target cell(s).

Paste data validation rules, if any, from the copied cell(s) into the target cell(s).

The right column of the paste section includes the following options:

Paste the copied cell’s entire contents, including the *Source theme*, if one had been used.

Paste the copied cell’s entire contents, including formatting information, but not border specifications.

Paste the column width taken from the copied column or column range, applying it to the target’s column or column range.

Paste only formulas and number formats from the copied cell(s) onto the target cell(s).

Paste values and number formats from the copied cell(s) onto the target cell(s).

Excel’s pasting function: When you want to paste only values without their underlying formulas, first copy the cell or cells whose values you want to paste elsewhere. Then go to the worksheet that contains the cell or cells onto which you want to paste the values. On the target worksheet, move the cursor to the cell where you want to paste the copied values. Then open the *Paste Special…* dialog box by clicking *Home**→* *paste *

In the screenshot below, you see how values from January’s Totals row were pasted:

Excel’s pasting function: If you want to take formats from the *March* table and apply them to the *Total* table, for instance: first select the entire *March* table (on the *March* worksheet), and then copy that table range in the usual fashion. After going to the *Total* tab, position the cursor on the *Total* table where you want to begin applying the formatting. Now, as described above, call up the *Paste Special…* dialog box. Activate the *Formats* option and conclude by clicking ** OK**.

The formats taken from the *March* table will have been pasted onto the *Total* table.

**Excel’s pasting function HINT**

When you open the *Paste* selection catalog, before clicking the ** Paste Special…** command, you will see a subset of

*Paste*options. If the option you want to use is there, you can save yourself a couple of clicks, by clicking on it right there.

**Excel’s pasting function HINT**

You can also get to the *Paste Special…* dialog box by right-clicking in the target cell and following the path indicated in the next illustration: