Absolute references ($) in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Understanding Absolute References in a SUM Formula

You have a table where cell B1 contains the value 10, and cells A2 to A4 contain 1, 2, and 3 respectively. You enter the formula =A2*$B$1 in cell B2 and then drag it down to B4. What is the value in cell B4 after dragging?

A 60

B 3

C 30

D 10

💡 Hint

Remember that $B$1 is an absolute reference and does not change when dragging.

The formula multiplies the value in column A by the fixed value in B1 (which is 10). For B4, A4 is 3, so 3 * 10 = 30.

❓ Function Choice

intermediate

Choosing the Correct Absolute Reference for a Discount Calculation

You want to calculate the discounted price for items listed in column A, where the discount rate is in cell C1. Which formula correctly applies the discount to the price in A2 and can be dragged down without changing the discount cell?

A =A2*(1-$C$1)

B =A2*(1-C1)

C =$A$2*(1-C1)

D =A$2*(1-$C1)

💡 Hint

The discount rate cell should not change when dragging the formula down.

Using $C$1 locks the discount rate cell so it stays fixed when dragging. Option A is correct.

❓ data_analysis

advanced

Analyzing the Effect of Mixed References in a Multiplication Table

You create a multiplication table where row 1 (B1:E1) contains numbers 1 to 4, and column A (A2:A5) contains numbers 1 to 4. In cell B2, you enter the formula = $A2 * B$1 and drag it across to E5. What is the value in cell D4?

A 8

B 9

C 16

D 12

💡 Hint

Look at how the mixed references lock either the row or the column when dragging.

In D4, $A2 locks column A but row changes to 4, so value is A4=4 (A2=1, A3=2, A4=3, A5=4). B$1 locks row 1 but column changes to D (4) (B1=1, C1=2, D1=3, E1=4). So 3 * 3 = 9.

🎯 Scenario

advanced

Fixing a Formula to Correctly Reference a Tax Rate Cell

You have a list of prices in column B and a tax rate in cell D1. You want to calculate the total price including tax in column C. The formula in C2 is =B2*(1+D1). When you drag this formula down, the tax rate reference changes incorrectly. Which formula fixes this issue?

A =B2*(1+$D1)

B =B2*(1+D$1)

C =$B2*(1+D1)

D =B2*(1+$D$1)

💡 Hint

The tax rate cell should stay fixed when dragging down.

Using $D$1 locks both column and row of the tax rate cell, so it does not change when dragging.

📊 Formula Result

expert

Determining the Result of a Complex Formula with Mixed Absolute References

In a spreadsheet, cell B1 contains 5, and cell C2 contains 3. You enter the formula =B$1 + $C2 in cell D3 and then drag it one cell right and one cell down to cell E4. What is the value in cell E4?

A 0

B 10

C 6

D 8

💡 Hint

Check how the mixed absolute references affect row and column changes when dragging.

Dragging right shifts the column of B$1 to C$1 (row fixed), and dragging down shifts the row of $C2 to $C3 (column fixed). C1 and C3 are empty (0), so C$1 + $C3 = 0 + 0 = 0.

Practice

1. What does an absolute reference like $A$1 do in Google Sheets?

easy

A. It locks only the row but allows the column to change when copied.

B. It locks only the column but allows the row to change when copied.

C. It locks both the column and row so the reference doesn't change when copied.

D. It allows both the column and row to change when copied.

Solution

1. Step 1: Understand absolute references

   An absolute reference uses $ before the column letter and row number to fix both.

2. Step 2: Effect when copying formulas

   When copied, the reference $A$1 stays exactly the same, not changing column or row.

3. Final Answer:

   It locks both the column and row so the reference doesn't change when copied. -> Option C

4. Quick Check:

   Absolute reference = fixed column and row [OK]

Hint: Look for $ before both column and row to lock both [OK]

Common Mistakes:

• Thinking $ locks only one part without checking both
• Confusing relative and absolute references
• Assuming $ allows changes when copying

