Excel Flash Fill (Ctrl+E): Pattern-Based Data Cleaning Masterclass

Flash Fill detects a pattern from one or two examples you provide in a worksheet and automatically completes the rest of the column.

Task: With Amit already typed for Row 2, trigger Flash Fill to extract first names for the entire roster:

Task: Extract the surname from each customer name:

Extract the username prefix before the @ sign without writing a single text formula:

Convert continuous 10-digit phone numbers into hyphenated format (98765-43210):

Combine First Name and Last Name into Full Name with Flash Fill:

Pattern: Take the first two letters of the name in uppercase and append the employee number with a hyphen (AM-101):

Interactive Experiment: Edit the source name below from Amit Sharma to Ankit Verma. Observe that the formula updates dynamically, while the Flash Fill result stays static:

When datasets contain varying name lengths (e.g. Amit Kumar Sharma with a middle name), Flash Fill may not know whether you want the middle name included.

Convert inconsistent lowercase names into Proper Case:

Determine whether Flash Fill or a formula is the appropriate tool:

In addition to the shortcut Ctrl + E, Flash Fill can be triggered from two locations in the Excel ribbon:

Scenario: Prepare the employee roster by generating formatted Employee IDs (AM-101) and extracting email usernames:

Test your knowledge of Flash Fill shortcuts, pattern recognition rules, and verification standards:

Checklist for foolproof Flash Fill adoption:

• Always Place Output Adjacent to Source: Flash Fill inspects cells immediately to the left or right of the active column to deduce patterns.
• Spot-Check Irregular Rows: Scan through rows with unusual formats (e.g. hyphenated last names or special characters) to confirm pattern fidelity.
• Undo Capability: If Flash Fill produces unexpected outputs, press Ctrl + Z immediately or click the Flash Fill smart tag icon to undo or accept suggestions.