AutoFill for formula copying in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does AutoFill do when copying formulas in Google Sheets?

AutoFill copies a formula from one cell to adjacent cells, adjusting cell references automatically to match each new position.

beginner

How do you use AutoFill to copy a formula down a column?

Click the cell with the formula, then drag the small square at the bottom-right corner of the cell (the fill handle) down over the cells you want to fill.

beginner

What happens to relative cell references when you AutoFill a formula?

Relative references change based on the new cell's position. For example, if the formula uses A1 and you fill down one row, it changes to A2.

beginner

How can you keep a cell reference fixed when using AutoFill?

Use the $ sign to make an absolute reference, like $A$1. This keeps the reference the same when copying the formula.

beginner

What is the difference between relative and absolute references in formulas?

Relative references change when copied to new cells; absolute references stay fixed no matter where the formula is copied.

What do you drag to use AutoFill in Google Sheets?

A The row number

B The formula bar

C The sheet tab

D The fill handle (small square at cell corner)

The fill handle is the small square at the bottom-right corner of a selected cell used to drag and copy formulas.

If a formula in cell B2 is =A1 and you AutoFill it down one row, what will the formula in B3 be?

A =A2

B =A1

C =B1

D =B2

Relative references adjust by row when AutoFilled down, so A1 becomes A2 in the next row.

How do you write a formula reference to keep it fixed when copying?

A Use # before the cell, like #A1

B Use & before the cell, like &A1

C Use $ before column and row, like $A$1

D Use * before the cell, like *A1

The $ sign fixes the column and/or row in a formula reference.

What happens if you AutoFill a formula with absolute references?

A The formula is deleted

B The references stay the same in all copied cells

C The references change relative to the new cells

D The formula converts to text

Absolute references do not change when copied or AutoFilled.

Which of these is NOT a use of AutoFill?

A Changing the sheet name

B Filling a series of numbers

C Repeating text patterns

D Copying formulas quickly

AutoFill cannot change sheet names; it copies or fills data within cells.

Explain how AutoFill works when copying formulas in Google Sheets.

Think about what happens when you drag the small square on a cell with a formula.

Describe the difference between relative and absolute references in formulas and how they behave with AutoFill.

Consider how cell references update or stay the same when copying formulas.

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