Dollar ($) in Excel - Why and How to Use $(formula). (2023)

Using $ (dollar) in Excel

$ The dollar symbol/strong in Excel is used in a cell address/reference to lock the reference to that specific onerow or columnand don't change if we copy the formula to another cell or if we drag the cell with a formula horizontally or vertically

To explain,

Start your free Excel course

Excel functions, formulas, charts, formatting Excel dashboard creation and others

You'll understand this better as we review what "Cell Address/Reference" is and how Excel uses it.

What is cell address/reference?

In an MSExcel spreadsheet, there are rows and columns of cells. Excel references each cell with an address that corresponds to column labels in alphabet and row labels in numbers.

So a mobile address isColumn alphabet + row number.

A cellvanColumn BInRow 4will aaddress/referenceif'B4‘.

The image below shows more cells and their addresses highlighted in them.

Use cell references in Excel

How can you tell Excel to use two different cells in a calculation?
You can use two values from two different cells by referencing their cell addresses.
For example, if you want to add the contents of two cells, A2 and B1, the formula is:
= A2+B1

How does Excel perform drag/copy and paste formulas?

Excel has a handy feature that allows you to autofill cells. You can achieve this by dragging the contents of one cell onto an adjacent cell, both vertically and horizontally.

When a cell is selected, a small filled box will appear in the lower right cornerof the selected cellcalled the "Fill Handle". The image below illustrates it-

When selecting a range of cells, this fill handle makes it convenient to drag exactly to the cell you need, as shown below.

Now that we know the basics of dragging, let's see how Excel handles the formula when we drag it.
Suppose there are two columns of numbers and you want the sum of two adjacent cells in the corresponding cell of the third column.

This formula works as shown below.

What is Relative Cell Reference?

Instead of typing out the formula for each cell, Excel lets you drag and drop the same formula as far as calculations are needed. Excel will automatically open thecell referencesin 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 we drag the formula out of cell C2.

In the image above, Excel has changed the dragged formula from "=A2 + B2" to "=A3 + B3". It's 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 calledRelative cell references!
It can happen while we drag the formula across cells or if we also copy the formula from one cell to another.

To change cell reference while copying formulas from one cell to another:

Let's copy the same formula,"=A2+B2.”from cell C2 as shown below.

We copy it to cell C6 by selecting cell C2 with theKeyboard shortcut Ctrl+C(for Windows) orCommand + C(for Mac). Then we move the cursor to cell C6 and use the keyboard shortcutCtrl+V(for Windows) or Command + V (for Mac).

What is an absolute cell reference in Excel?

What if you don't want Excel to changecell referenceswhen the formula is dragged or copied to another cell? It is possible when you lock the cell reference by using $ in Excel. Excel refers to a locked cell as aAbsolute cell referencebecause we can't change it. It's 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 $ for the column alphabet and row number to lock them in.
Therefore we can conclude this:

Using an absolute cell reference in a formula

We have used an absolute cell reference using $ symbols in the excel formula as shown below.

We need to 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 another cell.

Let's 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 pointing to cell B2 with a value of 1 in it. As you'll see below, this will never change in the formula when dragged or copied.

What is a mixed cell reference in Excel?

Amixed cell referencecombines onerelative and absolute cell reference. It gives us the choice to lock the column part of the address or the row part.

For example,
$A1 – Locks a cell reference, preventing Excel from varying the column reference in a formula.
A $1 - Locks Excel from varying the row reference.

Using a mixed cell reference in a formula

Let's see what happens when a mixed cell reference is dragged into an adjacent cell.

Examples

Let's see an example for each cell reference type.

You can download this $ in excel template here -$ in excel template

Example #1 Relative cell reference

In this example, we use a relative cell reference in a simple formula to calculate from a list of items. Attached here is an Excel worksheet with the list below.

The goal is to add up the scores of three subjects - Math, Science and English - for each student to calculate their final score.

Step 1:
Ideally, starting with 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:
Since it's an endless list, dragging the formula above to the end of the list is more accessible than copying and pasting. To drag as before, click cell E2, find the fill handle and drag it down to E16, as shown below.

Step 3:
Move the cursor to a calculated cell in column E to see how Excel changed the cell reference based on its location. The following shows how the cell reference is relatively adjusted in cell E6. You can see how it has changed to:
= B6+C6+D6

So this is called relative cell reference.

Example #2 Absolute cell reference

We have the price list of a supermarket. The supermarket has increased the 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 beOld price + (Old price x % price increase).
% Price Increase is listed in cell F2. % price increase applies 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

Note that $F$2 is an absolute cell reference, because in this case the percentage increase in price is a constant and should not change as it is dragged or copied.

Step 2:
The new price for a dozen Eggs is now calculated in cell C2. As you did in the previous example, select cell C2, grab the fill handle and drag it 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 dragged it to other cells.

It's not! All other relative cell references are adjusted to the new location, while the absolute cell reference remains unchanged.

Example #3 Mixed cell reference

In this example we are creating a multiplication matrix. The intersection of a number in the column header and the row number must have its product. The intersection at 5 in the column and 4 in the row, i.e. cell F6 must contain the product of 5 and 4, which is 20.

It's a comprehensive example that thoroughly explains mixed cell references and copy and pastes the formula.

Step 1:
For this to work, the formula must 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.

Moving down the same column, the column should stay the same, but the row should stay the same, and the formula should be set for the first cell B3 as:
=$A3*B$2

Now what remains is to drag/copy this formula across the entire array and check if the formula adapts as expected. But first, let's visualize how this formula is expected to change in the neighboring cells to better understand mixed cell references.

The image below explains how the formula is expected to adapt.

Each change in the cell changes the column or row reference in the formula accordingly. This is how we can use mixed cell references.

Step 2:
All that's left is to drag or copy and paste the formula as shown.

Pro-tip

It's badconvenient to use shortcutsswitch 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 alternates 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. First, it can be useful in denoting the US currency – the $ (dollar). The $ (dollar) symbol can lock cell references in Excel formulas and functions. It helps us simplify the process of using the same cell range and formula in different 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 remain constant when we copy or drag the formula. On the other hand, when the $ sign comes before the row number, the row value remains constant when we use the formula again in another cell.

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 another 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– $, Partially locked Example:$A2of€ 2	The unlocked part of the cell reference, Column or Row, will change.