AutoFill for formula copying in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

AutoFill formula with relative references

You enter the formula =A1+B1 in cell C1 and then use AutoFill to copy it down to C2 and C3. What will be the formula in cell C3?

A =A3+B3

B =A1+B1

C =A2+B2

D =A4+B4

💡 Hint

Think about how relative references change when you copy formulas down rows.

When you copy a formula down, relative references adjust by the number of rows moved. From C1 to C3 is 2 rows down, so A1 becomes A3 and B1 becomes B3.

📊 Formula Result

intermediate

AutoFill formula with mixed references

You enter the formula =A$1+B2 in cell C2 and then use AutoFill to copy it down to C4. What will be the formula in cell C4?

A =A4+B4

B =A$4+B4

C =A$1+B2

D =A$1+B4

💡 Hint

Remember that the dollar sign before the row number fixes the row when copying.

The reference A$1 fixes the row 1, so it stays A$1 when copied down. The B2 reference is relative and changes to B4 when copied two rows down.

❓ Function Choice

advanced

Choosing the correct formula for AutoFill across columns

You want to sum values in row 1 from columns A to D and place the sums in row 2, columns A to D respectively. Which formula should you enter in cell A2 and then AutoFill across to D2 to get correct sums for each column?

A =SUM(A$1:A$1)

B =SUM($A$1:$A$1)

C )1$A:1$A(MUS=

D =SUM($A1:$A1)

💡 Hint

Consider how column and row references behave when copying formulas across columns.

Using A$1 fixes the row 1 but allows the column to change if copied across columns. Copying across columns changes the column letter, so A$1 becomes B$1, C$1, etc., summing each column's row 1 value.

🎯 Scenario

advanced

AutoFill with absolute references to fix a cell

You have a tax rate in cell B1. You want to calculate tax for amounts in column A starting from A2. You write the formula =A2*B1 in cell C2 and AutoFill it down. What should you change in the formula to keep the tax rate fixed when copying down?

A =A2*B1

B =$A$2*B1

C =A2*$B$1

D =$A2*$B1

💡 Hint

Use dollar signs to fix both column and row of the tax rate cell.

To keep the tax rate fixed at B1 when copying down, use absolute reference $B$1. This prevents the reference from changing as the formula is copied.

❓ data_analysis

expert

Analyzing AutoFill behavior with mixed references in a large table

You have a table with sales data in columns A to D and rows 2 to 10. You want to calculate a 10% commission for each sale in column E. You enter the formula =B2*$D$1 in cell E2 and AutoFill it down to E10. What will be the formula in cell E10?

A =B2*$D$1

B =B10*$D$1

C =B$10*$D$1

D =B10*D10

💡 Hint

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

B2 is a relative reference and changes to B10 when copied down 8 rows. $D$1 is absolute and stays fixed at D1.

Practice

1. What happens to cell references when you use AutoFill to copy a formula down a column in Google Sheets?

easy

A. Formulas are deleted after copying.

B. Cell references stay exactly the same without changing.

C. Cell references adjust automatically to refer to the next rows.

D. Only text values are copied, not formulas.

Solution

1. Step 1: Understand AutoFill behavior

   When you drag the fill handle down, Google Sheets copies the formula to new cells.

2. Step 2: Observe cell reference adjustment

   Relative cell references change to match the new row numbers automatically.

3. Final Answer:

   Cell references adjust automatically to refer to the next rows. -> Option C

4. Quick Check:

   AutoFill changes relative references [OK]

Hint: AutoFill changes relative references automatically [OK]

Common Mistakes:

• Thinking references stay fixed without $
• Believing formulas get deleted
• Confusing values with formulas

2. Which of these formulas will keep the reference to cell A1 fixed when copied down using AutoFill?

easy

A. =$A$1+10

B. =$A1+10

C. =A$1+10

D. =A1+10

Solution

1. Step 1: Identify absolute reference syntax

   Using $ before column and row fixes the reference completely.

2. Step 2: Check each option

   =$A$1+10 uses $A$1 which fixes both column A and row 1, so it won't change when copied.

3. Final Answer:

   =$A$1+10 -> Option A

4. Quick Check:

   Use $ before column and row to fix reference [OK]

Hint: Use $ before column and row to fix reference [OK]

Common Mistakes:

• Using only one $ fixes only part of the reference
• Forgetting $ causes reference to shift
• Confusing relative and absolute references

3. If cell B2 contains the formula =A2*2 and you drag the fill handle down to B4, what will be the formula in cell B4?

medium

A. =A2*2

B. =A4*2

C. =B2*2

D. =A3*2

Solution

1. Step 1: Understand relative reference adjustment

   The formula in B2 uses A2, a relative reference that changes row number when copied down.

2. Step 2: Calculate new reference in B4

   Copying down two rows changes A2 to A4 in B4.

3. Final Answer:

   =A4*2 -> Option B

4. Quick Check:

   Relative references update row numbers when copied down [OK]

Hint: Relative references update row numbers when copied down [OK]

Common Mistakes:

• Assuming reference stays at A2
• Mixing column and row references
• Copying formula without dragging fill handle

4. You tried to copy the formula =SUM(A1:A3) down a column using AutoFill, but the range did not adjust as expected. What is the likely problem?

medium

A. You forgot to press Enter after typing the formula.

B. AutoFill does not work with SUM formulas.

C. The formula contains a syntax error.

D. The range uses absolute references like $A$1:$A$3.

Solution

1. Step 1: Check the range references in the formula

   If the range uses $ signs like $A$1:$A$3, it is fixed and won't change when copied.

2. Step 2: Understand AutoFill behavior with fixed ranges

   AutoFill copies the formula exactly if references are absolute, so the range stays the same.

3. Final Answer:

   The range uses absolute references like $A$1:$A$3. -> Option D

4. Quick Check:

   Absolute ranges do not adjust when copied [OK]

Hint: Check for $ in ranges to see if they are fixed [OK]

Common Mistakes:

• Assuming AutoFill can't copy SUM formulas
• Ignoring $ signs in ranges
• Thinking syntax error causes range not to adjust

5. You have a formula in cell C2: =IF(A2>10, B2*2, B2/2). You want to copy it down to C10 using AutoFill, but keep the reference to column B fixed while allowing the row number to change. Which formula should you enter in C2 before AutoFill?

hard

A. =IF(A2>10, $B2*2, $B2/2)

B. =IF(A2>10, B$2*2, B$2/2)

C. =IF(A2>10, $B$2*2, $B$2/2)

D. =IF(A2>10, B2*2, B2/2)

Solution

1. Step 1: Understand fixing column but not row

   To fix column B but allow row to change, use $ before column only: $B2.

2. Step 2: Check each option

   =IF(A2>10, $B2*2, $B2/2) uses $B2 which fixes column B and allows row to update when copied down.

3. Final Answer:

   =IF(A2>10, $B2*2, $B2/2) -> Option A

4. Quick Check:

   Use $ before column only to fix column in references [OK]

Hint: Use $ before column only to fix column, row changes [OK]

Common Mistakes:

• Fixing both column and row with $B$2
• Fixing row instead of column with B$2
• Not using $ at all causes column to shift