Wednesday, January 6, 2010

Paste Special Revisited

Back last June we took a look at how you can use Paste Special to preserve cells’ Values (rather than formulas).

Whereas that is cool, Paste Special has other valuable tricks! Here are a couple of my favorites:

1) Let’s say you would like to Multiply (works for other arithmetic functions as well) a range of cells by a certain value (For instance, perhaps you would like to increase all of your projections by 10%). Simply do the following:

a) Enter the value you wish to have as a multiplier (e.g. 1.10) in a cell, right-click with your mouse and copy it.
b) Select the range you wish to multiply, right-click and choose Paste Special
c) From the Paste Special menu, choose Multiply, click OK and Bamm! All of your cells in the range have been increased by 10%.

2) Another favorite of mine is the Transpose option on the Paste Special menu. Let’s say that you have created a range in a column, and you now wish you had made the range a row rather than a column. Paste Special to the Rescue! Simply do the following:

a) Select the range in the Column, right-click Copy and choose your location cell where you want the Row range to begin
b) Choose Paste Special
c) Select the Transpose option box and click OK.  Bamm! Your range has been changed from a column to a row (row to column works as easily).

How Cool is That?!? As I said back in June, Paste Special is our Friend!