Relative and absolute reference in google sheets are two of the most important concepts. They reduce workload and stress when calculating numerical values with functions and formulas on the worksheet.
When used correctly, the need for inputting and computing data manually is dissuaded. In this tutorial, we are going to evaluate and discuss what these references are and how to use them efficiently on your worksheet.
What is Relative Reference?
A relative cell reference is an unlocked cell on the google sheet. Also, it is the custom or default reference of the worksheet. It stimulates the Google sheet operating system to become aware of the relative position of the cells.
When formulas and functions are used, the relative reference locates the cells and their values which enables automatic updates across the worksheet.
What is Absolute Reference?
An absolute reference is a locked cell on a worksheet. It is denoted by adding the dollar signs into the values of the formula.
This tells the Google sheets program to lock the data in the cell. No matter where the data moves on the worksheet or document, it stays locked and is referred to as the absolute cell reference.
Relative Reference in Google Sheets.
In the worksheet below, we have some items, the units sold, and their unit costs but we want to find the total sales made on each item.
We are going to show you how the relative reference works on Google sheets.
Step 1: Firstly, we want to find the total sales made on the first item on the worksheet. Therefore, we multiply the units sold by the unit cost using the formula =B2*C2
Step 2: Press enter on the keyboard and the result is pasted on the result column.
Step 3: There are other items we need to calculate their total sales. Instead of typing the formula manually over and over again in each cell, we would use the relative cell reference to solve this problem.
Step 4: Place your cursor on the bottom corner of the first cell, it turns into a plus sign. Right-click and drag down the values into the cells. This has automatically copied and pasted the values into each cell.
Alternatively, the Google sheet would understand what you want to copy and an autofill pop up is produced asking if you want the data copied and pasted.
Click on the tick icon and the values are pasted into their respective cells. This is a result of the relative cell reference.
Step 5: To confirm if the copied data is correct, double click on the cell or press the F2 shortcut on your keyboard to place the cell in edit mode. You can see that the correct values are multiplied and produce accurate results.
Absolute Reference in Google Sheets.
Using the previous worksheet, we want to add a discount on the items listed. In cell D12, we have inputted the discount percentage of two per cent. So we want to calculate the discount on each item.
Step 1: Multiply the price per unit by the discount percentage of the first item by using the formula =E2*C12
Step 2: Press Enter on the keyboard and the discount price is displayed.
Step 3: Now, we would like to copy and paste the formula down the worksheet. Drag down and drop the formula by right-clicking.
Step 4: Press enter and invalid values and errors are pasted affecting the whole dataset, which is not right. This error occurred because cell C12 has not been made the absolute reference.
When we copied the formula downwards, click on another cell e.g cell B3, and notice that cell C12 is no longer referred to, but an empty cell C13 is referred to. This is the primary cause of the errors and invalid results.
To prevent this, we have to assign cell C12 as the absolute reference by adding dollar signs ($) in between the elements, that is $C$12 or by pressing the shortcut F4 on the keyboard, this adds and removes dollar signs into the elements.
This locks the values in the cell which makes it constant throughout the copy and paste process. So we try it again,
Step 1: Multiply the price per unit by the discount percentage of the first item by using the formula =E2×$C$12
Step 2: Press Enter on the keyboard and the discount price is displayed.
Step 3: Now, we would like to copy and paste the formula down the worksheet. Drag down and drop the formula by right-clicking.
Step 4: The valid results are pasted onto the column. To confirm the values, double click on a cell or press F2 on the keyboard to edit the cell.
The absolute cell reference remains in the cell. If you move the reference to another cell or another sheet, the results remain unaffected because it is locked on the dataset.
There are other ways you can place the absolute reference.
- Column and row reference ($B$1): The column and row remain locked no matter where the formula is located. This is the most common method used.
- Column reference ($B1): The column remains fundamental no matter where the formula is located, but the rows can be changed.
- Row reference (B$1): The row remains constant no matter where the formula is located, but the column can be edited or changed.
Final Thoughts:
With these significant concepts in Google sheets, calculations for large sets of data are easily performed and become less stressful. Now you know the process of Relative And Absolute Reference in Google Sheets.
I hope you found this tutorial helpful. Thank you.
