Flash Fill in Excel (Ctrl+E) With Examples

Flash Fill in Excel fills a whole column from an example you type. You show Excel the result you want for the first row, press Ctrl+E, and it copies the pattern down: first names from full names, emails built from names, phone numbers in one format. This guide covers how to use it, six practical examples, and when a formula is the better choice.

What is Flash Fill in Excel?#

Flash Fill is a tool that spots a pattern in the examples you type and applies it to the rest of the column. You don't write a formula; you show Excel the answer for one or two rows and it works out the rule.

It is fast for one-off clean-up jobs, such as preparing an imported list. The trade-off is that Flash Fill writes plain values. If the source data changes later, the Flash Fill column does not update.

Flash Fill is available in Excel 2013 and later on Windows, Microsoft 365, and current versions of Excel for Mac.

How to use Flash Fill step by step#

Here is a list of full names in column A:

1. Click B2, right next to the data, and type the result you want: Priya.
2. Press Enter to move to B3.
3. Press Ctrl+E. On a Mac, or if you prefer the ribbon, go to Data > Flash Fill. You can also use Home > Fill > Flash Fill.

Excel fills B3:B5 with Marcus, Lena and Tom. If the results are wrong, type a second example in B3 and run Flash Fill again; two examples make the pattern clearer.

Automatic Flash Fill#

Sometimes you don't need the shortcut. As you type the second example, Excel may show a grey preview of the rest of the column. Press Enter to accept it. On Windows, this preview is controlled by File > Options > Advanced > Automatically Flash Fill.

Flash Fill examples#

Each example below starts from one typed result in the first row.

Combine two columns#

Flash Fill can also join columns. With first names in A and last names in B, type Priya Sharma in C2 and press Ctrl+E. For a version that updates, use the & operator or TEXTJOIN, covered in our guide on how to combine text in Excel.

Why Flash Fill is not working#

If Ctrl+E does nothing or gives strange results, check these points:

• The example is not next to the data. Flash Fill reads the columns beside the one you're filling. A blank column in between can stop it.
• There are not enough examples. If the pattern is unclear, type a second or third example, then press Ctrl+E again.
• The data is inconsistent. A row with a middle name, an extra space or a missing part breaks the pattern. Excel guesses, and the guess may be wrong.

After any Flash Fill, scan the results. A Flash Fill Options button appears next to the filled cells; it lets you undo the fill, accept it or select the cells that changed.

Flash Fill vs formulas: which should you use?#

Use Flash Fill for one-off tasks on data that won't change. Use a formula when the source data will be updated, or when other people need to see how the result was made.

For example, to get first names with a formula that works in every version of Excel:

=LEFT(A2,FIND(" ",A2)-1)

FIND returns the position of the space (6 in "Priya Sharma"), and LEFT takes the first 5 characters. In Excel 365 or Excel for the web, =TEXTBEFORE(A2," ") does the same job more simply; our guide to TEXTBEFORE and TEXTAFTER explains the options.

For splitting full names into separate columns in one step, Text to Columns is another option. And if your names include middle names or titles, see how to split first and last names in Excel for formulas that handle them.

Tips for reliable Flash Fill results#

• Clean the data first. Remove extra spaces with TRIM so every row follows the same pattern.
• Give an example from a tricky row. If some names have middle initials, use one of those as your second example.
• Keep the original column. Flash Fill doesn't change the source, so you can always compare.
• Check the row count. Make sure the last row was filled; a blank cell in the source can stop Flash Fill early.

Undo a Flash Fill#

If the results are wrong, press Ctrl+Z (Mac: ⌘Z) straight away to remove them, then add a better example and try again. Flash Fill never changes the source column, so undoing it is always safe.

Because Flash Fill writes values, a filled column also won't break if you later delete the source column. That makes it handy when you need to send someone a clean list without the raw data. With a formula, you'd first have to paste the results as values.

Practise it#

Try the formula version in the exercise on this page, then practise splitting text at a delimiter with TEXTBEFORE and TEXTAFTER in a live spreadsheet. To build full names, emails and labels with formulas that update, try the joining text lesson.