You can often see people use the $ sign in spreadsheet formulas. I also use dollar symbols in formula no matter whether it’s Google Spreadsheets or Excel. Actually it helps us to reduce the formula entering time by effectively applying copy and paste.
In Excel and Google spreadsheet, the use of dollar symbols are same. We can learn how to use dollar symbols in formula also the different placement and its affect in result.
There are three different placement of dollar symbols in formula. I will explain you all the three with the help of examples and images.
Use of Dollar Symbols in Formula in Spreadsheets
Dollar symbols can be used to lock a particular cell in formula. The particular cell reference won’t change while we copy and paste.
I already mentioned there are three different placement of $ symbols which many of you are not aware. You can use single or double dollar symbols in a spreadsheet formula. There are two types of placement of single dollar symbols in a formula and one type of double dollar symbols.
1. Right Side Placement of Single Dollar Symbol in Formula
Use this placing when you want to keep the row fixed while copy and paste formula to down.
2. Left Side Placement of Single Dollar Symbol
Use this placing when you want to keep the column heading fixed while copy and paste formula to right.
The above two placing you can memorise quickly. If the symbol is left side of column heading, the column heading won’t change. On the other hand, if the symbol is left side of row number, the row number won’t change in formula.
3. Double Dollar Symbols in formula
It’s the common placement of dollar symbols in formula. You can use F4 shortcut key to insert dollar symbols in this case. See the screenshot above. This type of placement just retain the formula wherever you copy. No changes will be applied to the cell reference.