Using the Copy and Paste commands in Microsoft Excel, does not just copy the cell data; the formatting, style and number formats are also copied. The Paste Special command allows you to be selective about what you are pasting. The Paste Special command can be used to import elements from other programs into Excel using Windows Object Linking and Embedding. When doing so, the option "Paste Link" can be used to create a link to the original data rather than pasting in a copy of it.
If you are copying and pasting data purely within an Excel spreadsheet, the Paste Special command offers a set of options which are unique to the Excel environment. Interestingly enough, the Paste Special command is only available if the Excel data is copied rather than cut. Note also that it is often useful to paste over the original data, using the options in the Paste Special dialog to transform the original data in some way. Let's now look at the key options which are available when using the Paste Special command.
If the radio button labelled "All" is chosen, Excel will paste all the data on the clipboard in much the same way as it would do if the ordinary Paste command had been used.
Choosing the "Formulas" radio button causes Excel to ignore any cell formatting and paste only the data contained in the cells being copied. (The term "formulas" is used in a broad sense here and does not imply that Excel will only copy cells that contain formulas.)
I personally find the "Values" option particularly useful: it can be used to "freeze" dynamic data. Like the "Formulas" option, "Values" causes Excel to paste only data and ignore formatting. The key distinction is that if the copied cell contains a formula, Excel will paste the calculated result of the formula rather than the formula itself.
The "Formats" option is pretty much the same as using the Format Painter tool. It pastes the formatting from the copied cells but does not paste any data.
The "Comments" options copies only comments that were in the source range, ignoring all other data.
Similarly, the "Validation" option will copy only cells that have had validation rules applied to them.
I hope this gives you an idea of how the Paste Special command works. Let's just mention one final option: "Transpose". This option reverses the orientation of the original data, so that rows become columns and vice versa.
If you are copying and pasting data purely within an Excel spreadsheet, the Paste Special command offers a set of options which are unique to the Excel environment. Interestingly enough, the Paste Special command is only available if the Excel data is copied rather than cut. Note also that it is often useful to paste over the original data, using the options in the Paste Special dialog to transform the original data in some way. Let's now look at the key options which are available when using the Paste Special command.
If the radio button labelled "All" is chosen, Excel will paste all the data on the clipboard in much the same way as it would do if the ordinary Paste command had been used.
Choosing the "Formulas" radio button causes Excel to ignore any cell formatting and paste only the data contained in the cells being copied. (The term "formulas" is used in a broad sense here and does not imply that Excel will only copy cells that contain formulas.)
I personally find the "Values" option particularly useful: it can be used to "freeze" dynamic data. Like the "Formulas" option, "Values" causes Excel to paste only data and ignore formatting. The key distinction is that if the copied cell contains a formula, Excel will paste the calculated result of the formula rather than the formula itself.
The "Formats" option is pretty much the same as using the Format Painter tool. It pastes the formatting from the copied cells but does not paste any data.
The "Comments" options copies only comments that were in the source range, ignoring all other data.
Similarly, the "Validation" option will copy only cells that have had validation rules applied to them.
I hope this gives you an idea of how the Paste Special command works. Let's just mention one final option: "Transpose". This option reverses the orientation of the original data, so that rows become columns and vice versa.
About the Author:
The The writer of this article is a trainer and developer with a UK IT training company offering Microsoft Excel 2007 Classes, as well as Excel VBA training at their central London training centre.
Posts
No comments:
Post a Comment