We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)
Knowing how to use Flash Fill in Excel can help you save a ton of time when reformatting data in a new pattern.
This can include when changing the case of your text. For example, from lower case to all caps.
Or something a bit more complex like creating product numbers or generating employee email addresses from a list of first names and last names.
What is Flash Fill in Excel?
Not to be confused with Autofill, which detects series, like days of the week or sequence of numbers, Flash Fill detects patterns in formatting and replicates it in other cells.
3 Ways to Apply Flash Fill in Excel
Watch: How to Use Flash Fill in Excel – Tutorial
1. Apply Flash Fill as you type
To use Flash Fill as you type, create the formatting that you want Excel to replicate in the first row of your Flash Fill area. On occasion, you may have to do this in the second row too.
Press Enter. Once you begin typing in the next row, you’ll see your new formatting auto-populate. It’ll look a little like a drop-down list but grayed out.
Press Enter to populate the other cells.
Did you forget to press Enter? Not to worry! There are 2 other ways you can still use to apply Flash Fill.
2. Click on Flash Fill from your Ribbon
In your Ribbon, go to the Data tab / Data Tools section. You’ll see an icon for it there.
The Flash Fill icon has a small lightning bolt and looks like this:
Example of using Flash Fill to reformat a list of numbers
Let’s say you need to reformat a list of product numbers that all contain the same number of characters, to include a dash in the middle.
To the right of your list of numbers, type the first one as you would like all the others to look like.
Once you type the first example, you can click on Flash Fill to complete the rest of your list!
3. Use the Flash Fill Keyboard Shortcut: Ctrl+E
I think the Ctrl+E keyboard shortcut is the easiest way to use Flash Fill.
Simply type your first item how you’d like for all your data to be formatted, press Enter to move to the cell below it, then press Ctrl+E.
Example of using Flash Fill to create email addresses from a list of names
In this example, we need to create email addresses from a list of names. The first names are listed in one column. The last names are listed in a different column.
They’ll be formatted like this:
- Add a period between the first name and last name
- All the email addresses will use the same ending “@company.com”
After typing the first one, press Ctrl+E from your keyboard to populate the rest!
Tips to Use Flash Fill the Right Way
There are a few ways to apply Flash Fill. But, regardless of which option you use, there are 3 things that you always need to do:
1. The Flash Fill area needs to be in the column to the right and adjacent to the last column of your data.
For example, if the data that you want to apply Flash Fill to is in columns A, B, and C, the Flash Fill needs to be done in column D.
2. An example of the formatting that you want to replicate needs to be created in the first row of your Flash Fill column. This is what Excel will then copy to the cells below it.
3. Avoid blank rows in your data. Flash Fill will stop auto-populating at the first blank cell to its left.
Enable Flash Fill in Excel
If your Flash Fill isn’t working, it’s most likely because it’s not turned on.
To check that your Flash Fill is enabled:
- Go to your File menu
- Click on Options
- In your Excel Options, select Advanced
- Scroll to the Editing section
- Make sure that “Enable AutoComplete for cell values” AND “Automatically Flash Fill” are both checked off
Leave a Reply