Excel Combine data: pasting values, formulas, formats

Excel combine Data: 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.

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

Excel combine Data – 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 combine Data: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.

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 – Excel Combine 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).

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:

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 combine: HINT (1)

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 combine: HINT (2)

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:

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen