Computer Tips From A Computer Guy

Posted: 23 Mar 2009 03:06 AM PDT

Most of us using Excel and other MS Office tools might be very familiar with the basic copy and paste capability. You copy a part of the document (Control-C) and then you paste it on another part of the document (Control-V).

However did you know that in Excel there are several "special paste" options that you can use in Excel? In this tip we will show you how to use the special options so you can apply it to copy formulas and formula values.

First let's take a look at one of the options where you can copy formulas from an Excel cell or range of cells. First copy the range of cells that contains a formula:

Then position the cursor to the place where you want to paste the copied cells and then right click to access the "Paste Special…" window:

The Paste Special window will appear, choose Formulas then click OK:

The pasted values will be a "relative" formula result. The copied cells has a formula of C2=A2*B2 so when you copy it row D it will result in a shifted formula of B2*C2:

This is because we are copying a relative formula. The term relative is used because the formula variables are relative to the position of the formula. To change the reference from relative to absolute, position the cursor to the original formula's formula box and then put a $ sign on each variable used. The new formula should be $A2*$B2:

This means when you copy this formula, the variables will be retained:

Another tip that we would want to share to you is how to copy-paste the resulting values of formulas. To do this copy the cells you want then right click "Paste Special…" and then choose "Values" instead of "Formulas":