Excel formula with $ sign

In this post, we will discuss Excel formula with $ sign.

Many times you may have received an Excel file from colleague or someone else, where you find $ sign in formula like A$1+B2 or $A$1+B2 or $A1+B2.

This formula format is different from normal addition which looks like A1+B2.

What the person did here, is using this $ sign, he made that particular column (A) or row (1) frozen.

This means that when this particular formula is copied to other cell, the column or row with $ sign will remain the same in the other cell also.

It is also called absolute reference, whereas the normal cell formula is relative reference which changes when copied to another cell. Now I will explain you this with an example.

TotalPercentage
Men2050%
Women10#DIV/0!
Children10#DIV/0!
40

I have listed data here, where I need to compute percentage of men, women and children. One way is to divide each figure with the total one by one.

But, I want to save time (because time is money 😉). However when I simply copy the formula to next cell, it will take relative reference and move one cell down in copying.

ExampleTotalPercentage
Men20=C17/C20
Women10=C18/C21
Children10=C19/C22
40

To stop Excel changing cell C20 , I have to make my row absolute with the help of dollar sign.

TotalPercentage
Men20=C24/C$27
Women10=C25/C$27
Children10=C26/C$27
40

You can see above, now when I copied C27 with $ sign at for Row 27, it kept the same cell reference in the formula.

TotalPercentage
Men2050%
Women1025%
Children1025%
40

Tip: To toggle view mode to formulas instead of value, the shortcut is Ctrl + `

In the above example, we freeze row only, now lets look an example where we will freeze row and column both.

Present(Cell D33)     
DataAttendance
StudentDay 1Day 2Day 3Day 1Day 2Day 3
AyesnoyesPresentPresent
BnoyesnoPresent
CyesnoyesPresentPresent

As you can see, we have to evaluate if formula here and bring only 1 result “Present” if the condition is met.

With the help of $ sign, we made cell with Present as absolute reference.

Present(Cell D33)     
DataAttendance
StudentDay 1Day 2Day 3Day 1Day 2Day 3
Ayesnoyes=IF(C36=”yes”,$D$33,””)=IF(D36=”yes”,$D$33,””)=IF(E36=”yes”,$D$33,””)
Bnoyesno=IF(C37=”yes”,$D$33,””)=IF(D37=”yes”,$D$33,””)=IF(E37=”yes”,$D$33,””)
Cyesnoyes=IF(C38=”yes”,$D$33,””)=IF(D38=”yes”,$D$33,””)=IF(E38=”yes”,$D$33,””)

Tip: If you also want to bring empty cell, instead of 0 if the condition is false, use two “” together.

Hope you understood the reason for Excel formula with $ sign. If you still have any queries, let me know in the comments.

Please do share with others. May be use of $ sign is basic for you, but this specific query, Google receives more than 40,000 times a month. So, it is worth sharing to help others, because sharing is caring. 😉


Back to home: usamawrites.xyz