AutoFill for formula copying in Google Sheets - Step-by-Step Guide

Introduction

AutoFill helps you quickly copy a formula to nearby cells without typing it again. It saves time and keeps your calculations consistent across rows or columns.

When you calculate total prices for a list of items and want the same formula for each row.

When you have a formula in one cell and want to apply it down a column for monthly expenses.

When you want to copy a formula across a row to calculate discounts for multiple products.

When you update one formula and want the changes to apply automatically to other cells.

When you want to fill a series of dates or numbers based on a pattern next to your formulas.

Steps

Step 1: Click

- cell containing the formula you want to copy

The cell is selected and shows a small blue square at the bottom-right corner

💡 This small square is called the fill handle

Step 2: Hover your mouse over the fill handle

- bottom-right corner of the selected cell

The cursor changes to a small plus sign (+)

Step 3: Click and drag the fill handle down or across the cells where you want to copy the formula

- adjacent cells below or to the right of the original cell

The formula is copied to all selected cells, adjusting cell references automatically

Step 4: Release the mouse button

- after dragging over the desired cells

The selected cells show the calculated results based on the copied formula

Step 5: Double-click the fill handle

- bottom-right corner of the original formula cell

The formula auto-fills down the column until it reaches a blank row

💡 This is a quick way to fill formulas down a long list

Before vs After

Before

Cell B2 contains the formula =A2*10, cells B3 to B10 are empty

After

Cells B2 to B10 contain formulas =A2*10, =A3*10, ..., =A10*10 with results calculated

Settings Reference

Relative vs Absolute References

📍 In the formula bar when editing a formula

Controls how cell references change when formulas are copied with AutoFill

Default: Relative references

Enable/Disable AutoFill

📍 Google Sheets settings under Tools > Enable autocomplete

Turns on or off the AutoFill feature and formula suggestions

Default: On

Common Mistakes

Dragging the fill handle without checking if cell references should stay fixed

Relative references change automatically and may cause wrong calculations if some references should not change

Use $ signs to make references absolute where needed before using AutoFill

Double-clicking the fill handle when there are blank rows in the adjacent column

AutoFill stops at the first blank row, so formulas may not fill the entire range

Ensure the adjacent column has no blank rows or drag manually to fill all needed cells

Summary

AutoFill copies formulas quickly to nearby cells by dragging the fill handle.

It adjusts cell references automatically unless you use absolute references.

Double-clicking the fill handle fills down until a blank row in the adjacent column.

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