The Paste Special Function

Excel allows you to copy and paste specific information from one cell to another through the Paste Special function. The table below describes the differences amongst the various Paste Special options.

Paste Special Function
Description
All Every aspect of the original cell is copied and pasted into the copied cell.
Formulas Only the formula of the original cell is copied and pasted. The rules on relative and absolute references will apply.
Values Only the value of the original cell is copied and pasted. For example, if the original cell has a formula of =4+5, the copied cell will contain the value 9 and not the formula. Any subsequent changes that you make to the original cell will not be conveyed to the copied cell(s).
Formats Only the format of the original cell is copied and pasted. For example, if the original cell has red font, bold, and green color background, only those aspects of the cell will be pasted onto the copied cell.
Comments Only a cell comment will be copied and pasted.
Validation Only data validation rules are copied and pasted. Data validation rules are restrictions that you create for data entry. For example, you might want your cells only to accept whole numbers, as opposed to decimals.
All Except Borders Everything will be copied and pasted form the original cell except for any border formatting.
Column Widths The width of one column will be copied to another column.
Formulas and Number Formats The formula and the formatting of the original cell is copied and pasted. The rules on relative and absolute references will apply.
Values and Number Formats Only the value and the formatting of the original cell is copied and pasted.
Operation Specifies which mathematical operation, if any, you want to applied to the copied data.
Skip Blanks Avoids replacing values in your paste area when blank cells occur in the copy area.
Transpose Changes the columns of copied data to rows, and vice versa. In other words, if the copied information is contained in two rows across the spreadsheet, the pasted information will appear in two columns going down the sheet
Paste Link Links the pasted data to the active worksheet.

To use the Paste Special function, follow the instructions below.

  1. Select the cell(s) that contains the information you want to copy.
  2. Click on the word Edit in the Menu bar.
  3. Choose the option Copy.
  4. Animated lines will surround the cell(s).
  5. Move the cursor to the desired new location, clicking there once.
  6. Click on the word Edit in the Menu bar again.
  7. Choose the option Paste Special.
  8. The Paste Special dialog will appear.

  9. Select the desired option.
  10. Click on the OK button.
  11. The copied information will be moved to the new location.

Click HERE to return to the Table of Contents.