Goal
You’ll be able to use AutoFill and basic built-in functions confidently, and understand exactly what AutoFill actually does with cell references as it copies a formula — a detail that explains both its usefulness and its most common pitfall.
Learn
AutoFill lets you quickly copy a formula (or extend a pattern) across multiple cells by dragging the small square handle at the bottom-right corner of a selected cell. If cell C1 contains the formula =A1+B1, dragging that handle down to C2 automatically adjusts it to =A2+B2 — Excel intelligently shifts the cell references to match each new row, rather than just copying the exact same formula unchanged.
This automatic adjustment is genuinely useful — it’s exactly why you can write one formula and drag it down a hundred rows, rather than typing each one individually. But it also means you need to understand it’s happening, or a dragged formula can reference completely wrong cells without any obvious error.
Excel includes many built-in functions — pre-built formulas for common calculations:
=SUM(A1:A10) (adds every value in that range) =AVERAGE(A1:A10) (calculates the average of that range) =COUNT(A1:A10) (counts how many cells contain numbers) =MAX(A1:A10) (finds the largest value) =MIN(A1:A10) (finds the smallest value)
The colon (:) inside the parentheses defines a range — A1:A10 means “every cell from A1 through A10,” not just those two specific cells.
Decision Task
You write =SUM(A1:A5) in cell B1, then use AutoFill to drag that formula down to B2. Before reading on: what does the formula in B2 actually become?
Show Answer
=SUM(A2:A6) — AutoFill shifts every cell reference down by one row to match the new row, exactly the same relative adjustment covered for simple formulas earlier in this lesson. This applies to ranges (like A1:A5) exactly the same way it applies to single cell references.
Common Mistake
Using AutoFill to drag a formula across many rows without checking that the automatically-adjusted cell references still make sense for each new row. This is usually fine and exactly the intended behavior, but if a formula was meant to always reference one specific fixed cell (not one that should shift per row), AutoFill will silently break it — a problem this course revisits directly in the very next lesson, on absolute references.
Practice Questions
1. Write a function that calculates the sum of cells B1 through B20.
Show Answer
=SUM(B1:B20)
2. Write a function that finds the largest value among cells C1 through C15.
Show Answer
=MAX(C1:C15)
3. If cell D1 contains =A1*2, and you AutoFill it down to D2, what does the formula in D2 become?
Show Answer
=A2*2 — the reference shifts down one row to match.
4. What does the colon inside a function like SUM(A1:A10) actually specify?
Show Answer
A range — every cell from A1 through A10 inclusive, not just those two individual cells.
5. True or False: AutoFill always copies the exact same formula, completely unchanged, to every cell it fills.
Show Answer
False — AutoFill automatically shifts relative cell references to match each new row or column, which is exactly what makes it useful for filling formulas across many cells at once.
Try It Yourself
Without looking back, write a function that counts how many cells in the range E1:E30 contain numbers, then explain in one sentence what would happen if you AutoFilled this formula one column to the right, into F1.
Show Answer
=COUNT(E1:E30) — AutoFilling one column to the right would shift the column reference too, becoming =COUNT(F1:F30), following the same automatic-adjustment behavior covered in this lesson, just shifting columns instead of rows.
Quick Check
1. What does AutoFill let you do?
Show Answer
Quickly copy a formula (or extend a pattern) across multiple cells by dragging.
2. What happens to cell references when a formula is AutoFilled to a new row?
Show Answer
They automatically shift to match the new row, adjusting relatively.
3. What does the SUM function do?
Show Answer
Adds every value in a specified range.
4. What does a colon inside a function’s parentheses, like A1:A10, specify?
Show Answer
A range of cells, from the first reference through the second, inclusive.
5. What does the AVERAGE function calculate?
Show Answer
The average of the values in a specified range.