AutoFill and Flash Fill in Excel - Step-by-Step Guide

Introduction

AutoFill and Flash Fill help you quickly fill cells with patterns or extract data without typing everything. AutoFill copies or continues a series, while Flash Fill recognizes patterns and fills data automatically.

When you want to copy a formula or value down a column quickly.

When you need to fill dates or numbers in a sequence without typing each one.

When you want to split full names into first and last names automatically.

When you want to combine data from two columns into one without formulas.

When you want to format data consistently, like phone numbers or IDs, based on a pattern.

Steps

Step 1: Type the starting value or formula in a cell

- Worksheet cell

The cell shows the value or formula result

💡 For sequences, type the first one or two values to establish a pattern

Step 2: Click the cell to select it

- Worksheet cell

The cell is highlighted with a small square at the bottom-right corner

Step 3: Drag the small square (fill handle) down or across the cells you want to fill

- Fill handle at bottom-right corner of the selected cell

Cells fill with copied values, formulas, or continued series

Step 4: Type an example of the desired pattern in the adjacent column

- Next column cell

The cell shows the example data you typed

Step 5: Press Enter to confirm the example

- Cell with example data

Excel suggests the rest of the column filled based on the pattern

💡 If Excel does not suggest automatically, select the range and press Ctrl+E to trigger Flash Fill

Step 6: Press Enter to accept the Flash Fill suggestions

- Highlighted suggested cells

Cells fill automatically following the pattern you showed

Before vs After

Before

Column A has dates 1/1/2024 and 1/2/2024 in first two cells; Column B has full names like 'John Smith' in first cell only

After

Column A fills dates down to 1/10/2024 automatically; Column B splits full names into first and last names in adjacent columns using Flash Fill

Settings Reference

Enable Flash Fill

📍 File > Options > Advanced > Editing options

Turns Flash Fill feature on or off

Default: On

Fill Series

📍 After dragging fill handle, click Auto Fill Options button

Controls how AutoFill fills the selected cells

Default: Copy Cells

Common Mistakes

Dragging the fill handle without establishing a clear pattern

Excel may just copy the same value instead of continuing a series

Type at least two values to show the pattern before dragging

Expecting Flash Fill to work without examples

Flash Fill needs an example to detect the pattern

Type the desired output for the first cell before using Flash Fill

Not enabling Flash Fill in Excel options

Flash Fill will not work if disabled

Check and enable Flash Fill in File > Options > Advanced

Summary

AutoFill copies or continues values and formulas quickly by dragging the fill handle.

Flash Fill automatically fills data based on a pattern you show by typing examples.

Remember to provide examples and enable Flash Fill in options for best results.