Relative Referencing in Excel – Shiksha Online

Relative Referencing in Excel – Shiksha Online

5 mins read1.2K Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Mar 3, 2023 13:34 IST

The article covers the Relative Referencing in Excel, how to create a relative reference, and examples of relative referencing.

2022_06_Relative-Referencing-in-Excel-1.jpg

Relative references refer to a range of cells or an individual cell in Excel. Every time we input a value into a formula, such as COUNTIF or SUMIF, it is possible to input a “cell reference” into Excel as a substitute for a hard-coded number. A cell reference may come in A2, where A refers to the column, and 2 refers to the row number. To understand this further, read this blog on Relative Referencing in Excel.

Content

How to Create a Relative Reference in Excel

Whenever Excel deals with a cell reference, it visits the cell in question. It further extracts the cell value and uses that specific value in the formula created. If you duplicate this cell reference to another location, the cell’s relative references also automatically change.

When we reference cells in this way, we get two types of references, relative and absolute. The difference between these two references is that they demonstrate different inherent behavior when dragging, copying, and pasting in nearby cells. 

Relative references can alter and adjust when copy-pasted, while absolute references do not. To successfully achieve results in Excel, you must use relative and absolute references accurately. 

Read MS Excel Tutorials

Purpose – Relative reference gives flexibility to the formulas. If you change the value of one of the cells, the value that the reference takes will automatically be adjusted. In addition, being automatic, errors are avoided, and time is saved.

Relative reference

We can use relative references in the following way, for example, by making a formula containing the relative reference A1 multiplied by a value. In this case, we will multiply it by three, and it would be as follows =A1*3

Where A1 is our relative reference containing a value and will be multiplied by 3, our value is chosen as an example.

Relative reference 2

The benefit of relative references in Excel is that they get automatically modified when copying them to another cell.

The cell where you have introduced reflective reference will change as you copy the formula to another cell. What Excel understands is the relative position of the cells. It is easier to understand with the following examples.

Read What is MS Excel

Example 1 – Relative Referencing

We have a range in which we have the hourly pay and the hours worked to obtain the total payment for each worker. 

Relative Referencing - 1

So, we need to multiply the hourly rate by the hours worked. For that, we write =B2*C2 in cell D2

Relative Referencing - 2

To fill the rest of the data range, we must drag the formula using the lower right corner of cell D2 or by double-clicking on this same corner.

Relative Referencing - 3

As per the illustration, Jonas worked 3 hours and was paid $13 per hour. So his total payment is $39. We see that the formula in D4 is =B4*C4. This is a relative reference: Excel remembers the formula as “multiply the cell to the left by the cell two to the left.”

Most Popular and Powerful Formulas in Excel
Most Popular and Powerful Formulas in Excel
Excel has various functions ready to process your information to get the desired results. Familiarize yourself with each of them. Once you master various functions, you can create advanced MS...read more
Average Function in Excel – Formula and Examples
Average Function in Excel – Formula and Examples
The AVERAGE function is a popular Excel function used for various purposes, such as calculating average sales, average student scores, average monthly expenses, etc. It provides a quick and efficient...read more
What is Conditional Formatting in Excel?
What is Conditional Formatting in Excel?
Conditional Formatting is a popular tool in Excel that allows adding a different format to the cells per their conditions. This way, we can easily see if the value of...read more

Example 2 – Relative Referencing

Consider another example. We use the SUM formula that adds the total cost or value of the products in January in cell B7.

Example 2 - Relative Referencing

Cell B7 is a relative reference that contains a formula summing up the values ​​of B2:B6. 

We will copy or replicate the formula in other fields to get the total. When dragging B6 through F6, we will notice that we will be increasing the columns. This way, the references of these cells will change according to their corresponding ranges.

Example 2 - Relative Referencing 2

When copying the formulas to other cells, Excel will automatically adjust the references; that is, it will modify the columns and rows corresponding to each formula range. In addition, this will be relative to the number of columns we have moved.

Top MS Excel Interview Questions
Top MS Excel Interview Questions
Microsoft Excel is an indispensable tool that empowers professionals to manage, analyze, and visualize data with unparalleled efficiency. Master your Excel interview with confidence using our curated list of top...read more
Introduction to MS Excel – A Beginner’s Guide
Introduction to MS Excel – A Beginner’s Guide
Microsoft Excel has been around for decades and is one of the most desired skills if you are considering a career in data science or analysis. But there are still...read more
How to Create MIS Report in Excel?
How to Create MIS Report in Excel?
A Management Information System (MIS) report in Excel is a vital tool for businesses and organizations, offering valuable insights by consolidating and presenting data in an organized and comprehensible format....read more

Points to Remember

  • In relative referencing, the referred cells automatically set themselves in the formula when moved across any direction.
  • If we give reference to B10 and shift to the next cell, it would change to B11. If we move one cell upward, the relative reference will be B9. When we move to the right, the reference will be C10, and so on.

I hope this article helped you understand Relative Referencing in Excel and how it works. In the following article, we will cover the concept of absolute referencing in Excel


Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel

FAQs

What are absolute and relative references in Excel?

There are two types of references: relative and absolute. Relative references change when they are copied from one cell to another. Absolute references remain constant no matter what cell they are copied to.

What are relative references in Excel?

Relative references refer to the location of the cell. Relative references allow Excel to modify the column and row when copying the formula to other cells. The change will be relative to the number of columns or rows the formula has shifted.

How do I turn on relative references in Excel?

When you write a formula in Microsoft Excel, press the F4 key. This allows switching between relative, absolute, and mixed cell references.

What are absolute and mixed relative references?

Absolute references will be applied when neither the column nor the row is going to change; relative references when only one column or row is going to change, and mixed references when combining relative with absolute.

How to make an absolute reference in Excel without F4?

To work with absolute references, it must be specified by writing the $ sign in front of the column letter and the row number.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments