EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tips Excel Cell Reference
Secondary Sidebar
Excel Functions
  • Excel Tips
    • Recover Document in Excel
    • Excel Shortcut to Select Row
    • Excel Header Row
    • Excel Insert Button
    • Excel Translate
    • Budget in Excel
    • KPI Dashboard in Excel
    • Blank Invoice Excel Template
    • Write Formula in Excel
    • Auditing Tools in Excel
    • Autofit Row Height in Excel
    • Formatting Text in Excel
    • Add Rows in Excel Shortcut
    • Divide Cell in Excel
    • Compare Two Columns in Excel for Matches
    • Excel Troubleshooting
    • Unprotect Excel Workbook
    • Subtract Date in Excel
    • Carriage Return in Excel
    • Negative Numbers in Excel
    • Worksheets in Excel
    • Excel Match Multiple Criteria
    • Exponential Moving Average Formula
    • Sentence Case in Excel
    • Excel Reverse Order
    • Excel Timesheet Template
    • Weighted Average in Excel
    • CTRL Shift-Enter in Excel
    • Excel Expense Tracker
    • INDEX MATCH Function in Excel
    • Percent Change in Excel
    • Drag and Drop in Excel
    • Shade Alternate Rows in Excel
    • Excel Database Template
    • Inverse Matrix in Excel
    • New Line in Excel Cell
    • Format Cells in Excel
    • Excel Text with Formula
    • Project Management Template in Excel
    • Vlookup vs Index Match
    • Embedded in Excel
    • Count Colored Cells in Excel
    • Excel Hacks
    • Column Header in Excel
    • Database Function in Excel
    • Excel Spell Check
    • Linear Interpolation in Excel
    • Convert Excel to CSV
    • Excel Not Responding
    • Delta Symbol in Excel
    • Excel Mortgage Calculator
    • Excel Show Formula
    • Break Links in Excel
    • VLOOKUP Examples in Excel
    • Alternate Row Color Excel
    • Checklist in Excel
    • Microsoft Office Tips
    • Excel vs Access
    • Excel Change Case
    • Count Characters in Excel
    • Formula Bar in Excel
    • Invoice Template in Excel
    • Find and Replace in Excel
    • PowerPivot in Excel
    • Wildcard in Excel
    • Line Break in Excel
    • NULL in Excel
    • Text Formula in Excel
    • Family Tree in Excel
    • Insert Calendar in Excel
    • Page Setup in Excel
    • Search For Text in Excel
    • Switching Columns in Excel
    • 3D Cell Reference in Excel
    • Roadmap Template in Excel
    • Cell References in Excel
    • Excel Automation
    • Matching Columns in Excel
    • Evaluate Formula in Excel
    • How to Find Mean in Excel
    • How to Add Cells in Excel
    • Project Timeline in Excel
    • TODAY Formula in Excel
    • Poisson Distribution in Excel
    • YEAR Formula in Excel
    • REPLACE Formula in Excel
    • Create Spreadsheet in Excel
    • SEARCH Formula in Excel
    • HYPERLINK Formula in Excel
    • Pivot Table with Multiple Sheets
    • COLUMNS Formula in Excel
    • DAY Formula in Excel
    • RIGHT Formula in Excel
    • INDIRECT Formula in Excel
    • Excel IRR Formula
    • Pivot Table Sort
    • OR Formula in Excel
    • Pivot Table Formula in Excel
    • How to Edit Drop Down List in Excel?
    • WEEKDAY Formula in Excel
    • Chart Wizard in Excel
    • Time Difference in Excel
    • Lookup Table in Excel
    • SUBTOTAL Formula in Excel
    • LOOKUP Formula in Excel
    • Themes in Excel
    • Group Worksheets in Excel
    • Excel ISNUMBER Formula
    • 3D Maps in Excel
    • VLOOKUP Tutorial in Excel
    • Moving Columns in Excel
    • Grouping Columns in Excel
    • Excel Formula of Percentage
    • Excel Leading Zeros
    • Timesheet in Excel
    • Nested IF Formula in Excel
    • SUMIF Formula in Excel
    • Concatenation in Excel
    • Excel Insert Page Break
    • LEN Formula in Excel
    • Insert Comment in Excel
    • Excel Print
    • Fractions in Excel
    • Excel Add a Column
    • Borders in Excel
    • Excel Sum by Color
    • Excel vs Google Sheets
    • Calculate Percentage Increase in Excel
    • Excel Format Phone Numbers
    • Excel Search Box
    • Excel vs CSV
    • Excel vs Numbers
    • Excel Concatenate Date
    • Excel Bullet Points
    • Calculate Compound Interest in Excel
    • Find External Links in Excel
    • Excel Comma Style
    • Excel Remove Leading Spaces
    • Combine First and Last Name in Excel
    • How to Calculate Ratio in Excel
    • SUMPRODUCT Function with Multiple Criteria
    • Create Excel Template
    • Excel Greater than or Equal
    • Excel Keyboard Shortcuts
    • Excel Row vs Excel Column
    • Excel Operators
    • Excel Text Compare
    • Excel Uppercase Function
    • Excel Auto Numbering
    • Watermark in Excel
    • Excel Delete Row Shortcut
    • Excel Word Count
    • Row Count in Excel
    • Excel Grade Formula
    • Excel Percentage Difference
    • Excel Rows and Columns
    • Excel Group
    • Excel Move Columns
    • Excel Row Height
    • Excel Sparklines
    • Excel Drawing a line
    • Multiple IFS in Excel
    • Excel Strikethrough
    • Numbering in Excel
    • Share Excel Workbook
    • Excel Shortcut For Merge Cells
    • Excel OneDrive
    • Excel Track changes
    • Excel Hide Formula
    • Excel Filter Shortcuts
    • Excel 3D Reference
    • Protect Excel Workbook
    • Excel Moving Averages
    • Excel Row Limit
    • Excel Absolute Value
    • Excel Print Area
    • Excel Format Painter
    • Excel Separate text
    • Excel Autofit
    • Excel Columns to Rows
    • Excel Paste Shortcut Option
    • Excel Rows to Columns
    • Excel Insert Row Shortcut
    • Excel Extensions
    • Excel Unmerge Cells
    • Divide in Excel Formula
    • Excel Compare Two Columns
    • Not Equal To Excel
    • Excel Remove Duplicates
    • Excel Remove Spaces
    • Excel Calculate Age
    • Copy Excel Sheet
    • Excel Conditional Formatting Based on Another Cell Value
    • Excel Cell Reference
    • Excel Split Cell
    • Excel Circular Reference
    • Highlight Every Other Row in Excel
    • Errors in Excel
    • Excel Delete Pivot Table
    • Excel Pivot Table
    • Subtraction in Excel
    • Count Unique Values in Excel
    • Excel Highlight Duplicates
    • Excel Data Formatting
    • Random Numbers in Excel
    • Basic Excel Formulas
    • Page Numbers in Excel
    • Excel CAGR Formula
    • Excel Combine cells
    • IFERROR with VLOOKUP in Excel
    • Column Sort in Excel
    • Print Comments in Excel
    • Cheat Sheet of Excel Formulas
    • Remove Hyperlinks in Excel
    • Excel Drop Down List
    • Remove (Delete) Blank Rows in Excel
    • Relative Reference in Excel
    • Subscript in Excel
    • Superscript in Excel
    • Excel Insert Multiple Rows
    • Excel Absolute Reference
    • Unhide Columns in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL COURSE
  • Online Excel Data Analysis Course
  • Excel for Finance Course

