Named Ranges & Absolute References
What You’ll Learn:
-
The difference between relative, absolute, and mixed cell references
-
How to use $A$1 style referencing effectively in formulas
-
How to create and use Named Ranges
-
A practical mini-project for applying these concepts
Step 1: Understanding Cell References
When writing formulas in Google Sheets, how you reference a cell affects how the formula behaves when copied or dragged:
| Reference Type | Syntax | Behavior |
|---|---|---|
| Relative | A1 | Changes when copied (default behavior) |
| Absolute | $A$1 | Does not change when copied |
| Mixed | A$1 / $A1 | Partially fixed – row or column remains constant |
Example:
-
Formula in
B2:=A2*10(relative) -
If dragged to
B3, it becomes=A3*10
But if you use =$A$2*10, it will always refer to A2 even when dragged.
Step 2: Practice Exercise – Relative vs Absolute
Use this data:
| Product | Price | Quantity | Total |
|---|---|---|---|
| Pen | 10 | 5 | =B2*C2 |
| Book | 50 | 2 | =B3*C3 |
| Bottle | 30 | 4 | =B4*C4 |
Now add a 10% tax in cell E1.
Try calculating final price with:
=D2 + (D2 * $E$1)
When you drag the formula down, D2 will change, but $E$1 will remain fixed.
Step 3: What Are Named Ranges?
Named Ranges let you assign a meaningful name to a cell or range of cells.
For example:
-
Name
E1asTaxRate -
Use the formula:
=D2 + (D2 * TaxRate)
Much easier to understand and manage!
Step 4: How to Create a Named Range
Steps:
-
Select the cell or range (e.g.,
E1) -
Go to Data > Named ranges
-
In the side panel, give it a name (e.g.,
TaxRate) -
Click Done
You can now use TaxRate in your formulas instead of $E$1.
Step 5: Using Named Ranges in Formulas
Try using:
=D2 + (D2 * TaxRate)
This behaves exactly like:
=D2 + (D2 * $E$1)
But it's easier to read and understand!
Mini Project: Price Calculator with Named Tax
Use this sample table:
| Item | Price | Quantity | Subtotal | Tax Amount | Final Price |
|---|---|---|---|---|---|
| Pen | 10 | 2 | |||
| Book | 50 | 1 | |||
| Bag | 100 | 1 |
Steps:
-
In cell
F1, type:0.18(for 18% GST) -
Name this cell
GST -
Fill Subtotal:
=B2*C2 -
Tax Amount:
=D2*GST -
Final Price:
=D2+E2
Now drag down the formulas. Named ranges and absolute references will keep your formulas intact and reusable.
