How to use Flash Fill in Excel – 2024

How to use Flash Fill in Excel
EasyExcel
7 Min Read

What is Flash Fill in Excel?

Flash Fill in excel is a feature 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 Enable Flash Fill

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.
How to Use Excel Flash Fill

💡 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.
How to Concatenate Text

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., “[email protected]”).
  • 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.
Changing Case in excel flash cell

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.
Creating Initials in Excel

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.
Extracting Years Months and 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.

for more information follow EasyExcel

Our Channel EasyExcel

Share This Article
Leave a Comment