• Login / Signup

    • Welcome Back

    Google icon Sign in with Google
    OR
    Forgot Password?
    I agree to abide by Pharmadaily Terms of Service and its Privacy Policy

    Create Account

    Google icon Sign up with Google
    OR
    By signing up, you agree to our Terms of Service and Privacy Policy
    Instagram
    youtube
    Facebook

    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 E1 as TaxRate

    • Use the formula: =D2 + (D2 * TaxRate)

    Much easier to understand and manage!

    Step 4: How to Create a Named Range

    Steps:

    1. Select the cell or range (e.g., E1)

    2. Go to Data > Named ranges

    3. In the side panel, give it a name (e.g., TaxRate)

    4. 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.

    ❮ Previous Next ❯