Download the free practice workbook 👉 HERE and follow along.
What is Flash Fill in Excel?
Flash Fill is a feature in Excel that automatically completes your data when it detects a pattern. It’s great for tasks like splitting text, combining first and last names or formatting phone numbers.
How to Enable Flash Fill
Flash Fill is usually on by default. If it’s not working, here’s how to enable it:
- Click on File > Options.
- Select Advanced in the left menu.
- Make sure “Automatically Flash Fill” is checked.
- Click OK and restart Excel.
How to Use Excel Flash Fill
Splitting Text
Got a list where first and last names are in one cell? Here’s how to split them using Flash Fill.
Type the First Name
- Select the cell to the right of the first name.
- Type the first name and press Enter.
- Use the Flash Fill shortcut Ctrl + E to fill in the rest of the first names.
Type the Last Name
- Select the first cell to the right of the newly created first names list.
- Type the last name and press Enter.
- Press Ctrl + E to fill in the rest of the last names.
💡 Think of it this way: type a version of the data the way you want it to appear. Then, use the Flash Fill shortcut Ctrl + E to let Excel do the rest..
How to Concatenate Text
Type the Full Name
- In the cell where you want the full name to appear, type the first full name (e.g., “John Doe”).
- Press Enter.
Use Flash Fill
- Start typing the next full name in the following cell. Excel will detect the pattern and show a preview.
- Press Ctrl + E to fill in the rest of the full names automatically.
Creating Email Addresses
Need to generate email addresses from a list of names? Excel’s Flash Fill can help you do it quickly and easily.
Type the First Email Address
- In the cell where you want the email address to appear, type the first email address (e.g., “john.doe@example.com”).
- Press Enter.
Use Flash Fill
- Start typing the next email address. Excel will detect the pattern and show a preview.
- Press Ctrl + E to fill in the rest of the email addresses automatically.
❗Flash Fill does not check for duplicate email addresses. You will need to manually adjust any duplicates.
Changing Case
Have a list of names in all lower case letters and want to capitalize the first letter of each name? Excel’s Flash Fill can help you do this quickly.
Type the Name in Proper Case
- In the cell where you want the corrected name to appear, type the first name with the first letter capitalized (e.g., “John”).
- Press Enter.
Use Flash Fill
- Start typing the next name in the proper case format. Excel will detect the pattern and show a preview.
- Press Ctrl + E to fill in the rest of the names with the first letter capitalized.
For more tips on changing the case of text in Excel, read our detailed guide here. Learn easy ways to make your text look exactly how you want!
Creating Initials in Excel
Want to generate a list of initials from a list of full names? Excel’s Flash Fill can make this task simple.
Type the Initials
- In the cell where you want the initials to appear, type the initials of the first name (e.g., “J.D.” for John Doe).
- Press Enter.
Use Flash Fill
- Start typing the initials for the next name. Excel will detect the pattern and show a preview.
- Press Ctrl + E to fill in the rest of the initials automatically.
Extracting Years, Months, and Days
Need to extract the year, month, or day from a date in Excel? Flash Fill can help you do it quickly.
Extracting the Year
- In the cell where you want the year to appear, type the year of the first date (e.g., “2023”).
- Press Enter.
- Press Ctrl + E to fill in the rest of the years.
Extracting the Month
- Type the month of the first date (e.g., “07” for July) in the corresponding cell.
- Press Enter.
- Press Ctrl + E to fill in the rest of the months.
Extracting the Day
- Type the day of the first date (e.g., “15”) in the corresponding cell.
- Press Enter.
- Press Ctrl + E to fill in the rest of the days.
💡 If Flash Fill gets confused, such as with dates like “9/9”, try this:
- Choose a different row where the day and month are not the same.
- Type the value and press Enter.
- Press Ctrl + E to fill the list in both directions.
Sometimes, Flash Fill needs a clear example that avoids confusion. This might take some trial and error.
Limitations of Flash Fill in Excel
While Flash Fill in Excel is a powerful tool for automating repetitive tasks, it has some limitations that you should be aware of:
Not Dynamic and Requires Manual Refresh
Flash Fill is not a dynamic tool. Once you apply Flash Fill, the results are static. If the original data changes, you will need to run Flash Fill again to update the results. This manual refresh can be cumbersome and prone to errors, especially when working with large datasets that frequently change.
Placement Restriction
The new list generated by Flash Fill must be on the same rows and directly to the left or right of the source column. This restriction can limit how you organize and structure your data, potentially leading to less flexible spreadsheet designs.
Use Power Query for More Flexibility
For more flexible and dynamic data transformations, consider using Power Query. Power Query allows you to create robust data transformation processes that automatically update when the source data changes. This ensures your data is always current and reduces the need for manual updates.
For more on how to use Power Query for text transformation, read our detailed guide here. Learn how Power Query can save you time and enhance your data management skills.
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master Flash Fill Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
