Use of $ (Dollar) in Excel
$ The Dollar symbol/strong in Excel is used in a cell address/reference to lock the reference to that specific row or Column and not let it change when we copy the Formula to a different cell or if we drag the cell with a formula horizontally or vertically
To explain,
You will understand this better when we walk through what ‘Cell Address/Reference’ is and how Excel uses them.
What is Cell Address/Reference?
In an MS Excel Spreadsheet, Rows and Columns of cells exist. Excel refers to each cell by an address corresponding to the Column Labels in the Alphabet and Row Labels in Numbers.
So, a cell address is Column Alphabet + Row Number.
A cell from Column B and Row 4 will have an address/reference as ‘B4‘.
The image below shows more cells and their addresses marked within.
Use Cell References in Excel
How can you tell Excel to use two different cells in a calculation?
You can use two values of two different cells by referring to their cell addresses.
For example, when you want to add the contents of two cells, A2 and B1, the formula is:
= A2+B1
How Does Excel execute Drag/Copy & Paste of formulas?
Excel has a handy feature that helps you fill in cells automatically. You can achieve this by dragging the contents of a cell to an adjacent cell, either vertically or horizontally.
When a cell is selected, a small filled-in box appears at the lower right corner of the selected cell called the “Fill Handle”. The below picture illustrates it-
While selecting a series of cells, this Fill Handle makes it convenient to drag precisely to whichever cell is necessary, as shown below.
Now that we know the basics of dragging, we’ll see how Excel handles the formula when we drag it.
Let’s say there are two columns with numbers, and you want the sum of two adjacent cells in the third column’s corresponding cell.
This formula works as shown below.
What is Relative Cell Reference?
Instead of typing out the formula for each cell, Excel lets you drag the same formula down as far as calculations are needed. Excel will automatically change the cell references in the existing formula relative to where we drag it. Let’s see how this works.
See how Excel changes the references in the formula when the formula we drag from cell C2.
In the picture above, Excel has changed the dragged formula from “= A2 + B2” to “= A3 + B3”. It is possible because these cell addresses/references are relative, and you can change it relevant to where we move the formula.
So, the cell references that can change are called Relative Cell References!
It can happen while we drag the formula across cells or if we copy the formula from one cell to another too.
Changing cell reference while copying formula from one cell to another:
Let’s copy the same formula, “=A2+B2,” from cell C2 as shown below.
We will copy it to cell C6 by selecting cell C2 using the keyboard shortcut Ctrl + C (for Windows) or Command + C (for Mac). Then we will move the cursor to cell C6 and use the keyboard shortcut Ctrl + V (for Windows) or Command + V (for Mac).
What is an Absolute Cell Reference in Excel?
What if you do not want Excel to change cell references when the formula is dragged or copied to a different cell? It is possible when you Lock the cell reference by using $ in Excel. Excel refers to a locked cell as an Absolute Cell Reference because we cannot change it. It is absolute!
How to Use Excel $ (Dollar) in Formula
An absolute cell reference looks like $A$1.
Excel uses an absolute cell reference in a formula in the following way-
Use $ before the Column Alphabet and the Row Number to lock them in.
That is why we can infer this:
Use of an Absolute Cell Reference in a formula
We have used an absolute cell reference using $ symbols in the Excel formula, as shown below.
We must use an absolute cell reference in a formula where the reference to that cell should never change.
However, an absolute cell reference will never change, be dragged, or copied to a different cell.
Let us see what happens when a formula with an absolute cell reference is dragged and copied.
The formula is given below,
=A2+$B$2
$B$2 is an absolute cell reference referring to cell B2 with a value of 1 in it. As you will see below, this will never change in the formula when dragged or copied.
What is a Mixed Cell Reference in Excel?
A mixed cell reference combines a relative and absolute cell reference. It gives us the choice of either locking the Column part of the address or the Row part.
For example,
$A1 – Locks a cell reference, not allowing Excel to vary the Column reference in a formula.
A$1 – Locks Excel from varying the Row reference.
Use of a Mixed Cell Reference in a Formula
Let’s see what happens when a Mixed Cell Reference gets dragged to an adjacent cell.
In the above example, $A is locked in the cell reference.
Notice when we drag the formula across, $A does not change to B.
The following multimedia image shows when we drag down the same formula, the Row Number in the same formula changes because we have not locked it with a $.
Also, in the image below, notice how we have locked the Row Number with a $, so when we copy the formula to a different row, the Row reference does not change, but the Column reference changes because we have left it unlocked.
To summarize, we can use cell references in three ways:
|
Cell Reference Type |
How does it behave? |
| Relative Cell Reference – No $, No lock
Example: A2 |
Will change when we copy or drag the formula to a different cell |
| Absolute Cell Reference – $, Locked
Example: $A$2 |
It will never change when we copy or drag the formula to other cells |
| Mixed Cell Reference – $, Partly Locked
Example: $A2 or A$2 |
The cell reference’s unlocked part, Column or Row, will change. |
Examples
Let us work through an example for each cell reference type.
Example #1 Relative Cell Reference
In this example, we’ll use a relative cell reference in a simple formula to calculate from a list of items. Attached here is an Excel worksheet that contains the list shown below.
The goal is to add the scores of three subjects – Math, Science, and English- for each student to calculate their final scores.
Step 1:
Ideally, starting from the first student on the list – Ryan, Enter the formula to calculate his total score in the corresponding cell – E2. The formula is shown below.
=B2+C2+D2
Step 2:
Because it is an endless list, dragging the above formula down to the end of the list is more accessible than copying and pasting. To drag as previously, click on cell E2, locate the fill handle, and drag it to E16, as shown below.
Step 3:
Move the cursor to any calculated cell in column E to see how Excel has changed the cell reference according to location. Shown below is how the cell reference has adapted relatively in cell E6. You can see how it has changed to:
= B6+C6+D6
Thus, this is called Relative Cell Reference.
Example #2 Absolute Cell Reference
We have the price list of a grocery store. The grocery store has increased prices of certain items by 10%, and we will calculate the new price using absolute cell reference. The Excel sheet is shown below.
Step 1:
To calculate a new price, the formula should be Old price + (Old price x % increase in price).
% Increase in price is mentioned in cell F2. % Increase in price is common to all items in this example.
So the formula for calculating the new price of the first item, A Dozen eggs, should be:
= B2+B2*$F$2
Notice how $F$2 is an absolute cell reference because, in this case, the % Increase in Price is a constant and should not change if dragged or copied.
Step 2:
The new price for a dozen Eggs will now be calculated in cell C2. As you did in the previous example, select cell C2, grab the fill handle, and drag it down to the end of the list.
Step 3:
Select a cell in the New Price column to see if the absolute cell reference in the formula has changed after we drag it to other cells.
It has not! All other relative cell references have adapted to the new location, while the absolute cell reference remains unchanged.
Example #3 Mixed Cell Reference
In this example, we will create a Multiplication Array. The intersection of a number on the column header and the row number should have the product of them. The intersection at 5 in the column and 4 in the row, i.e., cell F6 should contain the product of 5 and 4, which is 20.
It is a comprehensive example explaining mixed cell references thoroughly and copying and pasting the formula.
Step 1:
For this to work, the formula should combine mixed cell references. When we move along the same row, the formula should contain one component that changes. The column should change as we move to the next cell in the same row.
When we move down the same column, the column should remain the same, but the row needs to, and the formula has to be set up for the first cell B3 as:
=$A3*B$2
What remains now is to drag/copy this formula across the entire array and check if the formula adapts as expected. But, before that, let us visualize how this formula is expected to change in the neighboring cells to understand mixed cell reference better.
The image below will explain how the formula is expected to adapt.
With every change in the cell, the column or row reference in the formula changes accordingly. This is how we can use mixed cell references.
Step 2:
All that is left is to drag the formula or copy and paste it as shown.
Pro-Tip
- It is very convenient to use shortcuts to cycle between Relative, Absolute, and mixed cell references while typing a formula.
- After entering the cell address as a relative reference in a formula, use the shortcut: For Mac – Command+T, For Windows – F4. The address will cycle between Relative, Absolute, and Mixed cell references.
Frequently Asked Questions (FAQs)
Q1. What does “$” (Dollar) mean in Excel?
Answer: We can use the “$” sign in Excel for two purposes. Firstly, it can be useful in denoting the US currency – the $ (dollar). The $ (Dollar) symbol can lock in cell references in Excel formulas and functions. It helps us simplify the process of using the same cell range and formula at various places in the worksheet.
Q2. What are the three types of cell references?
Answer: The three types of cell references available in Excel are: Relative, absolute, and mixed.
Q3. What is the difference between $B3 and B$3 in Excel?
Answer: When the $ symbol comes before the column value “B,” it means that the column value will stay constant when we copy or drag the formula. On the other hand, when the $ sign comes before the row number, the row value will stay constant when we reuse the formula in another cell.
Recommended Articles
The above article is a guide to $ (Dollar) in Excel using different methods and downloadable templates. To learn more about such useful functions of Excel, you can read the following articles.
