EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • 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 Tips Cell References in Excel

Cell References in Excel

Manisha Sharma
Article byManisha Sharma
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated August 22, 2023

cell references in excel

Cell References in Excel (Table of Contents)
  • Introduction to Cell References in Excel
  • How to Apply Cell Reference in Excel?

Introduction to Cell References in Excel

You would have seen the $ sign in Excel formulas and Functions. The $ sign confuses many people but is easy to understand and use. The $ sign serves only one purpose in the Excel formula. It tells Excel whether or not to change the cell reference when the Excel formula is copied or moved to another cell.

ADVERTISEMENT
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests

Start Your Free Excel Course

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

When writing a cell reference for a single cell, we can use any type of cell reference, but when we want to copy the cell to some other cells, it becomes important to use the correct cell references.

What is Cell Reference?

A cell reference is the cell address used in the Excel formula. In Excel, there are two types of cell references. One is an Absolute reference, and the other is a Relative reference.

What is Relative Cell Reference?

The cell reference without a $ sign will change every time it is copied or moved to another cell, known as a Relative cell reference.

What is an Absolute Cell Reference?

The cell references with a $ sign before the Row or Column coordinates are Absolute references. In Excel, we can refer to one and the same cell in four different ways, for example, A1, $A$1, $A1, and A$1. We will look at each type with examples in this article.

How to Apply Cell Reference in Excel?

Applying Cell References in Excel is very simple and easy. Let’s understand how to reference cells in Excel with some examples.

When a formula with relative cell reference is copied to another cell, the cell references in the formula change based on the position of rows and columns.

You can download this Cell References Excel Template here – Cell References Excel Template

Example #1 – Excel Relative Cell Reference (without $ sign)

Suppose you have sales details for January, as given in the screenshot below.

cell references example 1-1

There is Quantity sold in column C and Rate per KG in Column D. So to arrive at the Total Amount. You will insert the formula in Cell E2 = C2*D2.

cell references example 1-2

After inserting the formula in E2, press the Enter key.

cell references example 1-3

You will need to copy this formula in another row with the same column, E2; it will automatically change the cell reference from A1 to A2 because Excel assumes that you are multiplying the value in column C with the value in Column D.

cell refrence excel 1

Now drag the same formula in cell E2 to E17.

cell references in excel 1

So as you can see, when using the relative cell reference, you can move the formula in a cell to another cell, and the cell reference will change automatically.

Example #2 – Excel Relative Cell Reference (Without $ Sign)

The absolute cell reference is a cell address with a $ sign in a row or column coordinates. The $ sign locks the cell so that when you copy the formula to another cell, the cell reference doesn’t change. So using $ in cell reference allows you to copy the formula without changing cell reference.

Suppose in the above example, the Rate per KG is given only in one cell, as shown in the below screenshot. Thus, the Rate per KG is given only in one cell instead of providing in each line.

cell references example 1-4

So when we insert the formula in cell D2, we need to ensure that we lock cell H2, which is the Rate Per KG for Apple. So the formula to enter in cell D2 =$G$2*C2.

cell references example 2-1

After applying the above formula, the output is shown below.

cell excel 2

When you copy the formula to the next row, say cell D3. The cell reference for G2 will not change as we locked the cell reference with a $ sign. However, the cell reference for C2 will change to C3 as we have not locked the cell reference for Column C.

cell refrence

So now you can copy the formula to the below rows till the end of the data.

cell references in excel 2

As you can see, when you lock the cell in cell reference in a formula, no matter where you copy or move the formula in Excel, the cell reference remains the same. In the above formula, we saw the case where we locked an entire cell, H2. Now there can be two more scenarios where we can use absolute reference better.

  1. Lock the row – Refer to Example 3 below
  2. Lock the column – Refer to Example 4 below

As we already know in the cell reference, the columns are represented by words, and numbers represent rows. In the absolute cell reference, we have the option to either lock the row or column.

Example #3 – Copying the Formula

We will take a similar example of Example 2.

row reference 1

After applying the above formula, the output is shown below.

cell excel 1

In this case, we are only locking row 2, so when you copy the formula to the below row, the row reference and column reference will not change.

row reference 2

But when you copy the formula to the right, the column reference of H will change to I keeping row 2 locked.

row reference 3

After applying the above formula, the output is shown below.

row reference 4

Example #4 – Locking the Column

We will take a similar example of Example 2, but now we have the rate per KG for an apple in each line of Column G.

row reference 5

After applying the above formula, the output is shown below.

row reference 6

In this case, we are only locking column H, so when you copy the formula to the below row, the row reference will change, but the column reference will not.

row reference 7

But when you copy the formula to the right, the column reference of H will not change, and the row reference of 2 will also not change, but the reference of C2 will change to D2 because it is not locked at all.

row reference 8

After applying the above formula, the output is shown below.

row reference 9

Things to Remember About Cell Reference in Excel

  • The key that helps insert a $ sign in the formula is F4. When you press F4 once, it locks the entire cell; when you press twice, it locks the row only. And when you press F4 thrice, it locks the column only.
  • One more reference style in Excel refers to a cell as R1C1, where numbers identify both rows and columns.
  • Don’t use too many row/column references in the Excel worksheet, as it may slow down your computer.
  • Depending on the situation, we can also use a mix of Absolute and Relative cell references in one formula.

Recommended Articles

This is a guide to Cell Reference in Excel. Here we discuss how to use Cell Reference in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. Count Cells with Text in Excel
  2. 3D Cell Reference in Excel
  3. Excel Cell Reference
  4. New Line in Excel Cell
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
ADVERTISEMENT
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
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
  • Blog as Guest
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

© 2023 - 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

EDUCBA

Download Cell References Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

EDUCBA

Download Cell References Excel Template

EDUCBA

डाउनलोड Cell References Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW