Saturday, February 21, 2004
New for this blog. I am going to do an Excel tip of the day. It may not be every day, but it will be a useful tip.
They will all be boxed in this cool colour.
If you have any questions about the use of this free Excel tip - or need more help using Paste Special - feel free to add a comment and an email address and I will get back to you, and I will paste the answer in the comments also.
If you have any suggestions for tips leave a comment also.
They will all be boxed in this cool colour.
Excel Tip of The Day
Paste Special
When you copy something to the clipboard you can then paste it in several ways.
You can paste it as a link, as a value, as formats, formulas etc. To do this instead of pasting as per normal, right click and select Paste Special. (or Edit, Paste Special) This brings up the Paste Special Dialog box and allows you to select your activity. You can do this multiple times and paste different components.
Here are a few ideas that you can use.
Paste Special Formulas Use this when you want to copy a formula but don't want to change the editing on the target cell(s)
Paste Special Values Use this when you want to convert a selection (or single cell) from formulas or links to just the current calculated value. Full resolution of formulas to maximum decimal places will occur even if formatting doesn't show it. This is useful to cut links from external files, replace temporary formulas with actual results etc.
Paste Special Links Use this to quickly paste the link to an external spreadsheet by copying from that sheet and paste links into the target cell(s)
Paste Operation - Multiply, Add, Divide, Subtract These are very powerful tools. Try this
Find a selection of formulas (eg sums at the bottom of a range). Enter 0 (zero) in a blank cell then copy that cell.
Select the range you want to alter and Paste Special Operation Multiply. (You may want to click Formulas as well so as to not change target cell(s) formatting).
This will add to your existing formula *0 (and any required brackets) and the result will become zero. This can be used in all sorts of ways, - eg dividing numbers by 1,000 to change $ to $'000 etc.
Paste Special Transpose Use this to alter the orientation of a selection of cells. Copy a column and turn it into a row and vice versa.
Paste Special
When you copy something to the clipboard you can then paste it in several ways.
You can paste it as a link, as a value, as formats, formulas etc. To do this instead of pasting as per normal, right click and select Paste Special. (or Edit, Paste Special) This brings up the Paste Special Dialog box and allows you to select your activity. You can do this multiple times and paste different components.
Here are a few ideas that you can use.
Paste Special Formulas Use this when you want to copy a formula but don't want to change the editing on the target cell(s)
Paste Special Values Use this when you want to convert a selection (or single cell) from formulas or links to just the current calculated value. Full resolution of formulas to maximum decimal places will occur even if formatting doesn't show it. This is useful to cut links from external files, replace temporary formulas with actual results etc.
Paste Special Links Use this to quickly paste the link to an external spreadsheet by copying from that sheet and paste links into the target cell(s)
Paste Operation - Multiply, Add, Divide, Subtract These are very powerful tools. Try this
Find a selection of formulas (eg sums at the bottom of a range). Enter 0 (zero) in a blank cell then copy that cell.
Select the range you want to alter and Paste Special Operation Multiply. (You may want to click Formulas as well so as to not change target cell(s) formatting).
This will add to your existing formula *0 (and any required brackets) and the result will become zero. This can be used in all sorts of ways, - eg dividing numbers by 1,000 to change $ to $'000 etc.
Paste Special Transpose Use this to alter the orientation of a selection of cells. Copy a column and turn it into a row and vice versa.
If you have any questions about the use of this free Excel tip - or need more help using Paste Special - feel free to add a comment and an email address and I will get back to you, and I will paste the answer in the comments also.
If you have any suggestions for tips leave a comment also.
