Many times you would have come across formulas in Excel where you see a dollar sign ($) attached with the cell references. If you fall in that description, you might think that like the world, money is also making Excel go round. The monetary aspect isn’t why the dollar sign is being used here even though we can’t confirm that Excel is completely nonmaterialistic.
There may be great emphasis on finding the right function for Excel maneuvers, but it’s equally important to use the formula the right way too. In today’s tutorial, we will talk about the right usage of formulas using this small Excel trick, a dollar sign. Examples are B2, $B$2, $B2, and B$2.
Think of the dollar sign as a lock, as it locks the proceeding element; column or row. Since the formulas we use on the worksheets may not just be applied to a single cell, we would want to control the behavior of a formula as it is copied to other cells and hence to other rows and columns.
Learn how to add the dollar sign to a cell reference in a formula using a shortcut key. If this is new to you, find out why it's important to lock a reference and what are the 3 types of references. Now…
Let’s make some bucks! (Sad joke)
What does $ (dollar sign) mean in Excel?
The dollar sign ($) in Excel, when attached to cell references, denotes an absolute reference. This means that the referenced cell is securely locked and remains unchanged regardless of formula movement.
- To lock a column, prefix the column letter with a dollar sign, like $B2.
- Similarly, to lock a row, place the dollar sign before the row number, such as B$2.
- For a combination of both, use the dollar sign before both the column letter and row number, like $B$2.
Look at this super simple formula below:
=C3*D3
It’s a simple formula used to multiply the contents of one cell with another. In itself, the formula is fine as it is and just for one cell you won’t need to lock any cell reference. The need to lock a reference arises when the same formula will be copied elsewhere. When the given formula is dragged or copied down, see what happens:
Since we are copying the formula down along the column, the column reference is constant in the formula and the row numbers have changed. In this way, as the formula is dragged down, the reference will keep changing and each price will be multiplied by the adjacent product discount.
Likewise, when a formula is pasted rightward, the row will be constant and the column reference will change in the formula. This is how cell references behave in formula-pasting.
Now what will you do if you want to multiply each product with just the 20% discount only as a small check? You can change each product discount to 20% but only for running a check on things, that’s taking it too far.
You can lock the cell in the formula by converting the reference to an absolute reference. By doing that, the cell with the discount will stay fixed in the formula while the prices will change row by row to multiply each cell individually with 20% and return the results in the target column.
Locking D3 with dollar signs keeps it constant in other rows.
Instead of entering a value in a cell in the formula (e.g. changing the formula to =C3*20%) refer to the cell containing 20% and lock it so the value of the cell value can be changed as per the need, instead of having to rearrange the data and alter the formula. You can also have this cell created separately outside of the table e.g. $I$2.
Convinced? Read on to learn how to add a dollar sign to the formulas in Excel.
Shortcut to Add Dollar Sign in Formulas
Manual addition is an option too, but the shortcut to add the dollar sign in formulas is pretty… short. The shortcut key to be used to add the $ sign to lock a cell reference is:
F4
The dollar sign is added to the formula by having the blinking cursor in one of three places; before, after, or in between the coordinates of the cell reference you want to lock.
Every time you hit the F4 key to add a locked reference (say we’re at B2), Excel will loop through all the reference options starting with:
- an absolute reference (e.g. $B$2),
- then mixed references, first with a fixed row (e.g. B$2) and then with a fixed column (e.g. $B2),
- and then back to a relative reference (e.g. B2).
To find out what these references are and how they work (or don’t work since we’re locking them?), read the section below.
Types of References in Excel
The cell references you will find in Excel formulas are:
- Relative reference
- Absolute reference
- Mixed reference
This section will tell you the role of each reference so let’s begin.
Relative Reference
A relative reference is a regular type and this is the cell reference that goes into your formulas by default. Think of it as a free reference that is not held down by anything. The reference is relative to the position of the target cell (the cell the formula is in).
Here’s a glimpse of what it looks like:
In the example shot above, the formula shows two relative references; C3 and D3. When this formula is dragged down and the row of the formula changes, you can see that the cell references in the formula accommodate the change:
From C3 and D3, the references are left free to become C4 and D4. If the formula is dragged to the right, the row will be the same in the formula and the column will change. In this type of reference, there is a relative column and a relative row.
Absolute Reference
Absolute is used here as the opposite of relative; i.e. fixed. In an absolute reference, both the column and row of the cell reference are locked. The lock is represented by a dollar sign. An absolute reference can be applied to a single cell or an entire range. The latter will be necessary for referring to tables in formulas.
As an example, we will show you what a range with absolute references looks like and you can apply the same concept to a single cell too. Have a look at the formula below:
In this formula, we will zero in on the second parameter. Let's take one reference from the range in the second parameter; $B$3. This is an absolute reference as it contains an absolute column and an absolute row. Consequently, as the formula is copied down, the reference will not change:
See that? The reference is still $B$3 and has taken no effect from being copied down while the relative reference in the formula has changed from E3 to E4. Do you want to know what would happen if the reference wasn’t locked in place?
In this formula we have referred the range as an absolute reference which is $B$3:$C$12 and selected through the formula, it looks like this in Excel:
This is the range that has been selected for returning the relevant data using the example formula. Had we left the range reference relative, copying down the formula would do this:
As evident from the formula and the selected range, that hasn’t worked out well. The range has shifted from B3:C12 to B4:C13. To avoid this, remember to lock your references with the dollar signs.
Also, see this small example of creating an absolute reference for a single cell:
In this example, every cell in column C is being compared to C3 which is entered as an absolute reference in the formula to keep it constant.
Note: An absolute reference only changes with additions or deletions of cells in the absolutely referred range. E.g. deleting row 10 from the event management example would adjust the range from $B$3:$C$12 to $B$3:$C$11, keeping the originally absolute reference intact in essence. This applies to the addition and deletion of columns as well.
Bonus tip: Excel also allows you to refer to and even lock an entire column or row. See these sample references to refer to the whole column B or whole row 2:
- Relative column: B:B
- Absolute column: $B:$B
- Relative row: 2:2
- Absolute row: $2:$2
Mixed Reference
Perhaps for the lack of a better term, this one’s called a mixed reference that includes both aspects, relative and absolute. There are two types of mixed reference; one where the column is constant and one where the row is constant. Let’s see how they can be applied.
Constant Column
A reference with a constant column means that the row is relative. Such a reference will look like this: $E4 where column E is locked with a dollar sign and row 4 is relative. A mixed reference with a constant column will be most helpful when you are comparing 2 or more columns and want to paste a formula rightward. See our example to understand this:
The second parameter with $E4 is the mixed reference. Since we would want to apply the rest of the percentages in column E in the formula, we will lock the column and leave the row free so that the row can change from 4 to 5 and 6.
The relative row solves the problem of copying the formula vertically. As for copying the formula horizontally, this is where the absolute column comes into play. When the formula is dragged to the right, E4 stays as it is:
And when copied down for Class B, the relative row comes in handy again.
Constant Row
A mixed reference can also have the reverse; a relative column and a constant row. This will be required when you want to fix the row in a formula to apply the formula to 2 or more rows and also relatively change the column. Quite like the example ahead:
In the second argument of the formula, we have entered a mixed reference with an absolute row. Since we want to fix row 13 as the formula is copied down, said row is locked with a dollar sign and the column is kept free to copy it across from column C to D.
In the next row of the formula, row 13 stays constant:
With the formula copied across, the free column changes relative to its location:
That was relatively easy, wasn't it? One thing is for sure, with the constantly changing world, there will absolutely be new things to learn. And on the Excel side of things, we can promise to broaden your horizons bit by bit. Why don’t we go take up on another Excel trick? Whoosh!
About Mehwish Javaid