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

**which changes when copied to another cell. Now I will explain you this with an example.**

*relative reference*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