Unlocking the Power of Flash Fill in Excel
- Gaurav Chutani
- Apr 25
- 3 min read
Flash Fill is one of Excel’s most underrated features. Introduced in Excel 2013, it automatically fills in values when it detects a pattern in your data. Instead of writing complex formulas, you can simply show Excel what you want, and Flash Fill does the rest.
Let’s explore some practical use cases with examples and visuals.
✂️ 1. Splitting Names into First and Last
Scenario: You have a list of full names in one column and want to separate them.
Steps:
Type the first name in the adjacent column.
Press Ctrl + E (or go to Data → Flash Fill).
Excel fills the rest automatically.
Example:
Full Name | First Name | Last Name |
Gaurav Sharma | Gaurav | Sharma |
Priya Mehta | Priya | Mehta |
Arjun Kapoor | Arjun | Kapoor |
📧 2. Extracting Usernames from Email Addresses
Scenario: You want to pull out the username part before the “@” in email addresses.
Steps:
Type the first username manually.
Use Flash Fill to complete the column.
Example:
Email Address | Username |
gaurav.sharma | |
priya.mehta | |
arjun.kapoor |
📞 3. Formatting Phone Numbers
Scenario: You have raw phone numbers and want them in a consistent format.
Steps:
Type the desired format for the first entry (e.g., (+91) 98765-43210).
Apply Flash Fill.
Example:
Raw Number | Formatted Number |
9876543210 | (+91) 98765-43210 |
9123456789 | (+91) 91234-56789 |
📅 4. Extracting Dates or Years
Scenario: You have full dates but only need the year.
Steps:
Type the year for the first entry.
Use Flash Fill.
Example:
Date | Year |
25-Apr-2026 | 2026 |
14-Feb-2025 | 2025 |
01-Jan-2024 | 2024 |
🎨 5. Combining First and Last Names
Scenario: You have separate columns for first and last names and want to combine them into a full name.
Steps:
Type the full name for the first entry.
Use Flash Fill to complete the column.
Example:
First Name | Last Name | Full Name |
Gaurav | Sharma | Gaurav Sharma |
Priya | Mehta | Priya Mehta |
Arjun | Kapoor | Arjun Kapoor |
🔍 6. Extracting Domain Names from URLs
Scenario: You want to extract the domain name from a list of URLs.
Steps:
Type the domain name for the first URL.
Use Flash Fill to complete the column.
Example:
📝 7. Extracting Area Codes from Phone Numbers
Scenario: You want to extract the area code from phone numbers.
Steps:
Type the area code for the first entry.
Use Flash Fill.
Example:
Phone Number | Area Code |
(123) 456-7890 | 123 |
(987) 654-3210 | 987 |
(555) 123-4567 | 555 |
📝 8. Extracting Initials
Scenario: You want to get the initials from full names.
Steps:
Type the initials for the first name.
Use Flash Fill.
Example:
Full Name | Initials |
Gaurav Sharma | GS |
Priya Mehta | PM |
Arjun Kapoor | AK |
📝 9. Formatting Social Security Numbers
Scenario: You have raw SSN numbers and want to format them.
Steps:
Type the formatted SSN for the first entry (e.g., 123-45-6789).
Use Flash Fill.
Example:
Raw SSN | Formatted SSN |
123456789 | 123-45-6789 |
987654321 | 987-65-4321 |
555667777 | 555-66-7777 |
📝 10. Extracting File Extensions
Scenario: You want to extract file extensions from filenames.
Steps:
Type the extension for the first file.
Use Flash Fill.
Example:
Filename | Extension |
report.pdf | |
photo.jpg | jpg |
data.xlsx | xlsx |
📝 11. Creating Custom IDs
Scenario: You want to create custom IDs by combining parts of other columns.
Steps:
Type the custom ID for the first entry.
Use Flash Fill.
Example:
First Name | Last Name | Birth Year | Custom ID |
Gaurav | Sharma | 1990 | GSH1990 |
Priya | Mehta | 1985 | PME1985 |
Arjun | Kapoor | 1992 | AKA1992 |
📝 12. Extracting Street Names from Addresses
Scenario: You want to extract street names from full addresses.
Steps:
Type the street name for the first address.
Use Flash Fill.
Example:
Address | Street Name |
123 Main St, Springfield | Main St |
456 Oak Ave, Shelbyville | Oak Ave |
789 Pine Rd, Capital City | Pine Rd |
📝 13. Extracting Product Codes
Scenario: You want to extract product codes from product descriptions.
Steps:
Type the product code for the first description.
Use Flash Fill.
Example:
Product Description | Product Code |
Widget A - Code123 | Code123 |
Gadget B - Code456 | Code456 |
Thingamajig - Code789 | Code789 |
📝 14. Extracting Invoice Numbers
Scenario: You want to extract invoice numbers from invoice descriptions.
Steps:
Type the invoice number for the first entry.
Use Flash Fill.
Example:
📝 15. Extracting Currency Amounts
Scenario: You want to extract currency amounts from text.
Steps:
Type the amount for the first entry.
Use Flash Fill.
Example:
Text | Amount |
$100.00 payment received | 100.00 |
€250.50 invoice | 250.50 |
£75.25 refund | 75.25 |
🎨 Visual Example
Here’s a simple illustration of Flash Fill in action:
Comments