Flash Fill in Excel: how it works

Flash Fill completes a column following an example you type yourself, such as the first name from a full name. You'll find it on the Data tab, or press Ctrl + E.

Try the first exercises for freeIn the lesson Cleaning up data with the ribbon

What does Flash Fill do?

With Flash Fill, you don't have to come up with a formula. You type what you want to see in the first cell of an empty column, and Excel recognises the pattern: from Lucy Walker in column A, it takes the first name, for all the rows below too.

It works for taking pieces of text, joining text or changing capitals, as long as there's a clear pattern. The result is ordinary values: if the source changes, you do it again. On Windows, the shortcut is Ctrl + E.

When do you use Flash Fill?

The first name from a column of full names.
Making an email address from a first name and a surname.
Turning names in capitals into normal writing, with a capital at the start.
Taking the digits out of a code, such as 2024 from UK-2024-115.

Flash Fill, step by step

This is how to go about it:

Make sure your data is in a column, and that the column next to it is empty.
In the first cell of the empty column, type what you want to see, such as the first name from the name next to it.
Press Enter, so that you're in the cell below.
Choose Data > Flash Fill, or press Ctrl + E. Excel fills in the rest of the column.
Check the result. If a cell is wrong, type a second example there and press Ctrl + E again.

Where it often goes wrong

Flash Fill guesses a pattern. Check the result, especially with double first names such as Mary Ann.
The result is ordinary values, not formulas. If the source changes, the column doesn't update.

Want to try it yourself?
Practise this in the lesson Cleaning up data with the ribbon. The first exercises are free.

Try the first exercises for free

Frequently asked questions

Does Flash Fill work on a Mac too?

Yes, through Data > Flash Fill or Home > Fill > Flash Fill. The shortcut Ctrl + E is for Windows.