Filling Data Efficiently

Autofill and Flash Fill are both about reducing repetitive work, but they have fundamentally different jobs. Autofill extends — it copies or continues what already exists. Flash Fill transforms — it extracts or reformats based on a pattern you demonstrate.

The same drag gesture on different seed values produces completely different behaviour. Try each scenario below to see what actually happens:

The Autofill Options menu (the small icon that appears after dragging) is the safety valve. It lets you explicitly choose the fill type instead of trusting Excel's inference.

Custom Lists

Excel ships with built-in sequences (Mon–Sun, Jan–Dec) but you can add any sequence your workflow needs. Once registered, it works exactly like the built-in ones — type the first item and drag to continue.

How to create a custom list (Windows):

1. File → Options → Advanced — scroll to the General section at the bottom;
2. Click Edit Custom Lists;
3. In the List entries box, type each item on its own line, pressing Enter after each one;
4. Click Add then OK — the sequence is now available globally across all workbooks.

How to create a custom list (Mac):

1. Excel → Settings → Custom Lists — or go to Excel in the menu bar and choose Preferences;
2. Click Custom Lists under the Formulas and Lists section;
3. In the List entries box, type each item on its own line, pressing Enter after each one;
4. Click Add then you can close the window — the sequence is now available globally across all workbooks.

Flash Fill

Flash Fill reads the pattern between your source column and the example you type, then replicates that transformation for every remaining row. You never write a formula — you just show Excel what the result should look like.

Flash Fill handles a surprisingly wide range of transformations from a single example:

1. Split names — source: "Emily Johnson" → example: "Emily" → Flash Fill extracts all first names;
2. Extract domain from email — source: "user@company.com" → example: "company.com" → fills all domains;
3. Reformat dates — source: "2026-03-01" → example: "01/03/2026" → reformats all dates;
4. Combine columns — source cols A+B: "John" + "Smith" → example: "John Smith" → merges all rows;
5. Extract codes — source: "ORDER-2024-0042" → example: "0042" → pulls trailing numbers from all rows.