$ symbol in Excel locks a specific cell or rows or columns in a worksheet. The shortcut to lock down a reference in Excel is pressing ALT + F4. This feature is used while working on formulas when we do not want the reference to be changed when copying or dragging the formula to other cell references. This feature will keep the reference the same for all the further calculations.
For example, when we use a $ sign before the cell reference, you inform Excel to keep referring to the cell even while copying and pasting the formula.
Table of contents
$ Symbol in Excel Formula
One of the common questions among all the Excel beginners is, “What does the dollar ($) symbol do inside the formula?” It is the most trending question in Excel forums. I was not different from you either; I had the same doubt. This article will explain the importance of the “dollar ($)” symbol in the basic Excel formulaBasic Excel FormulaThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions.read more.
You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: $ Symbol in Excel (wallstreetmojo.com)
Cell References in Excel
So, now the C1 cell is dependent on cell A1. So, whatever happens in cell A1 will directly impact cell C1. Now, if I copy and paste cell C1 to C2, as a new learner, we think we will get the value of 100 only, but that is not the case here.
Before explaining the $ symbol, let me brief you about cell references in Excel.
- For example, we have a value of 100, 200, and 300 in the first three cells.
- Now, in cell C1, we will link to cell A1.
- So, now the C1 cell is dependent on cell A1. So, whatever happens in cell A1 will directly impact cell C1. Now, if we copy and paste cell C1 to C2, as a new learner, we think we will get the value of 100 only, but that is not the case here.
Look, instead of 100, we got 200.
If you look at the formula bar, it says A2. The reason why we got A2 was that when we copied cell C1, it was not valued. Instead, it was a formula. Since cell C1 had a reference to cell A1 and we moved down the cell by one, it changed from cell A1 to A2. So we got the value of cell A2.
Similarly, the reference changes accordingly when we copy the cell reference based on how many cells we move down and how many cells we carry, either left or right.
It is a brief understanding of cell references. Now, we will see what the $ symbol does with Excel formulas.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
–>> If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Use $ Symbol in Excel formula? (With Examples)
Let’s see some simple examples of the $ symbol in the Excel formula.
Example #1
Look at the below data set.
We have the tax rate in cell A2 as 5% in the above data. In column B, we have the cost price. We need to multiply the cost amount by the tax percentage to arrive at the tax amount. Apply the formula in cell C2 as B2 * A2 to get the tax amount.
Since we have given a reference to cells, we will copy and paste the formula down to the below cell.
This time we got 0 as a result. Now, press F2 on the cell to debug the formula.
Now, the tax percentage cell has been changed from A2 to A3. So, change the tax amount reference from A3 to A2 again.
If we copy and paste this cell again, we will get the tax percentage cell to change from A2 to A3. It will be the case with the remaining cells as well.
So, how do we deal with it?
Can we do this for all the cells?
What if there are a thousand cells??
These are the questions everyone will get who does not know about the $ symbol in the Excel formula.
The ($) dollar symbol in the Excel formula plays a vital role here. In a cell, C2 opens the formula.
Now, give the cell reference to the A2 cell.
After giving the cell reference to cell A2, press the F4 key, and see the magic.
Wow!!! After all these days, you got to know the technique of inserting a dollar ($) symbol in the Excel formula for cell reference. We are sure you must have tried entering the $ symbol manually!!!
Press the “Enter” key to have a value of the tax amount.
Copy and paste the formula to the below cell and see the magic.
Wow!!! It takes the same cell reference even though we copy and paste the above cell to the below cell.
It takes the same cell reference because since we have pressed the F4 key, it has inserted a ($) dollar symbol in Excel, making the cell an absolute reference cellAbsolute Reference CellAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing.read more.
It does not matter where you are pasting in the worksheet. It will still take the reference of cell A2 only.
Copy and paste to all the other cells to get an exact tax amount.
We have seen how to make the absolute cell reference by pressing the F4 key, which inserted a ($) dollar symbol in Excel. Once the absolute reference sets, it will still take the same cell reference.
We can use two absolute references, i.e., absolute row reference and absolute column reference.
Example #2
For example, look at the below data.
We have unit data in column A. In row 2, we have price data. Therefore, we need to arrive at the sales amount in the empty boxes by multiplying units * price.
Let us apply the formula.
I have applied the formula B2 to A3 to arrive at the first sales price. Now, I will copy and paste the formula to the below cell.
Since we have not locked the cell using the F4 key, price cell B2 moved down by one cell and took the reference of the B3 cell.
While applying the formula, we need to decide on two things.
First, we must decide that moving to the left column reference of units should be absolute, but the row reference should change while moving down.
The second thing is that the price reference should be absolute while moving from top to bottom row, but the reference should change while moving to the right column.
To make these arrangements, let’s open the formula in the B3 cell and select the B2 cell, i.e., price.
Now, press the F4 key to make it an absolute reference.
It is an absolute reference now, but the price reference should be absolute while moving from the top to bottom for the price row. But while moving to the right column, the reference should change. To make the row cell reference absolute, press the F4 key.
As you can see from two, it has changed to one dollar, i.e., in front of the row number.
Now, put a multiply sign and select A3 cell.
We need to make the column cell reference absolute with this column, so press the F4 key three times to make the ($) dollar symbol come in front of a column header in Excel.
Press the “Enter” key to have a result. Then, copy and paste the formula to other cells to have sales value.
Using a ($) dollar symbol in an Excel formula, we can create absolute cell reference, row reference, and column reference.
Recommended Articles
This article is a guide to $ in Excel. We discuss examples of the $ symbol in Excel formula using row and column references with a downloadable Excel template. You may also look at these useful functions in Excel: –
