EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Formula and Functions $ (Dollar) in Excel
 

$ (Dollar) in Excel

Updated July 3, 2023

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,

 

 

Use of $ in Excel

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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‘.

What is Cell Address Reference-1

The image below shows more cells and their addresses marked within.

What is Cell Address Reference-2

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

Use Cell References in Excel

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-

Fill Handle -to Drag

While selecting a series of cells, this Fill Handle makes it convenient to drag precisely to whichever cell is necessary, as shown below.

Gif 1 - Fill Handle

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.

How does Excel execute Drag 1

This formula works as shown below.

How does Excel execute Drag-2

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.

Gif 2 - Formula Drag

See how Excel changes the references in the formula when the formula we drag from cell C2.

What is Relative Cell Reference

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.

Changing cell reference while copying formula

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).

Changing cell reference while copying formula-2

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-

How to use Excel $ in formula

Use $ before the Column Alphabet and the Row Number to lock them in.
That is why we can infer this:

How to use Excel $ in formula-2

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.

Use of an Absolute Cell Reference in a formula

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.
Use of an Absolute Cell Reference in a formula-2

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.

Use of an Absolute Cell Reference in a formula-3

Using $ symbol in MS Excel-Use of an Absolute Cell Reference in a formula-4

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.

Gif 3 - Mixed ref drag 1

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 $.

Using $ symbol in MS Excel-Mixed ref drag 2

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.

Using $ symbol in MS Excel-Mixed ref copy

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.

You can download this $ in Excel Template here – $ in Excel Template

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.

Using $ symbol in MS Excel-Eg1

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

Using $ symbol in MS Excel-Eg1 step 1

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.

Using $ symbol in MS Excel-Eg1 Step 3

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.

Eg2

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

Using $ symbol in MS Excel-Eg2 step 1

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.

Eg2 step 3

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.

Eg3

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

Eg3 step 1

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.

Using $ symbol in MS Excel-Eg3 step 1-2

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.

Mixed Cell Reference eg

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.

  • VLOOKUP Function in EXCEL
  • Excel Match Function
  • Multiple IFS in Excel
  • IF AND Function in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download $ in Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download $ in Excel Template

EDUCBA

डाउनलोड $ in Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW