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.