Paste Special when copying from Excel

Use the Paste Special dialog box to copy complex items from an Excel worksheet and paste them into the same worksheet or another worksheet using only specific attributes of the copied data, or a mathematical operation that you want to apply to the copied data.

To see the Paste Special dialog box, copy the items and then go to Home > Clipboard > Paste > Paste Special.

Paste Special dialog box

Paste

Select an option to indicate how you want copied data to be pasted.

Paste option

Action

All

Pastes all cell contents and formatting of the copied data.

Formulas

Pastes only the formulas of the copied data as entered in the formula bar.

Values

Pastes only the values of the copied data as displayed in the cells.

Formats

Pastes only cell formatting of the copied data.

Comments

Pastes only comments attached to the copied cell.

Validation

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

All using Source theme

Pastes all cell contents in the document theme formatting that is applied to the copied data.

All except borders

Pastes all cell contents and formatting applied to the copied cell except borders.

Column widths

Pastes the width of one copied column or range of columns to another column or range of columns.

Formulas and number formats

Pastes only formulas and all number formatting options from the copied cells.

Values and number formats

Pastes only values and all number formatting options from the copied cells.

All merging conditional formats

Pastes the contents and conditional formatting options from the copied cells.

Mathematical Operation

Specify which mathematical operation, if any, that you want to apply to the copied data. For example, put 10 in a cell, press Ctrl+C to copy it. Next, select the cells you want to apply the operation to, and go to Home > Clipboard > Paste > Paste Special > select the Operation you want, such as Multiplication, and press OK.

Paste Operation

Action

None

Specifies that no mathematical operation will be applied to the copied data.

Add

Adds the copied data to the data in the destination cell or range of cells.

Subtract

Subtracts the copied data from the data in the destination cell or range of cells.

Multiply

Multiplies the copied data with the data in the destination cell or range of cells.

Divide

Divides the copied data by the data in the destination cell or range of cells.

Other

Paste Operation

Action

Skip blanks

Avoids replacing values in your paste area when blank cells occur in the copy area when you select this check box.

Transpose

Changes columns of copied data to rows and vice versa when you select this check box.

Paste Link

Links the pasted data on the active worksheet to the copied data.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Copy cell data and paste attributes only

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×