Excel Cell Reference

By Manisha SharmaManisha Sharma

Cell Reference in Excel

Excel Cell Reference (Table of Contents)

  • Cell Reference in Excel
  • Types of Cell Reference in Excel
    • #1 – Relative Cell Reference in Excel
    • #2- Absolute Cell Reference in Excel
    • #3- Mixed Cell Reference in Excel

Cell Reference in Excel

Cell Reference in excel is the way to represent the identity and the location of any cell with the help of combining Column Name and Row Number on a worksheet. For example, if we say cell B10, then it expands as Column B and 10th Row. Similarly, we can define or declare cell references to any position in the worksheet. We can also activate R1C1 from Excel Options, another way for cell reference, where R1 is Row1 and C1 is Column1.

Start Your Free Excel Course

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

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,368 ratings)

Types of Cell Reference in Excel

We have three different types of Cell References in Excel –

  1. Relative Cell Reference in Excel
  2. Absolute Cell Reference in Excel
  3. Mixed Cell Reference in Excel

Using the correct type of Cell Reference in a particular scenario will save a lot of time and effort and make the work much easier.

#1 – Relative Cell Reference in Excel

Relative cell references in excel refer to a cell or a range of cells in excel. Every time a value is entered into a formula, such as SUMIFS, it is possible to input into Excel a “cell reference” as a substitute for a hard-coded number. A cell reference may come in the form B2, where B corresponds to the cell column letter in question and 2 represents the row number. Whenever Excel comes across a cell reference, it visits the particular cell, extracts out its value, and uses that value in whichever formula that you’re writing. When this cell reference in excel is duplicated to a different location, the relative cell references in excel correspondingly also change automatically.

