Monday, February 19, 2018

Copy cell data and paste attributes only

Copy cell data and paste attributes only

There are two ways to paste specific cell contents or attributes like a formula, format, or comment copied from another cell. You can either pick a specific paste option directly from the Paste menu or click Paste Special, and pick an option from the Paste Special box.

for paste special options, click paste on the Home tab

  1. Select the cell with the attribute you want to copy and press Ctrl+C.

    Tip:  To copy a selection to a different worksheet or workbook, click another worksheet tab or switch to another workbook.

  2. Click the upper-left cell of the paste area and use either the Paste options or the Paste Special options.

Paste menu options

Click Home > Paste and pick the specific paste option you want. For example, to paste only formatting from the copied cell, click Formatting the paste formatting button . This table shows the options available in the Paste menu:

icon

Option name

Paste result

paste cell content

Paste

All cell contents.

Icon for pasting value an keeping source column width

Keep Source Column Widths

Copied cell content along with its column width.

Paste copied values by swithing columns to rows and vice versa.

Transpose

Reorients the content of copied cells when pasting. Data in rows is pasted into columns and vice versa.

paste values with source formatting

Values & Source Formatting

Values and formatting from copied cells.

Paste link option

Paste Link

Reference to the source cells instead of the copied cell contents.

Paste copied picture

Picture

Copied image.

Paste linked picture

Linked Picture

Copied image with a link to the original cells (if you make any changes to the original cells those changes are reflected in the pasted image).

Paste Special options

To use options from the Paste Special box, click Home > Paste > Paste Special.

Keyboard Shortcut: Press Ctrl+Alt+V.

Paste Special Dialog box

In the Paste Special box, pick the attribute you want to paste.

Note:  Depending on the type of data you copied and the Paste option you picked, some other options might be grayed out.

Option name

Paste result

All

All cell contents and formatting.

Comments

Only comments attached to the cell.

Validation

Only the data validation settings from the copied cells.

Column widths

Only column widths.

Common Paste and Paste Special options

You can use some of the paste special options from either the Paste menu or the Paste Special box. The option names might vary a bit but they results are the same:

Icon and option name in Paste menu and Paste Special box

Paste results

Paste formatting icon Formatting

Formats in Paste Special.

Only the formatting from copied cells.

paste Values only Values

Values (visible results of formulas and not the formulas).

Paste formulas icon Formulas

Only the formulas from copied cells.

Paste formulas and number formatting option Formulas & Number Formatting

Formulas and number formats in Paste Special.

Only formulas and number formats (not text formats) from copied cells.

paster values and number formatting Values & Number Formatting

Values and number formats in Paste Special.

Only the values (not formulas) and number formats from copied cells.

Paste cell content without borders No Borders

All except borders in Paste Special.

All copied cell contents except borders.

Paste value with source formatting Keep Source Formatting

All using Source theme in Paste Special.

Cell content with the formatting from copied cells.

For more about copying an entire worksheet to another location in the same workbook or to a different workbook, see Move or copy worksheets or worksheet data.

No comments:

Post a Comment