Friday, October 1, 2010

Excel - Unchange cell reference while copy and pasting Formulas



How to create a set of formula that will always refer to one specific cells?

When you copy and paste formula the cell reference automatically changes.
First look at the image below carefully.
Under a
I have used formula in cell E5 as =D5/F3*C5
Then I have copied and paste the formula in below cells. You can see that the out put is wrong!
Why?

Under b
Here I have used formula in cell E16 as =D16/$F$14*C16
Then I have copied and paste the formula below.
Here the result is OK!

What is the difference. Yes the $ sign makes the difference.

Under a
If you type =D5/F3*$C$5 instead of =D5/F3*C5 then copy and paste the formula below, you will get the desired result.

I know that the example is enough for you to get a clear picture in using the $ symbol.

If you have any doubt please leave your query in the comment section below.

Note : You can use F4 button to put $ symbol
For example type = symbol, select any cell having numeric value and press F4.
Get Free Updates:
We will send a confirmation mail. Please check your inbox/spam folder to confirm it
Related Posts Plugin for WordPress, Blogger...
 

Copyright © 2011 All Rights Reserved | Designed by Amith | Post RSS | Home | About | Disclaimer | Privacy Policy | Contact