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