Excel help for adding $ sign to formula

This query is : Resolved

Anil (Querist)
Follow

30 March 2009 Hi,

I have range of cells with forumula which have references to other cells.

For example:: In cell A1 I have forumula "=B1*C1"

In cell A2 I have forumla" =B2*C2"

I want to select these two cells A1 and A2 and make forumulas in them permanent.

For example I want in cell A1 I want the forumula to be "=$B$1*$C$1" and so forth.

How to do this in one stroke for all selected cells. Is there any shortcuts for adding $ sign in formulas in cells?

Thanks

Tushar Yerawar

Tushar Yerawar (Expert)
Follow

30 March 2009 go to particular cell, press F2 and then go to particular reference and press F4 to put the $. Once it is done copy the formula in requisite cells.

CA. Amit Daga

CA. Amit Daga (Expert)
Follow

30 March 2009 Tushar if you will go in this way it will talke the same formula in all the cell. Like "=$B$1*$C$1" . If he want this formula in all the cell than its fine. But if he dont want this formula

Than why he need to change because anil if you will drag automatically it will multiply two previous cells only

Upgrade to CAclubindia Pro. Subscribe Now!

K V Subba Rao

K V Subba Rao (Expert)
Follow

30 March 2009 If we press F4, $ sign is prefixed to both vertical and horizontal references.

In a few situations, you need to keep only one constant and allow the other to keep changing

So depending on your requirement, prefix $ sign to the one which you need to be constant.

For example, in A1---

if you prefix $ sign to A, value in column A remains constant;

if you prefix $ sign to 1, value in row 1 remains constant

(Notes: Words used here by me may not be appropriate as per EDP standards, but it should be possible to understand concept)