When we refer to cells like this, we can achieve it with any of the two cell reference types in excel: absolute and relative. The demarcation between these two distinct reference types is the different inherent behavior when you drag or copy and paste them to different cells. Relative Cell references can alter themselves and adjust as you copy and paste them; absolute references contrarily do not. Therefore, in order to successfully achieve results in Excel, it is critical to be able to use relative and absolute cell references in the right way.

How to effectively use Relative cell reference in Excel?

To comprehensively understand the versatility and usability of this amazing feature of Excel, we will need to look at a few practical examples to grasp its true value.

You can download this Cell Reference Excel Template here – Cell Reference Excel Template
Example #1

Let us consider a simple example to explain the mechanics of Relative Cell Reference in Excel. If we wish to have the sum of two numbers in two different cells – A1 and A2, and have the result in a third cell A3.

So we apply the formula =A1+A2

Relative Reference Example 1-1

Which would yield the result as 100 in A3.

Relative Reference Example 1-2

Now suppose, we have a similar scenario in the next column (B). Cell B1 and B2 have two numbers, and we wish to have the sum in B3.

We can achieve this in two different ways:

Here we physically write the formula to add the two cells B1 and B2 in B3.

Relative Reference Example 1-3

The result is 30.

Relative Reference Example 1-4

Or we could simply copy the formula from cell A3 and paste it into cell B3 (it would work if we drag the formula from A3 to B3 also).

Relative Reference Example 1-5

So, when we copy the contents of cell A3 and paste in B3 or drag the contents of cell A3 and paste in B3, the formula gets copied, not the result. We could achieve the same result by right-clicking on cell A3 and use the Copy option.

Relative Reference Example 1-6

And after that, we move to the next cell, B3, and right-click and select “Formulas (f)”.

Relative Reference Example 1-7

What this means is that cell A3=A1+A2. When we copy A3 and move one cell to the right and paste it onto cell B3, the formula automatically adapts itself and changes to become B3=B1+B2. It applies the summation formula for B1 and B2 cells instead.

Relative Reference Example 1-8

Example #2

Now, let’s look at yet another practical scenario that would make the concept quite clear. Let us assume that we have a data set consisting of the Unit Price of a product and the quantity sold for each of them. Now our objective is to calculate the Sale Price, which the following formula can describe:

Sale Price = Unit Price x Units Sold.

Relative Reference Example 2-1

To be able to find the Sale Price, we need to now multiply Unit Price with Units Sold for each product. So, we shall now proceed to apply this formula for the first cell in Sale Price, i.e. for Product 1.

Relative Reference Example 2-2

When we apply the formula, we get the following result for Product 1:

Relative Reference Example 2-3

It successfully multiplied the Unit Cost by the Units Sold for Product 1, i.e. cell G2 * cell H2, i.e. 1826.00 * 20, which gives us the result 36520.00.

So now we see that we have 9 other products to go. In real case scenarios, this could go up to hundreds or thousands of rows. It becomes difficult and nearly impossible to simply go about writing the formula for each row.

Hence, we will use the Relative Reference feature of Excel and simply copy the contents of cell I2 and paste in all of the remaining cells in the table for the column Sale Price or simply drag the formula from cell I2 to the rest of the rows in that column and get the results for the whole table in less than 5 seconds.

Here we press Ctrl + D. So the output will look like below:

Relative Reference Example 2-4

#2 – Absolute Cell Reference in Excel

Most of our daily work in Excel involves handling formulae. Therefore, having a working knowledge of Relative, Absolute, or Mixed cell References in excel becomes quite important.

Let us see the following:

=A1 is a relative reference, where both the row and column change when we copy the formula cell.

 Excel Absolute Reference Example 1-1

=$A$1 is an absolute cell reference; both the column and row are locked and do not change when we copy the formula cell. Thus, the cell value remains constant.

Excel Absolute Reference Example 1-2

In =$A1, the column is locked, and the row can keep changing for that specific column.

Excel Absolute Reference Example 1-3

