Sunday, October 17, 2004

Excel Paste Special Function

Most people know how to Copy and Paste in Excel. However few people know about the Paste Special function.

Open an excel spreadsheet and try these out. After selecting a cell select a target cell and then use Edit Paste Special to bring up the dialog box.

Paste
There are several different options available.
Formatting
Select Formats to only copy the cell formats from the source cell to the target cell. Note this includes conditional formatting.
Select Comments to only copy the cell comment from the source cell to the target cell.
Select Validation to only copy the cell validation options from the source cell to the target cell.
Select All Except Borders to copy the enitre cell content and formats except borders from the source cell to the target cell.
Select Column Widths to only copy the cell column width from the source cell to the target cell. This is especially useful if copying columns from one sheet to a blank new sheet. Paste Special the Column Widths first then paste the selection in normally afterwards.

Formulas
Select Formulas to only copy the cell formula from the source cell to the target cell. Note this does not change any formatting.
Select Formulas and Number Formats to only copy the cell formulas and number formats from the source cell to the target cell.

Values
Select Values to copy the cell contents as a value from the source cell to the target cell. This can be used to copy a selection that is derived by formula over itself so that it turns into values only. Useful if the original cell had a link to an external workbook that you want to break for example.
Select Values and Number Formats to copy the cell contents as a value and the number formats from the source cell to the target cell.

Operations
You can combine any of the Paste options with an Operation option but I won't discuss that here.
Arithmetic Functions
The simple use of these is to add, subtract, multiply or divide the target cell contents with the source cell contents.
For example copy a cell with a value of 3 and then Paste Special Add into a cell with a value of 4. It should change to a value of 7.
Copy a cell with 0 in it and Edit Paste Special Multiply onto a range of cells with formulas that return values to see that the formulas will be appended with *0 and the original formula will be inside brackets.

Transpose
This option enables you to switch a horizontal selection of cells into a vertical selection and vice versa. Be careful when doing this with cells with formulas linking other cells in them.

Links
Use this option to paste a range of cells into another range as a link back to the original range.


Posted by: Jethro | 11:14 PM |