How to Use Flash Fill in Excel

What this does

Flash Fill watches you type an example and infers the pattern from the columns to the left. It handles splitting, joining, reformatting, case changes and extraction — first names out of full names, area codes out of phone numbers, initials out of both — without a single formula. Its two defining limits follow from that: the result is STATIC text, so it does not update when the source changes, and the inference is a guess, which on messy data can be confidently wrong for rows you never look at. It needs the source columns to be adjacent to where you type, and it is Windows-and-Mac Excel only — Google Sheets has no equivalent. Where the data will change, or the rule must be auditable, a formula (TEXTBEFORE, TEXTAFTER, LEFT/RIGHT/MID) or Power Query is the safer instrument. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. Treat “use flash fill in excel” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That turns a data step that keeps your analysis trustworthy into a method you can reuse, explain, and defend when the workbook leaves your screen.

A worked example

A2 holds "Rebecca Chen". In B2 type "Rebecca" and press Enter; start typing "Mark" in B3 and Excel greys in the remaining first names. Ctrl+E accepts them. Do the same in C for last names. But if row 218 is "Dr. Ana María Ruiz-Delgado", Flash Fill will produce something for it, and it may not be the something you wanted — which is why you sort and scan the result before using it. For a one-off reshape of a column you are about to use and discard, Flash Fill is the fastest tool in Excel — provided you check what it guessed. If this is something you do often, add the command to the Quick Access Toolbar (right-click it on the ribbon and choose Add to Quick Access Toolbar) or learn its shortcut, so next time it is one click instead of a hunt through the menus.

In Google Sheets

This is a command rather than a formula, so there is no syntax to carry over. Google Sheets arranges its commands in menus across the top instead of a ribbon, names some of them differently and does not have every Excel command — look for the equivalent in the Sheets menus or its help before assuming the steps match. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Treat “use flash fill in excel” as a routine rather than a one-off. Once you know which setting or command controls it, the same few steps handle every workbook where it comes up in Excel, and you can explain them to a colleague in a sentence.

Common mistakes

• Treating the output as live — it is typed text, and it will not follow a change in the source column.
• Accepting the fill without scanning it; one unusual row shape is enough to produce plausible nonsense.
• Giving one example where the data has two shapes ("Last, First" mixed with "First Last") — supply an example of each.
• Leaving a blank column between the source and where you type, which stops Excel seeing the pattern.
• Using it on data that gets refreshed monthly, where a formula or a query does the job once instead of every month.

Frequently asked questions