Sunday, July 22, 2018

Copy and paste specific cell contents

Copy and paste specific cell contents

You can copy and paste specific cell contents or attributes (such as formulas, formats, comments, and validation). By default, if you use the Copy Copy icon and Paste Paste Options button icons (or COMMAND + C and COMMAND + V), all attributes are copied. To pick a specific paste option, you can either use a Paste menu option or select Paste Special, and pick an option from the Paste Special box. Attributes other than what you select are excluded when you paste.

Paste menu, showing options and Paste Special

Paste menu options

  1. Select the cells that contain the data or other attributes that you want to copy.

  2. On the Home tab, click Copy  Copy icon .

  3. Click the first cell in the area where you want to paste what you copied.

  4. On the Home tab, click the arrow next to Paste, and then do any of the following. The options on the Paste menu will depend on the type of data in the selected cells:

    On the Home tab, select Paste

Select

To paste

Paste

All cell contents and formatting, including linked data.

Formulas

Only the formulas.

Formulas & Number Formatting

Only formulas and number formatting options.

Keep Source Formatting

All cell contents and formatting.

No Borders

All cell contents and formatting except cell borders.

Keep Source Column Widths

Only column widths.

Transpose

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

Paste Values

Only the values as displayed in the cells.

Values & Number Formatting

Only the values and number formatting.

Values & Source Formatting

Only the values and number color and font size formatting.

Formatting

All cell formatting, including number and source formatting.

Paste Link

Link the pasted data to the original data. When you paste a link to the data that you copied, Excel enters an absolute reference to the copied cell or range of cells in the new location.

Paste as Picture

A copy of the image.

Linked Picture

A copy of the 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).

Column widths

Paste the width of one column or range of columns to another column or range of columns.

Merge conditional formatting

Combine conditional formatting from the copied cells with conditional formatting present in the paste area.

Paste Special options

  1. Select the cells that contain the data or other attributes that you want to copy.

  2. On the Home tab, click Copy  Copy icon .

  3. Click the first cell in the area where you want to paste what you copied.

  4. On the Home tab, click the arrow next to Paste, and then select Paste Special.

    On the Home tab, select Paste

  5. Select the options you want.

    Paste Special box

Paste options

Select

To paste

All

All cell contents and formatting, including linked data.

Formulas

Only the formulas.

Values

Only the values as displayed in the cells.

Formats

Cell contents and formatting.

Comments

Only comments attached to the cell.

Validation

Only data validation rules.

All using Source theme

All cell contents and formatting using the theme that was applied to the source data.

All except borders

Cell contents and formatting except cell borders.

Column widths

The width of one column or range of columns to another column or range of columns.

Formulas and number formats

Only formulas and number formatting.

Values and number formats

Only values and number formatting options from the selected cells.

All, merge conditional formats

Combine conditional formatting from the copied cells with conditional formatting present in the paste area.

Operation options

The Operation options mathematically combine values between the copy and paste areas.

Click

To

None

Paste the contents of the copy area without a mathematical operation.

Add

Add the values in the copy area to the values in the paste area.

Subtract

Subtract the values in the copy area from the values in the paste area.

Multiply

Multiply the values in the paste area by the values in the copy area.

Divide

Divide the values in the paste area by the values in the copy area.

Other options

Click

To

Skip Blanks

Avoid replacing values or attributes in your paste area when blank cells occur in the copy area.

Transpose

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

Paste Link

If data is a picture, links to the source picture. If the source picture is changed, this one will change too.

Tip: Some options are available both on the Paste menu and in the Paste Special dialog box. The option names might vary a bit but the results are the same.

  1. Select the cells that contain the data or other attributes that you want to copy.

  2. On the Standard toolbar, click Copy  Copy button .

  3. Click the first cell in the area where you want to paste what you copied.

  4. On the Home tab, under Edit, click Paste, and then click Paste Special.

    Home tab, Edit group

  5. On the Paste Special dialog, under Paste, do any of the following:

    Click

    To

    All

    Paste all cell contents and formatting, including linked data.

    Formulas

    Paste only the formulas as entered in the formula bar.

    Values

    Paste only the values as displayed in the cells.

    Formats

    Paste only cell formatting.

    Comments

    Paste only comments attached to the cell.

    Validation

    Paste data validation rules for the copied cells to the paste area.

    All using Source theme

    Paste all cell contents and formatting using the theme that was applied to the source data.

    All except borders

    Paste all cell contents and formatting except cell borders.

    Column widths

    Paste the width of one column or range of columns to another column or range of columns.

    Formulas and number formats

    Paste only formulas and number formatting options from the selected cells.

    Values and number formats

    Paste only values and number formatting options from the selected cells.

    Merge conditional formatting

    Combine conditional formatting from the copied cells with conditional formatting present in the paste area.

    To mathematically combine values between the copy and paste areas, in the Paste Special dialog box, under Operation, click the mathematical operation that you want to apply to the data that you copied.

    Click

    To

    None

    Paste the contents of the copy area without a mathematical operation.

    Add

    Add the values in the copy area to the values in the paste area.

    Subtract

    Subtract the values in the copy area from the values in the paste area.

    Multiply

    Multiply the values in the paste area by the values in the copy area.

    Divide

    Divide the values in the paste area by the values in the copy area.

    Additional options determine how blank cells are handled when pasted, whether copied data is pasted as rows or columns, and linking the pasted data to the copied data.

    Click

    To

    Skip blanks

    Avoid replacing values in your paste area when blank cells occur in the copy area.

    Transpose

    Change columns of copied data to rows, or vice versa.

    Paste Link

    Link the pasted data to the original data. When you paste a link to the data that you copied, Excel enters an absolute reference to the copied cell or range of cells in the new location.

    Note: This option is available only when you select All or All except borders under Paste in the Paste Special dialog box.

See Also

Move or copy a sheet

Move or copy cells, rows, or columns

No comments:

Post a Comment