What Flash Fill does and when to use it

Flash Fill is a feature in Excel that watches what you type in a column, guesses the pattern you are following, and offers to fill the rest of the column the same way. You type one or two examples, Excel suggests the rest, and you press Enter to accept. It works for reformatting names, extracting parts of text, combining columns, and splitting data that should not be in the same cell.

Flash Fill saves time when you have a repetitive text task — but only if the pattern is consistent enough for Excel to recognize it. It works best when you are doing something straightforward like turning "Smith, John" into "John Smith" or pulling domain names out of email addresses. It struggles with irregular data, abbreviations that do not follow rules, or patterns that depend on context.

The feature lives in Excel for Windows (2013 and later) and Excel for Mac (2016 and later). It does not exist in Google Sheets or older versions of Excel. If you are using a web browser version of Excel, Flash Fill may not be available.

Key Takeaways

  • Flash Fill works by example: you type the result you want in the first cell or two, then Excel suggests filling the rest of the column the same way.
  • You trigger Flash Fill by typing your first example, pressing Enter, typing a second example, then pressing Ctrl+E (Windows) or Cmd+E (Mac), or by going to Data > Flash Fill in the menu.
  • Flash Fill works reliably for reformatting text, extracting parts of data, and combining columns, but fails on irregular patterns or data that needs context to understand.
  • If Flash Fill does not suggest what you want, you can type more examples to make the pattern clearer, or use a formula instead.
  • Always check the results before accepting them, because Flash Fill sometimes misreads the pattern, especially with names or abbreviations.

How to start Flash Fill and give it examples

Open the spreadsheet and click the cell in the column where you want the filled data to appear. Type the first result exactly as you want it. Press Enter to move to the next cell down.

Type the second example of the same pattern. This second example is what teaches Excel what you are doing. Press Enter again. Now select both cells you just filled (click the first one, hold Shift, click the second one).

On Windows, press Ctrl+E. On Mac, press Cmd+E. Excel will scan the rest of the column and suggest filling it the same way. A preview will appear showing what the filled cells would look like. If the preview looks right, press Enter to accept it. If it does not look right, press Escape and try again with a different second example or more examples.

Using the menu instead of the keyboard shortcut

If the keyboard shortcut does not work or you prefer the menu, you can reach Flash Fill through the ribbon. Click the Data tab at the top. Look for Flash Fill in the Data Tools group (it may say "Flash Fill" or show an icon that looks like a lightning bolt or wand). Click it.

The menu route works the same way as the keyboard shortcut — Excel will preview the fill and ask you to confirm. Some versions of Excel hide Flash Fill under a dropdown menu called "Flash" or group it with other fill options. If you cannot find it, the keyboard shortcut is usually faster.

What patterns Flash Fill handles well

Flash Fill is reliable when the transformation is consistent and rule-based. If you have a column of full names and want to extract just the first name, type "John" for "John Smith" and "Maria" for "Maria Garcia", then Flash Fill will usually get the rest right. The same goes for extracting last names, pulling domain names from email addresses, or turning "2024-01-15" into "January 15, 2024".

Combining two columns also works well. If you have a first name column and a last name column and want to create a full name column, type "John Smith" in the first result cell, "Maria Garcia" in the second, then Flash Fill will combine the rest. Removing extra spaces, fixing capitalization, and reformatting phone numbers or dates are also tasks Flash Fill handles reliably.

When Flash Fill fails and what to do instead

Flash Fill struggles when the pattern is not consistent or when it depends on context. If you have a list of names where some are "FirstName LastName" and others are "LastName, FirstName", Flash Fill may not recognize the pattern. If you are abbreviating company names and the abbreviation depends on what the company does, Flash Fill will not understand that logic.

When Flash Fill does not work, try typing a third or fourth example to make the pattern clearer. Sometimes Excel needs more data to recognize what you are doing. If that still does not work, use a formula instead. A formula like =LEFT(A1, FIND(" ", A1)-1) will extract the first name reliably, even if Flash Fill could not figure it out. Formulas take longer to set up but work on any pattern you can describe in logic.

Checking your results before you commit

Always look at the preview Flash Fill shows you before pressing Enter. Scroll through the suggested results and spot-check a few cells, especially near the beginning and end of the column. Flash Fill sometimes misreads the pattern — it might extract the wrong part of a name, or combine columns in the wrong order.

If you see even one cell that looks wrong, press Escape and do not accept the fill. Type another example that makes the pattern clearer, or switch to a formula. It is much faster to catch the mistake before Flash Fill fills 500 rows than to fix 500 wrong cells afterward.

Flash Fill versus formulas: which one to use

Flash Fill is faster to set up when the pattern is straightforward and consistent. You type two examples and you are done. A formula takes more time to write and test, but it works on any pattern you can describe in logic, and it updates automatically if the source data changes.

Use Flash Fill when you are doing a one-time cleanup of a small dataset and the pattern is straightforward. Use a formula when the pattern is complex, when you need the result to update if the source data changes, or when Flash Fill does not recognize the pattern. You can also use Flash Fill to create the results you want, then copy those cells and paste them as values if you do not need the formula to update anymore.

Frequently Asked Questions

Why is Flash Fill not showing up when I press Ctrl+E?

Flash Fill only works in Excel for Windows (2013 and later) and Excel for Mac (2016 and later). It is not available in Google Sheets, older versions of Excel, or the web version of Excel. If you are using one of those, you will need to use a formula instead. On Windows, make sure you have typed at least two examples and selected both cells before pressing Ctrl+E.

Can I use Flash Fill on data that has some empty cells or missing values?

Flash Fill will skip empty cells and only fill the cells where it can see a pattern in the source data. If your source column has gaps, Flash Fill may still work, but it is more likely to misread the pattern. Check the preview carefully before accepting the fill.

What if Flash Fill fills the column but gets some cells wrong?

Press Ctrl+Z to undo the fill. You can then type more examples to make the pattern clearer, or switch to a formula. Do not try to fix individual cells by hand — it is faster to undo and redo the fill correctly than to edit cells one at a time.

Does Flash Fill work on numbers or only on text?

Flash Fill works primarily on text. It can reformat numbers (like turning 2024-01-15 into January 15), but it is not designed for math or calculations. For number patterns that require logic, use a formula instead.

Can I undo Flash Fill if I change my mind?

Yes. Press Ctrl+Z when ready after accepting the fill, and Excel will remove it. If you have done other work since the fill, you may need to press Ctrl+Z multiple times to get back to before the fill happened.