Why reference types affect formula behavior in Google Sheets - Challenge Your Understanding

Challenge - 5 Problems

📊 Formula Result

intermediate

Effect of Relative vs Absolute References on Copying Formulas

You have the formula =A1+B1 in cell C1. You copy this formula from C1 to C2. What will be the formula in C2?

A =$A1+$B1

B =$A$1+$B$1

C =A1+B1

D =A2+B2

💡 Hint

Think about how relative references change when you copy a formula down one row.

Relative references like A1 and B1 adjust when copied. Copying down one row changes them to A2 and B2. Absolute references with $ do not change.

📊 Formula Result

intermediate

Using Mixed References in Formulas

Given the formula =A$1+$B2 in cell C2, what will the formula be if copied to cell D3?

A =A$1+$B3

B =B$1+$B3

C =B$1+$C3

D =B$1+$B2

💡 Hint

Remember that the dollar sign fixes either the row or the column when copying formulas.

A$1 fixes the row 1 but allows the column to change from A to B. $B2 fixes the column B but allows the row to change from 2 to 3. So the formula becomes =B$1+$B3.

❓ Function Choice

advanced

Choosing the Correct Reference Type for a Fixed Lookup

You want to create a formula that always looks up a value in column A, row 5, no matter where you copy the formula. Which formula correctly uses references to achieve this?

A =VLOOKUP($A$5, B1:C10, 2, FALSE)

B =VLOOKUP(A5, B1:C10, 2, FALSE)

C =VLOOKUP($A5, B1:C10, 2, FALSE)

D =VLOOKUP(A$5, B1:C10, 2, FALSE)

💡 Hint

Think about how to keep both column and row fixed when copying formulas.

Using $A$5 fixes both the column A and row 5, so the lookup always refers to that exact cell regardless of where the formula is copied.

🎯 Scenario

advanced

Impact of Reference Types on Summation Across Rows

You have numbers in cells A1 to A5. You want to sum these numbers in cell B1 using the formula =SUM(A1:A5). You then copy this formula from B1 to B2. What will be the sum range in B2 if the formula uses relative references?

A SUM(A2:A6)

B SUM(A1:A5)

C SUM($A$1:$A$5)

D SUM(A1:A6)

💡 Hint

Copying a formula down one row shifts relative references down by one row.

The relative range A1:A5 shifts down one row when copied to B2, becoming A2:A6. Absolute references would not shift.

❓ data_analysis

expert

Analyzing Formula Behavior with Mixed Reference Types in a Table

You have a table with prices in column B and quantities in column C (rows 2 to 6). In column D, you want to calculate total cost per row using the formula =B2*$C$7, where C7 contains a fixed tax rate. You copy this formula down from D2 to D6. What will be the formula in cell D5?

A =B2*$C$7

B =B5*C7

C =B5*$C$7

D =B5*$C7

💡 Hint

Consider how relative and absolute references behave when copying formulas down rows.

B2 is relative and changes to B5 when copied down to row 5. $C$7 is absolute and stays fixed on cell C7. So the formula in D5 is =B5*$C$7.