2. Which of the following is the correct syntax for an absolute reference to cell B3 in Google Sheets?

easy

A. $B$3

B. B$3$

C. $3$B

D. 3$B$

Solution

1. Step 1: Recall absolute reference format

   Absolute references use $ before the column letter and row number, like $B$3.

2. Step 2: Check each option

   Only $B$3 correctly places $ before column B and row 3.

3. Final Answer:

   $B$3 -> Option A

4. Quick Check:

   Correct absolute reference syntax = $Column$Row [OK]

Hint: Always put $ before column letter and row number in order [OK]

Common Mistakes:

• Placing $ after row or column
• Swapping column letter and row number
• Using numbers before letters

3. If cells A1 and A2 contain 10, cell B1 contains the formula =A1*$C$1, and C1 contains 5, what will be the value in B2 after copying the formula from B1 to B2?

medium

A. 5

B. 10

C. Error

D. 50

Solution

1. Step 1: Understand formula in B1

   The formula multiplies A1 (10) by $C$1 (5). $C$1 is absolute, so it stays fixed.

2. Step 2: Copy formula to B2

   When copied to B2, A1 becomes A2 (10), but $C$1 stays fixed at 5. Result is 10*5=50.

3. Step 3: Assume A2 same as A1 (10)

   Value in B2 = 10 * 5 = 50.

4. Final Answer:

   50 -> Option D

5. Quick Check:

   Absolute reference keeps C1 fixed, so 10*5=50 [OK]

Hint: Absolute $C$1 stays fixed; relative A1 changes to A2 [OK]

Common Mistakes:

• Assuming $C$1 changes when copied
• Ignoring relative reference change from A1 to A2
• Confusing values in cells

4. You have the formula =SUM(A1:A5)*$B$1 in cell C1. When you copy it to cell C2, the formula changes to =SUM(A2:A6)*$B$1. But you want the range to stay A1:A5. How do you fix the formula?

medium

A. =SUM(A$1:A$5)*$B$1

B. =SUM($A$1:$A$5)*$B$1

C. =SUM($A1:$A5)*$B$1

D. =SUM(A1:A5)*B1

Solution

1. Step 1: Identify why range changes

   Range A1:A5 is relative, so when copied down, it shifts to A2:A6.

2. Step 2: Use absolute references for range

   To keep range fixed, add $ before column and row: $A$1:$A$5.

3. Step 3: Check options

   Only =SUM($A$1:$A$5)*$B$1 uses full absolute references for the range and keeps $B$1 fixed.

4. Final Answer:

   =SUM($A$1:$A$5)*$B$1 -> Option B

5. Quick Check:

   Absolute range locks cells, so range stays same when copied [OK]

Hint: Add $ before both column and row in range to fix it [OK]

Common Mistakes:

• Using partial $ causing only column or row to fix
• Not fixing the range at all
• Removing $ from $B$1 accidentally

5. You want to create a formula in cell D2 that multiplies the value in column B of the same row by the fixed tax rate in cell $F$1. You plan to copy this formula down the column. Which formula should you use in D2?

hard

A. =B2*$F$1

B. =$B$2*F1

C. =B$2*$F1

D. =$B2*$F1

Solution

1. Step 1: Identify relative and absolute parts

   The value in column B changes by row, so B2 should be relative (no $). The tax rate in F1 is fixed, so $F$1 is absolute.

2. Step 2: Check formula options

   =B2*$F$1 uses B2 (relative) and $F$1 (absolute), perfect for copying down.

3. Step 3: Verify copying behavior

   When copied down, B2 becomes B3, B4, etc., but $F$1 stays fixed, applying the same tax rate.

4. Final Answer:

   =B2*$F$1 -> Option A

5. Quick Check:

   Relative row for B, absolute for tax rate cell [OK]

Hint: Use relative for row-changing cells, absolute for fixed constants [OK]

Common Mistakes:

• Fixing B2 with $ causing no row change
• Not fixing tax rate cell causing errors
• Mixing $ placement incorrectly