How to Use Flash Fill in Excel to Auto Fill Data (Step by Step)

Working with large tables in Excel often requires data manipulation, such as splitting names, addresses, or combining values from different columns. Fortunately, Excel offers a powerful data autofill feature called Flash Fill.

Recommended Resources

• Official Microsoft documentation for Flash Fill
• https://accdox.com/shkmw: AccDox URL
• How to enable Flash Fill manually

What is Flash Fill

Flash Fill (Sample Fill) is a feature in Excel that automatically fills in data when it detects a repeating pattern in the entered values. It works best when you manually enter the first value, which serves as a template for the rest.

Examples of use:

• Separate a full name into separate columns (first name, last name, last name);

• Merge information from multiple columns;

• Converting data formats (e.g. phone numbers);

• Extracting a piece of text (e.g., domains from email addresses)

Important: Flash Fill is only available in Excel 2013 and later versions.

Example:Separating the three column names

Assume that in column A you have a list of people's full names: first name, last name and last name - for example:

Ivan Asenov Yordanov

The goal is to split this content into three separate columns:

• column B - first name (Ivan);
• column C - surname (Asenova);
• column D - surname (Yordanov).

Step 1: Retrieve the proper name

Column B - first name;

1. Copy the entire first line from column A into column B.Example: Ivan Asenov Yordanov;
2. In the copied row of column B, delete the first and last name. Example: leave only: Ivan;
3. Highlight the first row and go to Fill and select Flash Fill (Press Ctrl+E or go to the Home > Fill > Flash Fill menu).
4. Excel will automatically fill in the remaining rows following the pattern you set.

Step 2: Retrieve the last name

Column C - the surname;

1. Copy the entire first line from column A into column C again. Example: Ivan Asenov Yordanov;
2. In the copied line of column C, delete the first and last name. Example: leave only: Asenov;
3. Highlight the first row of Column C again from Fill select Flash Fill. (Press Ctrl + E or go to the Home > Fill > Flash Fill menu).

Step 3: Extract the last name

Column D - last name;

1. As in step 1 of steps 1 and 2 , copy the entire first row from column A into column D. Example: Ivan Asenov Yordanov;
2. In the copied line of column D, delete the first name and last name. Example: Yordanov;
3. Activate Flash Fill in the same way.

The result will be an automatic separation of the names into separate columns.

Where is Flash Fill located in Excel?

If Flash Fill is not automatically activated, check to see if it is turned on in the Excel settings:

Short answer: top right of the Home menu. I haven't seen it elsewhere.If it's not there from the Data menu:Flash Fill

The Fill button is on the Home menu, under the AutoSum button /On Excel the most used function/!

The Flash Fill button is under Fill!

Note: Flash Fill is only available in Excel 2013 and newer. If Flash Fill does not generate a preview or cannot find a pattern or is not turned on.

You can go to Data> Flash Fill to run it manually, or press Ctrl+E.

If it's still not on, to turn on Flash Fill, go to:

• Go to File > Options > Advanced

• Under Editing Options find the Automatic Flash Fill

• Check and confirm with OK

Bonus tip: Remove HTML tags from a column

If you have a column with HTML tags (e.g. links) and want to remove them:

1. Select the column

2. Press Ctrl + H (Search and replace)

3. In the Find what field, type: <*>

4. Leave the "Replace with" field blank or enter a space

5. Click Replace All or Replace (optional)

This will delete all HTML tags using what are called wildcard characters (in this case "<> "* means "everything between < and >").

Conclusion

Flash Fill is an extremely useful feature that can save you hours of manual filling. With its help, you will easily split, combine or format text data in Excel without writing complex formulas. Combined with other tools such as Find & Replace, Excel becomes a powerful information processing tool.

Test the functions in a sample file and apply them to your work for greater efficiency.