AutoFill and Flash Fill in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales assistant at a retail company.

📋 Request: Your manager wants you to quickly fill in missing data and create a list of customer initials from their full names.

📊 Data: You have a table with customer full names and some missing order quantities. You need to fill missing quantities and extract initials.

🎯 Deliverable: A completed table with all order quantities filled and a new column showing customer initials.

Sample Data

Customer Name	Order Quantity
John Smith	5
Mary Johnson
Robert Brown	7
Linda Davis
Michael Wilson	9

1

Step 1: Use AutoFill to fill missing order quantities by copying the pattern from the previous filled cells.

Select cell B2 (5), drag the fill handle down to B6 to copy the value or pattern.

Expected Result

This copies 5 into B3:B6 (overwriting B4 and B6 partially). AutoFill works for constant values or simple consecutive patterns, but with gaps and existing data, use Flash Fill for better results.

2

Step 2: Use Flash Fill to fill missing order quantities based on a pattern you provide.

In cell B3, type 6 (assuming the pattern is increasing by 1), then press Ctrl+E or Data > Flash Fill.

Expected Result

Flash Fill recognizes the +1 pattern: B3=6, B4=7 (matches existing), B5=8, B6=9 (matches), completing missing order quantities.

3

Step 3: Create a new column 'Initials' to extract initials from 'Customer Name' using Flash Fill.

In cell C2, type 'JS' for John Smith, then select C2 and C3, press Ctrl+E to Flash Fill initials for all customers.

Expected Result

Column C fills with initials: JS, MJ, RB, LD, MW.

Final Result

Customer Name | Order Quantity | Initials
---------------------------------------
John Smith    | 5              | JS
Mary Johnson  | 6              | MJ
Robert Brown  | 7              | RB
Linda Davis   | 8              | LD
Michael Wilson| 9              | MW

✓AutoFill helps quickly copy or continue data patterns.

✓Flash Fill can extract or fill data based on examples you provide.

✓Using these tools saves time and reduces manual entry errors.

Bonus Challenge

Use Flash Fill to separate first and last names into two new columns.

Show Hint

Type the first name in a new column for the first row, then use Flash Fill to complete the column automatically.