In =A$1, the row is locked, and the column can keep changing for that specific row.

Excel Absolute Reference Example 1-4

Unlike Relative Reference, which can change as it moves to different cells, the absolute reference doesn’t change. The only thing required here is to lock the specific cell completely.

Using a dollar sign in the formula, w.r.t. a cell reference makes it an absolute cell reference as the dollar sign locks the cell. We can lock either the row or the column using the dollar sign. If the “$” is before an alphabet, then it locks a column, and if the “$” is before a number, then a row is locked.

#3- Mixed Cell Reference in Excel

How to effectively use Absolute cell reference in Excel also how to use Mixed cell Reference in excel?

To get a comprehensive understanding of Absolute and Mixed cell Reference in excel, let us look at the following example.

Absolute and Mixed Reference Example 1-1

We have the sales data for 4 sales managers across different months, where sales have occurred multiple times in a month.

cell reference in excel Example 1-2

Our objective is to calculate the consolidated sales summary of all 4 sales managers. We shall apply the SUMIFS formula to get the desired result.

cell reference in excel Example 1-3

The result will be as:

cell reference in excel Example 1-4

Let us observe the formula to see what happened.

  • In the “sum_range”, we have $C$2:$C$17. There is a dollar sign in front of both the alphabet and the numbers. Thus, both the rows and columns for the cell range are locked. This is an absolute cell reference.
  • Next, we have “criteria_range1”. Here too, we have absolute cell reference.
  • After this, we have “criteria1” – $F2. Here we see that only the column will be locked while copying the formula cell, meaning only the row will change when we copy the formula to a different cell (moving down). This is a mixed cell reference.
  • Next, we have “criteria_range2”, which is also an absolute cell reference.
  • The final segment of the formula is “criteria2” – G$1. Here we observe that the dollar sign is present in front of the number and not the alphabet. Thus, only the row is locked when we copy the formula cell. The column can change when we copy the formula cell to a different cell (moving right). This is a mixed cell reference.

Dragging the formula across the summary table by pressing the Ctrl+D key first and later Ctrl+R. We get the following result:

Result of 3

Mixed cell reference refers to a particular row or column only, such as =$A2 or =A$2. If we want to create a mixed cell reference, we can press the F4 key on the formula two to three times, as per your requirement, i.e. to refer to a row or a column. Pressing F4 again will cause the cell reference to change to relative reference.

Things to Remember

  • While copying the Excel formula, relative referencing is generally what is desired. This is the reason why this is the default behavior of Excel. But sometimes, the objective might be to apply absolute reference rather than relative Cell reference in excel. Absolute Reference is making a cell reference fixed to an absolute cell address, due to which, when the formula is copied, it remains unaltered.
  • Absolutely no dollar signs are required with Relative referencing. When we copy the formula from one place to others, the formula will adapt accordingly. So, if we type =B1+B2 into cell B3 and then drag or copy-paste the same formula into the cell C3, the Relative Cell reference would automatically adjust the formula to =C1+C2.
  • With Relative referencing, the referred cells automatically adjust themselves in the formula as per your movement, either to the right, left, upward or downwards.
  • With Relative referencing, if we were to give a reference to cell D10 and then shift one cell downwards, it would change to D11; if instead, we shift one cell upwards, it would change to D9. If we shift one cell to the right, the reference will change to E10, and instead, if we shift one cell to the left, the reference would automatically adjust itself to C10.
  • Pressing F4 once will change relative Cell reference to absolute Cell reference in excel.
  • Pressing F4 twice will change the cell reference to a mixed reference where the row is locked.
  • Pressing F4 thrice will change the cell reference to a mixed reference where the column is locked.
  • Pressing F4 for the fourth time will change the cell reference back to the relative reference in excel.

Recommended Articles

This has been a guide to Cell Reference in Excel. Here we discuss three types of cell reference in excel, i.e. absolute, relative, and mixed cell reference, and how to use each of them along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Relative Reference in Excel
  2. New Line in Excel Cell
  3. SUM Cells in Excel
  4. Divide Cell in Excel
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Finance Training (18 Courses, 7+ Projects)4.8
3 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

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

EDUCBA Login

Forgot Password?

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

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

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

EDUCBA

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

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

Let’s Get Started

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

EDUCBA

Download Cell Reference Excel Template

EDUCBA

Download Cell Reference Excel Template

EDUCBA

डाउनलोड Cell Reference 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