Flash Fill in Excel: How to Use It With Simple Examples

Have you ever had a list of names in Excel and wanted to change the way they are displayed?

For example, you have:

• John Smith
• David Miller
• Sarah Wilson

But you want to separate the first name and last name into different columns.

You could use formulas such as `LEFT`, `RIGHT`, or `TEXTBEFORE`.

But Excel has an easier feature for this.

It is called Flash Fill.

Flash Fill looks at the example you provide and tries to understand the pattern. It then fills the rest of the cells for you.

In this article, we’ll see how Flash Fill works, how to use it, some simple examples, and what to do if Flash Fill is not working.

What Is Flash Fill in Excel?

Flash Fill is an Excel feature that automatically fills data based on a pattern.

You give Excel an example of the result you want.

Excel looks at that example and tries to find the same pattern in the other rows.

How to Use Flash Fill in Excel:

Let’s start with a simple example.

Suppose you have these names in column A:

Full Name	First Name
John Smith
David Miller
Sarah Wilson
Michael Brown

You want to extract the first name into column B.

Step 1: Enter the First Example

In B2, type:

John

Your worksheet will look like this:

Step 2: Move to the Next Cell

Click B3.

Excel may automatically show a preview of the remaining results.

If Excel does not show the preview, type the first letter of the expected result. 

For example, type D in B3.

Excel will automatically show a preview of the remaining results.

You may see:

Step 3: Accept the Result

If the preview is correct, press Enter.

Excel fills the remaining cells.

That’s it.

You don’t need to write a formula.

Flash Fill Shortcut in Excel:

There is a quick keyboard shortcut for Flash Fill.

Press:

Ctrl + E

For example:

1. Enter your first example.

2. Move to the next cell.

3. Press Ctrl + E.

Excel will try to recognize the pattern and fill the remaining cells.

Use Flash Fill from the Data Tab:

You can also run Flash Fill from the Excel ribbon.

1. Enter your first example.

2. Select the cells where you want the results.

3. Go to the Data tab.

4. Click Flash Fill.

Quick Tip:

If Excel does not automatically show the Flash Fill preview, try Ctrl + E.

This is one of the easiest ways to use Flash Fill.

Flash Fill Example: Separate First and Last Names

Here You want to create separate columns for the first and last names.

First Names we have already created, now

Now create another column.

In C2, type:

Smith

Then use Flash Fill.

Excel can fill:

This can save you a lot of time when working with a long list.

Flash Fill Example: Create Email Addresses

Flash Fill can also help you create email addresses.

You want to create an email address in column C.

In D2, type:

john.smith@example.com

Then use Flash Fill.

Excel may recognize the pattern and fill the remaining rows.

This is much quicker than creating each email address manually.

Flash Fill Example: Combine Data

Flash Fill can also combine information from different columns.

Suppose you have:

First Name	Last Name
John	Smith
David	Miller
Sarah	Wilson
Michael	Brown

You want a full name in column C.

In C2, type:

John Smith

Then use Flash Fill.

Excel can fill the remaining rows

You don’t have to create a formula such as:

=A2&” “&B2

Now Flash Fill can recognize the pattern from your example.

Flash Fill Example: Change the Format of Data

Flash Fill can also help when you want to change the way data is displayed.

Suppose you have some phone numbers and You want them to look like this:

98765-43210

Enter the first example in the next column:

Then use Flash Fill.

Excel may recognize the pattern and apply it to the remaining numbers.

This can be useful when you need to clean up a large amount of data.

How to Turn On Flash Fill in Excel:

Flash Fill is normally enabled in Excel.

If Flash Fill is not working automatically, you can check the setting.

Go to:

File → Options → Advanced

Look for the option related to Automatically Flash Fill.

Make sure it is enabled.

You can also use Ctrl + E to trigger Flash Fill manually.

The Flash Fill vs. Formula:

Flash Fill and formulas can both be used to transform data, but they work differently.

Flash Fill	Formula
Recognizes a pattern	Uses a formula you define
No formula required	Requires a formula
Quick for one-time tasks	Useful when data keeps changing
Creates values	Creates calculated results
Does not automatically update when source data changes	Can update when source data changes

For example, if you are cleaning a list once, Flash Fill can be very convenient.
But if the source data will keep changing, a formula may be a better choice.

Flash Fill Does Not Create a Formula:

