Flash Fill recognizes patterns in your data and fills the rest automatically

Flash Fill is a feature in Excel that watches what you type, spots the pattern, and offers to fill in the remaining cells the same way. You type one or two examples of what you want, Excel suggests the rest, and you press Enter to accept. It works on splitting names, combining columns, reformatting dates, extracting numbers from text, and other repetitive transformations that would otherwise take you minutes to do by hand.

Flash Fill is built into Excel 2013 and later on Windows, and Excel 2016 and later on Mac. It does not work in Google Sheets or older versions of Excel. The feature lives in the Data tab at the top of the screen, but most of the time you do not need to click anything — you just type your examples and Excel prompts you.

Key Takeaways

  • Flash Fill works by example: type what you want in the first cell or two, and Excel suggests filling the rest of the column the same way.
  • The feature recognizes patterns like splitting "John Smith" into separate first and last name columns, or reformatting "1/15/2024" to "January 15, 2024".
  • You can accept Flash Fill's suggestion by pressing Enter, or reject it and type a different example if the pattern is wrong.
  • Flash Fill works best when the pattern is consistent — if your data is messy or varies a lot, you may need to clean it first or use a formula instead.

When Flash Fill recognizes your pattern and makes a suggestion

After you type your first example in a new column and move to the second cell, Excel watches what you type next. If it sees a clear pattern, a faint suggestion appears in the cells below — the text is grayed out and ghosted, not yet part of your spreadsheet. This suggestion is Flash Fill's best guess at what the rest of the column should be.

The suggestion appears as you type your second example. You do not have to finish typing the second one — as soon as Excel is confident it understands the pattern, the ghosted text shows up. At that point, you can press Ctrl+E (Windows) or Cmd+E (Mac) to accept the suggestion, or you can keep typing and ignore it.

If no suggestion appears after you type two examples, the pattern may be too inconsistent for Flash Fill to recognize, or the feature may not be the right tool for that task. In that case, a formula or manual entry might work better.

How to accept or reject Flash Fill's suggestion

When you see the ghosted suggestion, press Ctrl+E (Windows) or Cmd+E (Mac) to fill the entire column at once. Excel replaces the ghosted text with real data, and you move on. If you press Ctrl+E and nothing happens, the suggestion may have disappeared — click on the column header and go to the Data tab, then click Flash Fill.

If the suggestion is wrong, just keep typing your second example differently, or press Delete to clear the cell and type a new one. Flash Fill will adjust its guess based on what you type. If you reject the suggestion and want to try again later, you can always select the column and use the Data tab to trigger Flash Fill manually.

Common patterns Flash Fill handles well

Flash Fill works reliably on splitting and combining text. If you have a column of full names and want to split them into first and last names, type the first name from the first row in a new column, then type the first name from the second row. Flash Fill usually recognizes the pattern and suggests filling the rest. The same works for extracting last names, or combining a first-name column and a last-name column into one full-name column.

Flash Fill also handles date reformatting. If your dates are in the format "1/15/2024" and you want them as "January 15, 2024", type one example of the new format and Flash Fill will suggest the rest. It can also extract numbers from text — for example, pulling the area code out of a phone number like "(555) 123-4567" — as long as the pattern is consistent.

The feature struggles when your data is inconsistent. If some names are "John Smith" and others are "Smith, John", or if some dates are missing, Flash Fill may not recognize a single pattern. In those cases, you may need to clean the data first, or use a formula like LEFT, RIGHT, MID, or CONCATENATE instead.

When to use a formula instead of Flash Fill

Flash Fill is fast for one-off tasks, but formulas are better when you need the transformation to update automatically if the source data changes. If you split a name using Flash Fill and then the original name column is edited, the split names do not update — they are static text. A formula would update automatically.

Formulas are also more reliable when your data is messy or has exceptions. Flash Fill needs a clear, consistent pattern to work. If you have 500 rows and 10 of them are formatted differently, Flash Fill may fail or produce wrong results. A formula can handle those edge cases if you write it carefully.

Use Flash Fill when you are doing a one-time cleanup and speed matters more than automation. Use a formula when the data will change, or when the pattern is too irregular for Flash Fill to recognize.

How to trigger Flash Fill manually if it does not appear automatically

Sometimes Flash Fill does not suggest anything even though you have typed two clear examples. This can happen if the feature is turned off, or if Excel is not confident enough in the pattern. To trigger it manually, click on any cell in the column where you typed your examples, then go to the Data tab at the top of the screen. In the Data Tools section, click Flash Fill.

If Flash Fill is grayed out and you cannot click it, the feature may not be available in your version of Excel, or you may not have typed enough examples yet. Try typing a third example and then try the Data tab again. If it stays grayed out, a formula is probably your best option.

Frequently Asked Questions

Does Flash Fill work in Google Sheets?

No. Flash Fill is a Microsoft Excel feature only. Google Sheets has a similar tool called Smart Fill in some versions, but it works differently and is less reliable. For Google Sheets, use formulas like SPLIT, CONCATENATE, or REGEXEXTRACT instead.

Can I undo Flash Fill if it fills the column wrong?

Yes. Press Ctrl+Z (Windows) or Cmd+Z (Mac) when ready after accepting Flash Fill, and the column returns to the state before you pressed Ctrl+E. You can then type different examples and try again.

What if Flash Fill fills some cells correctly but not others?

Flash Fill sometimes gets the pattern partially right. If most cells are correct but a few are wrong, you can manually edit those cells after accepting the fill. Or reject the fill, type a third example that is different from the first two, and try again — more examples sometimes help Excel understand the pattern better.

Is Flash Fill faster than writing a formula?

For a one-time task with consistent data, Flash Fill is usually faster — you type two examples and press one key. For complex transformations or messy data, a formula takes longer to write but is more reliable and updates automatically if the source changes.

Can Flash Fill handle phone numbers or other formatted text?

Yes, if the format is consistent. If all your phone numbers are in the format "(555) 123-4567" and you want to extract just the area code, type "(555)" in the first new cell and Flash Fill will suggest filling the rest. But if some numbers are missing parentheses or use dashes instead, Flash Fill may fail.