Excel Relative Referencing
Microsoft Excel, abbreviated as Excel, is a widely used spreadsheet software that helps users record vast amounts of data types in cells across multiple worksheets in a workbook. It enables the users to perform various simple to complex calculations on the recorded data within the cells and get the results concluded using the inbuilt functions or formulas.
In particular, most Excel formulas can be used with the desired values as direct arguments. However, we often supply corresponding cell references in Excel formulas that hold effective values needed to be used in calculations or financial analysis. The main advantage of using cell references instead of direct values in a formula is that we get real-time results or outputs by applied formulas when we change the values in the corresponding cells accordingly. There are mainly three types of cell references in Excel: relative, absolute, and mixed references.
In this tutorial, we discuss a brief introduction to Excel Relative Referencing. The tutorial also discusses step-by-step procedures for creating or using relative references within the Excel worksheets with the help of relevant examples. Before we look at Relative References in Excel, let us first understand Cell Reference in Excel with its definition.
What is a Cell Reference?
In Excel, a cell reference is defined as the name or address of a specific cell or range. It accordingly remains unique for each respective Excel cell. An Excel cell reference is formed by combining a particular column name and the corresponding row number. For example, A1 is the cell reference to the first cell in the sheet where the letter 'A' represents the first column and the numeric '1' represents the first row of the sheet.
The primary purpose of a cell reference in Excel is to tell an Excel formula where to look for the desired value/data to be used in the formula to produce the corresponding output. In Excel, we can use cell references to refer to the same sheet, another sheet, another workbook, and many other similar programs.
What is the Relative Reference in Excel?
Relative reference is the default approach used by Excel. It makes the Excel cells reference-free, giving the fill function freedom to continue its order without any restriction. In particular, a relative reference in Excel refers to a pointer to any specific cell or range of multiple cells within the same sheet, another sheet, or workbook. For example, the relative reference for the cell A1 can be given as below:
As shown above, the relative reference in Excel is nothing but only the combination of a respective column name and row number. If we copy-paste the relative references in different cells, their references automatically change depending on the relative position of rows and columns. Relative references typically change when copied to other locations because they describe the "offset" to other cells accordingly, rather than fixed addresses.
For example, if you copy the formula (=A1*B1) from the first row to the second row of the sheet, the formula will become (=A2*B2). The relative references are mainly useful if there is a need for using the same formula or calculation in multiple cells within the workbook (s).
When do the relative references in Excel change?
As discussed above, the relative references are the default reference type used by Excel. When we use relative references, they automatically change after copying them to another location in the sheet or workbook. Each referred cell with relative reference changes with left, right, top or bottom movement.
For example, suppose we give relative reference to cell D9 and perform the movement in the following ways within the sheet:
How to create/ make relative references in Excel?
To create or make a relative reference, we should include the equal sign before the cell reference to refer to any specific cell in the sheet. Depending on the requirement for our formula, we can either refer to a cell or a range. Below are the steps explaining how to create a basic relative reference in Excel:
How to use relative references in Excel?
Let us now understand the concept of Excel relative referencing better with the help of the below examples:
Relative Referencing in Excel: Example 1
Consider the following sheet as an example data set where numbers in four different cells, A1, A2, B1, and B2, are recorded.
Suppose we need to SUM values in A1 and A2 in the below cell A3. Likewise, we also need to SUM values in B1 and B2 in cell B3.
Relative Referencing in Excel: Example 2
Consider the following Excel sheet as an example data set where we list different items with their prices and sold quantities.
We need to calculate the Sale Price/Value for each item separately in column D. To do this, we need to multiply the sold quantities by their respective price.
Important Points to Remember