Relative reference in Excel is akin to instructing the software, 'I've placed this formula here based on the values relative to this cell. Now, apply the same logic to the rest of the values.' It's undoubtedly a lifesaver! In this article, we'll delve into the various applications of relative references and explore why it's a valuable technique to incorporate into your Excel workflow.
What is Relative Reference in Excel
In the simplest terms, a relative reference in Excel is like telling the program to be smart and adjust your formula based on the cell you put it in. It keeps everything connected and correct as you move the formula around. So technically, a relative reference cell is a cell reference that changes according to to the location you copy the formula in.
How to Use Relative Reference in Excel
To grasp the different types of references in Excel and avoid confusion between relative and absolute references, let's understand type's characteristics.
Relative Reference
A relative reference is one that adjusts automatically when you copy or move a formula to another cell. It does not contain the dollar signs "$".
An example will help us better understand the concept of relative reference in Excel
Step 1: Consider the data presented below, which consists of a matrix with 4 columns and 4 rows, containing repeated digits.
Step 2: We will be using the cell relative reference; A2, as the reference from another cell.
Step 3: When we do so, it gives us the value of 11.
Step 4: Now, if we copy this cell value and paste it in cell D5, we will get the value of 88.
Step 5: Notice how the reference that was originally pointing to A2 automatically changes to B5. This behavior is known as Relative Reference.
Absolute Reference
An absolute reference is a reference that remains constant regardless of where you copy or move the formula. It starts with a dollar sign "$".
In the same example mentioned earlier, instead of referring to A2, we will use $A$2.
Step 1: Using $A$2 as the reference will once again give us the value 11.
Step 2: However, if we copy this reference and paste it into cell D5, it will still give us the value 11, not 88.
Step 3: In this case, the reference maintained its absolute nature, as indicated by the use of dollar signs before both the column (A) and row (2) references. As a result, the reference remains fixed, regardless of where it is copied or pasted within the spreadsheet.
Mixed Reference
A mixed reference is a combination of relative and absolute references, where either the row or the column remains fixed. This is achieved by using a dollar sign before the row, the column, or both.
Let’s say you copy a value from cell A1 to cell D4. Following scenarios can occur:
A1 (relative column, relative row) will change to D4 (relative reference)
$A1 (fixed column, relative row) will change to $A4 (mixed reference)
A$1 (relative column, fixed row) will change to D$1 (mixed reference)
$A$1 (fixed column, fixed row) will change to $A$1 (fixed reference)
We have an example below to help ease the process of understanding Mixed Reference in Excel.
Step 1: Continuing with the same example, we can create a mixed reference by keeping the column fixed and the row relative. Let's use $A2 as the reference.
Step 2: When we paste this reference into another cell, it will display the value 77.
Step 3: As you can observe, $A2 changes to $A5, retaining the column (A) intact while adjusting the row (from 2 to 5). This combination of fixed and relative elements in the reference provides flexibility when copying formulas or values within the spreadsheet.
Example
Let’s take an example of our clothing store Xfactor, where we manage the sales data through an excel sheet with different columns for product names, quantities sold, and unit prices. For our sales data, we were looking to calculate the Total Sales and also apply a discount percentage of 10% to each unit price to determine the discounted price. This example will help us understand how both relative references in Excel can be used alongside absolute reference.
Let’s look at our sales data sheet:
Step 1: Let’s start the function in cell D3 in our Total Sales column. The function will start off with the cell B3 as a relative reference; excluding the $ sign.
Step 2: Now, we want our quantity sold; B3, to be multiplied with the unit price; C3, which again will be a relative reference as we want the reference to change for each row; C4 and C5.
Step 3: Here comes the absolute reference. Since the discount percentage is the same for each product, we don’t want Excel to change the reference for each cell. Therefore we will be using the absolute reference cell B8 in our example.
Step 4: Simply use the Autofill handle to copy the formula on to the other cells.
Looking at the result, excel has effectively used mixed referencing to calculate the Total sales of each product.
How did the referencing change? Let’s take a look at our formula in cell D4.
The cell B3 and cell C3 changed to cell B4 and C4 as a result of relative reference. Whereas, the cell B8 did not change throughout as a result of absolute reference.
Excel Relative Reference Not working
There two major reasons why Excel relative reference might not be working in your calculations
Problem 1
The issue might be due to the "Manual" calculation option in Windows. This setting affects how formulas are calculated in your Excel sheet.
Solution: To resolve this issue, users can simply select “Automatic” instead of “Manual”.
Problem 2
The problem could be due to using incorrect mixed cell references in your worksheet. Make sure to use the right type of mixed references to avoid errors.
Solution: Use cell referencing correctly. Make sure the relative and absolute reference are selected appropriately for each cell.
Best Free Alternative to Microsoft Office - WPS Office
Imagine streamlining your tasks with ease using simple shortcuts like Ctrl+C and Ctrl+V, powered by the convenience of relative reference in Excel. However, the possibilities extend beyond Excel alone. Introducing WPS Office, an exceptional alternative to Office 365, offering a valuable hack that can revolutionize your workflow. The best part? It comes at no cost! With an intuitive interface akin to Excel, WPS Office's spreadsheet tool stands as a robust contender, adept at handling all tasks you expect from Excel, but without the cost!
Use the relative reference in WPS Office
Users can use the Relative Reference in WPS Office in a very similar way to Excel. A small set of sample data will help us understand this further:
In the above example, we have a fixed value of “10”. We want to find the product of the fixed value and the value in cells A2:A5
Step 1: We will start the function with an Equal sign followed.
Step 2: To find the product, we will select our cell A2; relative reference, as we want Excel to change reference.
Step 3: For our product, we will be selecting cell B8
Step 4: Forgot to enter the $ sign for our absolute reference? The relative reference shortcut can also be used to efficiently change the reference style. Simply press “F4” to toggle between relative, absolute and mixed reference.
Step 5: Press “Enter” for the result. Use the Autofill handle to paste to fill other cells for results.
FAQs
Q1. How to reference another sheet or workbook in Excel?
To add reference from another sheet of the ame workbook in Excel, simply enter the sheet name with an exclamation sign followed by the cell. Eg. If we want to add E4 as our reference from sheet 4, simply type “sheet4!E4”. To add an absolute reference, simply use the relative reference shortcut key “F4” to add the $ sign.
Q2. What are the 3 types of cell references in Excel?
The 3 types of cell reference in Excel are:
Relative Reference
Absolute Reference
Mixed Reference
Q3. What is the difference between relative and absolute reference?
In simple terms:
When copied, a relative reference modifies to reflect its new location.
When copied, an absolute reference doesn't change; it stays the same.
Q4. What does '$' mean in Excel formula?
The '$' symbol is used in Excel formulas to denote an absolute reference. A cell reference is fixed and won't change when the formula is copied or filled into other cells if it is prefixed with the symbol "$"
Master Relative Referencing in WPS Spreadsheet
By using relative references, you can significantly simplify your data handling process. With just a single input of the formula, Excel takes care of the rest for you. In this article, we explored how to use Relative Reference in Excel, but the same functionality is also available in WPS Spreadsheet. WPS offers a similar interface, but it's even more intuitive, making it a breeze to work with. Don't hesitate to download WPS Office now and get started with seamless data management.
