How to Use Absolute and Relative Cell References in Google Sheets

In this article, we will learn about what are absolute and relative cell references in google sheets and how to use them properly in a google sheet.

We all use these but some beginners do not know what they are? and we often get confused when seeing the absolute references in a google sheets file made by other persons. We will learn what is absolute and relative references in Google Sheets. So firstly, these are cell references, two main types of cell references, one is relative (default), and the other is absolute. We normally use relative references whenever we copy the formula of function from one cell to other cells. Since the relative reference is a default cell reference, we don’t understand how it works, we only use it to copy formulas and do our work, in this article we will see both, we will see how these two work, and when we need to use default cell references and when we should use absolute cell references.

What are Absolute and Relative Cell References in Google Sheets?

Google sheets are mainly about formulas, calculations, measurements, functions, and so on. We normally do not have a lot of plain text in google sheets, rather we deal with formulas, equations, calculations, etc. Since we use various formulas in our google sheets file. We need to copy the formulas, functions, and similar calculations to apply to a bigger data set, or on the same big column. What we do, is we mostly drag the formula and copy it to the entire column, it’s pretty simple but sometimes, the formula is a little complex, and we don’t want to copy with relative references. Don’t worry I will talk about both references, particularly in the coming sections.

So when we need to have absolute values, what will we do? Using copy-paste method will not work, the drag method will also not work, and double click on the fill handle of the cell containing the formula will also not work, we need to learn how to change default cell references and make them absolute references. This is why we need to know and learn what are absolute and relative references in Google sheets.

How to Use Absolute and Relative Cell References in Google Sheets

From this section we will step-by-step cover the entire topic, as I have already talked about the basics, let’s get into the short definitions.

How to use Relative Cell Reference in Google Sheets

The relative reference is the default cell reference, it’s also called A1 (A-one) Notation. It is there by default when you copy a formula in other cells or you drag the formula to the entire column, you must have noticed this before, the formula automatically detects the value changes and keeps taking the relative references of the cells as per the pattern of the formula we have used in the very first cell.

Let’s understand it with an example.

Use Case

Suppose you have data in two columns A and B, now in the third column, you want to have the result for the calculation performed on A and B, let’s simply see the addition operation. when I write the formula in cell C1 “=A1+B1“, it will sum the values of A1+B1 and calculate the answer in the C1 cell. This is so simple, but when I copy this formula to cell C2, now what will happen? Since I am using the relative reference (i am not using, actually it is by default) so the formula copied in the cell C2 will automatically become A2+B2, because of the cell reference, now relative will change itself according to its cell address or the position in a column, similarly if I drag this formula to C10. It will keep changing its reference, and every time it will keep the relative reference of the adjacent cell.

Practical Example

In this example, I will show you how relative cell references work and behave when copied.

Step 1

Some random data

Step 2

A simple formula in Column C

Step 3

The result

Step 4

Now copy the formula below in the same column

Now select the cells and right click -> Paste

Step 5

You see the formula keeps the relative cell references; you can check by double clicking on any resultant cell.

Tips

Keep in mind that relative reference is a highly used cell reference because of two reasons, number one is the default cell reference, but again it’s the default because it’s highly used. The second reason is using this reference is easy, we do not need to do anything, and most importantly most of the data is required to keep the relative cells when the formula is copied. 

How to use Absolute Cell Reference in Google Sheets

The absolute reference is a secondary cell reference also called the Dollar Notation $Notation, it is a commonly used cell reference when working on big formulas and little more complex calculations, the simple purpose of using an absolute reference or dollar notation is to tell google sheets not to change the original cell references when the formula is copied from any other cell within the entire sheet. It also goes the same for formula dragging, and double click to auto-fill the formula since they all are the method of copying formulas so the method works the same for every method.

Now absolute references can be used in three ways.

$A1

It will keep the absolute cell reference for column A and will keep the relative cell reference for row 1.

A$1

This will keep the absolute cell reference for row 1 and will keep the relative cell reference for column A

$A$1

This will lock a particular cell and will keep the absolute cell references for column A and row 1.

Think for rows as well, we many times need to copy formulas horizontally.

Let’s see it with the help of an example

Use Case

Let’s say you have data in google sheets, the data is about some employees, you have employee name, designation, salary, and sales tax in four columns, let’s say A B C D, now you need have everything but you need to calculate the tax for each employee according to their salary. A cell in which a tax amount is written and it’s the same for same-ranked employees. Now the overall equation is simple, we have to cut a percentage from the employee’s salary and according to their rank we, need to cut down the maintenance amount from their salary. Now here, we will need to use absolute references to keep the same amount to deduct for the same ranked employee.

Practical Example

Step 1

Sample data

Step 2

A tax value in a separate cell

Step 3

Calculate tax value by dividing salary/tax rat

Step 4

Dragging the formula without Absolute cell reference (Dollar Notation)

Step 5

Error, #DIV/0!

double click on the error cell

Step 6

See it using relative references that’s why we are facing an error

Step 7

Add $ to convert into cell reference (here we only need to write the $Notation with rows)

F4 is the shortcut

Step 8

Now drag the formula again.

Step 9

Now, there is no error and the calculation is working perfectly

Step 10

What if I copy the formula horizontally?

we have to remove this error by locking rows (here we need to write $Notation with column)

Step 11

Now you are using complete absolute reference for a particular cell

Step 12

Now you can move this formula horizontally without losing the absolute cell references for the “tax value” cell, but still, you need to add $ with salary cells to lock the salary column (only column) rows should be changed relatively

This is how we can use absolute cell references in such requirements when we don’t want to lose the absolute cell or values when the formula is copied in any direction.

Free Example Template used in this Article

What are Absolute and relative cell references in Google Sheets?

Notes

FAQs 

When should I use relative cell references?

When you have a relatively simple formula or function and you don’t want to keep the original values, and you need the formula to take the cell’s values relative to its current position, then the relative cell references are good to use, and they are default, you don’t need to do anything to use relative cell references, they are automatically there when a formula is copied

When should I use absolute cell references?

When you have little complex formula (can also be used for simple formula), it mostly depends on what your formula is doing, if you want to change the values each time the formula’s position is changed you can use the default cell references, but if you need to have some fixed values every time the formula is copied, you need to define it by telling google sheets to lock a column or row or both (cell) whenever the formula is copied to other cells.

Conclusion

In this particular article, we learned what are absolute and relative cell references in google sheets? I tried to cover both of them in detail, I have used simple day-to-day examples to make you understand how these cell references work, and when we need to use each of the. Earlier we discussed the theoretical concept behind cell references, and then we tried to learn how each works, we saw use cases and practical examples with step-by-step procedures along with screenshots to make you understand it very easily without missing any step. So that’s, all from today’s tutorial, I hope you find this article helpful, please don’t forget to like, share, and Subscribe to our blog. Keep learning with Office Demy. Thank you!!