How to Use Microsoft Excel Flash Fill to Automate Data Entry

If you have ever received a messy spreadsheet where an entire column is filled with full names (e.g., “John Smith”), but you actually need the first names in one column and the last names in another, manually retyping hundreds of names is a frustrating waste of time. You could use complex text-extraction formulas like =LEFT() and =RIGHT(), but these can be difficult to build and troubleshoot.

Instead, you can use one of Microsoft Excel’s most intelligent features: Flash Fill. This tool uses predictive machine learning to recognise the pattern of what you are trying to do. Once it understands your goal, it will instantly complete the rest of the column for you, requiring absolutely no formulas.

How to Use Flash Fill

To use Flash Fill, your data must be organised in adjacent columns without any completely blank columns separating them.

1. Imagine Column A contains a list of full names: “Sarah Jenkins”, “Michael Chang”, and “David O’Connor”.
2. Click on Cell B1 (directly next to “Sarah Jenkins”).
3. Type exactly what you want to extract: Sarah. Press Enter.
4. Click on Cell B2 (directly next to “Michael Chang”).
5. Begin typing Michael.

As soon as you type the first letter “M”, Excel will instantly recognise the pattern (you are extracting the first word from Column A). A grey, ghosted list of every other first name will automatically appear cascading down Column B.

6. Simply press Enter to accept the suggestion. Excel will instantly fill the entire column.

How to Trigger Flash Fill Manually

Sometimes, Excel’s machine learning needs a slightly stronger hint and will not automatically display the grey ghost text after just two examples.

If the automatic suggestion does not appear, you can force Excel to look for the pattern:

1. Type the first correct answer in Cell B1 and press Enter.
2. Type the second correct answer in Cell B2 and press Enter. (Providing two examples helps the AI understand the pattern better).
3. Click on the empty Cell B3.
4. Go to the Data tab on the top ribbon menu.
5. In the “Data Tools” group, click the Flash Fill button (the icon looks like a lightning bolt next to a spreadsheet grid).

Alternatively, you can use the keyboard shortcut: click on the empty Cell B3 and press Ctrl + E.

Advanced Flash Fill Uses

Flash Fill is not just for splitting text; it can also combine data, reformat text, and extract specific characters.

• Creating Email Addresses: If Column A has First Names and Column B has Last Names, type “[email protected]” in Column C. Flash Fill will instantly generate the correct email format for every other employee on the list.
• Formatting Phone Numbers: If you have a column of raw numbers like “07700900123”, type “(07700) 900-123” in the next column. Flash Fill will format the rest of the list to match.
• Fixing Capitalisation: If a database export gave you a column of names in all-lowercase (“david o’connor”), type “David O’Connor” in the next column. Flash Fill will fix the capitalisation for the entire dataset.