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.
| Total | Percentage | |
| Men | 20 | 50% |
| Women | 10 | #DIV/0! |
| Children | 10 | #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.
| Example | Total | Percentage |
| Men | 20 | =C17/C20 |
| Women | 10 | =C18/C21 |
| Children | 10 | =C19/C22 |
| 40 |
To stop Excel changing cell C20 , I have to make my row absolute with the help of dollar sign.
| Total | Percentage | |
| Men | 20 | =C24/C$27 |
| Women | 10 | =C25/C$27 |
| Children | 10 | =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.
| Total | Percentage | |
| Men | 20 | 50% |
| Women | 10 | 25% |
| Children | 10 | 25% |
| 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) | |||||
|---|---|---|---|---|---|---|
| Data | Attendance | |||||
| Student | Day 1 | Day 2 | Day 3 | Day 1 | Day 2 | Day 3 |
| A | yes | no | yes | Present | Present | |
| B | no | yes | no | Present | ||
| C | yes | no | yes | Present | Present |
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) | |||||
|---|---|---|---|---|---|---|
| Data | Attendance | |||||
| Student | Day 1 | Day 2 | Day 3 | Day 1 | Day 2 | Day 3 |
| A | yes | no | yes | =IF(C36=”yes”,$D$33,””) | =IF(D36=”yes”,$D$33,””) | =IF(E36=”yes”,$D$33,””) |
| B | no | yes | no | =IF(C37=”yes”,$D$33,””) | =IF(D37=”yes”,$D$33,””) | =IF(E37=”yes”,$D$33,””) |
| C | yes | no | yes | =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