This is an important thing to remember.

When you use Flash Fill, Excel does not create a formula in the cells.

It fills the cells with the results.

For example, after using Flash Fill, B2 may contain:

John

It does not contain a formula like

=LEFT(C2,FIND(” “,C2)-1)

Because of this, the result does not automatically change when the original data changes.

So, keep this difference in mind.

When Should You Use Flash Fill?

Flash Fill is especially useful when you need to clean or change data quickly.

You can use it to:

• Separate first and last names
• Combine names
• Create email addresses
• Change text formats
• Extract parts of text
• Format phone numbers
• Create codes based on existing data

It is especially useful for one-time data-cleaning tasks.

When Should You Use a Formula Instead?

Flash Fill is not always the best option.

A formula may be better when your source data will change regularly.

For example:

=A2&” “&B2

If A2 or B2 changes, the result updates automatically.

With Flash Fill, the result is generally stored as a value.

So, a simple rule is:

One-time data cleaning → Flash Fill
Data that changes regularly → Formula

This is a simple rule, not a strict requirement.

Why Is Flash Fill Not Working?

Sometimes Flash Fill may not give you the result you expect.

Here are a few things you can check.

1. Give Excel a Clear Example

Flash Fill works by recognizing a pattern.

If your example is not clear, Excel may not understand what you want.

Try entering one or two examples that clearly show the pattern.

2. Use Ctrl + E

If Excel does not automatically show the Flash Fill preview, try:

Ctrl + E

This manually starts Flash Fill.

3. Check Flash Fill Settings

Go to:

File → Options → Advanced

Make sure Automatically Flash Fill is enabled.

4. Check Your Data

Flash Fill works best when the data follows a clear pattern.

For example, if some names contain middle names and others don’t, Excel may have difficulty understanding exactly what you want.

In that case, a formula or another Excel feature may give you more control.

Flash Fill vs. Text to Columns:

Flash Fill and Text to Columns can both help you split data, but they work differently.

Text to Columns is useful when your data follows a clear delimiter such as comma, tab, space, semicolon.

You can use the comma as the delimiter and split the data into separate columns.

Flash Fill is useful when you want Excel to recognize a pattern from an example.

A simple way to remember it is:

Clear delimiter → Text to Columns
Pattern-based change → Flash Fill

Flash Fill vs. Power Query:

Flash Fill is useful for quick, simple data-cleaning tasks.

For example, you may have a list of names and want to separate the first names.

For larger datasets or tasks that you need to repeat regularly, Power Query can be more suitable.

Flash Fill works directly in the worksheet and is quick to use.

Power Query is designed for importing, cleaning, and transforming data in a more structured way.

Frequently Asked Questions:

1. What is Flash Fill in Excel?

Flash Fill is an Excel feature that recognizes a pattern in your data and automatically fills the remaining cells.

2. What is the shortcut for Flash Fill?

The shortcut is:

Ctrl + E

3. Does Flash Fill use formulas?

No.

Flash Fill fills the cells with values based on the pattern it identifies.

4. Does Flash Fill update automatically?

No.

Flash Fill does not behave like a formula.

If the original data changes later, the Flash Fill results do not automatically update.

5. Can Flash Fill separate first and last names?

Yes.

You can enter an example of the first name or last name, and Flash Fill can recognize the pattern.

6. Can Flash Fill combine two columns?

Yes.

For example, you can combine a first name and last name into a full name by entering the first example.

7. Why is Flash Fill not working?

Try pressing Ctrl + E, provide a clearer example, and check whether Automatically Flash Fill is enabled under Excel’s Advanced options.

8. Is Flash Fill available in all versions of Excel?

Flash Fill is available in modern versions of Excel, including Microsoft 365 and newer desktop versions.

Final Thoughts:

Flash Fill is one of those Excel features that can save you a lot of manual work.

You give Excel an example, and Excel tries to understand the pattern and fill the remaining cells.

You can use it to:

• Separate names
• Combine data
• Create email addresses
• Change text formats
• Clean up data

And the shortcut is easy to remember:

Ctrl + E

Just remember one important thing:

Because there is no formula in the result cell, Excel does not automatically update that result when the source data changes.

So, if your source data will change regularly, a formula may be a better choice.

For quick, one-time data cleaning, Flash Fill can be a very useful Excel feature.