How to Use Flash Fill in Excel: Save Time on Data Entry

While doing data entry and analysis, we have to spend a lot of time on copying, pasting, splitting, or re‑typing data. However, there is one Excel feature that can save us a lot of time and effort by spotting the patterns in our data entry and thereby auto completing the cells. This feature is called ‘Flash Fill in Excel’.

Flash fill was introduced in Excel 2013 and it was further improved in Microsoft 365. Tasks like separating first and last names, converting dates, reformatting phone numbers, or piecing together email addresses are so much easier to execute with the help of Flash Fill in Excel.

In this beginner to intermediate level guide, we will show you how to use Flash Fill, what are its limitations and we will also give you practice exercises to try.

1. What Is Flash Fill in Excel?

Flash fill operates by observing what you type in the nearby columns. It analyzes the pattern in your entries and then uses that pattern to fill the remaining cells. It is the auto completing mechanism that Excel deploys without using any formulas. Thus, after the Flash Fill writes the hard coded values, no formulas remain.

2. Enabling and Triggering Flash Fill in Excel

Usually, Flash Fill in Excel is turned on by default. However, this is how you can cross check before starting –

1. First Go to File › Options › Advanced.

2. Then go to Editing options and make sure “Automatically Flash Fill” is checked.

Now there are two ways of using it –

2.1 Automatic Flash Fill

When you are typing something in a cell, Excel may give auto complete suggestion if it notices a pattern in nearby cells. These suggestions are highlighted in grey and if you hit enter, Excel will fill those in the corresponding cell.

2.2 Manual Flash Fill (Ctrl + E)

We can also trigger Flash Fill manually if for some reason we are not getting the auto complete suggestions.

1. Type the desired result in the first cell of a new column.

2. Then, press Ctrl + E (or go to Data › Flash Fill).

It will become clearer when we look at below examples –

3. Core Examples of Flash Fill in Excel

Here are 4 practical scenarios where Flash Fill can be extremely handy –

3.1 Splitting Names into First and Last

1. Type “Maya” in B2 and press Enter.

2. Then, press Ctrl + E in B2. You will see that Excel has filled “Amir”, “Olivia” and “José” in below cells.

3. Similary, type “Sharma” in C2 and press Ctrl + E in C2 so that Flash fill can enter last names in below cells.

3.2 Combining Text to Form Email Addresses

Let’s try to create email ids in D column. Suppose the format for email ids is <first>.<last>@example.com in lowercase. Hence, in D2 just type the first email id –

1. maya.sharma@example.com is the email id we manually write in D2.

2. Then, press Ctrl + E. Flash Fill will recognize the pattern, it will add dots, turn everything to lowercases, and append @example.com. You will get amir.khan@example.com etc in below cells.

3.3 Reformatting Phone Numbers

Imagine you have a list of thousands of phone numbers. They are in the format – 9876543210, but you need in this format – (987) 654‑3210. This is how we can get this done –

1. Type (987) 654-3210 next to the first phone number.

2. Trigger Flash Fill. Excel will insert parentheses, space and dash for the remaining phone numbers.

3.4 Extracting Initials

The task is to extract two‑letter initials from full names.

1. For “Maya Sharma,” type MS.

2. Then hit Ctrl + E and as expected, Excel will fill “AK”, “OD”, “JF” and so on.

4. Advanced Tricks of Flash Fill in Excel

Flash fill in Excel can achieve much more than simple splits and joins. Let’s look at a few more complicated tasks –

4.1 Conditional Text (Add Titles)

Have a look at the below table. We have Gender in A column (M/F) and last names in B. We want to update Mr. for males and Ms. for females in C column.

Type “Ms. Dsouza” once in C2 and press Ctrl + E. Bingo, Excel will Flash Fill rest of the below columns by inferring the “Mr./Ms.” rule.

4.2 Fixing Capitalization

We have capitalization errors in a column having names. We want to correct that. For e.g., convert “jaY deeP” to proper case “Jay Deep”:

1. Type the correctly capitalized name in the next column.

2. Ctrl + E will convert the rest, even if each source cell is differently mangled.

4.3 Working with Dates

We have dates in a column but they are all formatted differently. Turn “2025‑07‑07” and all variations into “07‑Jul‑25”:

1. Fist enter the desired output once.

2. Trigger Flash Fill by CTRL+E.

Note: Flash Fill can store dates as text, but not as true date values. Thus, if you need date functions later, use formulas or Power Query.

5. Tips for Accuracy & Troubleshooting

6. Flash Fill vs. Formulas vs. Power Query

7. Practice Questions & Sample Data

Hope you have the clarity on Flash fill basics. It is time to practice yourself. Copy the following small dataset into Excel and try the tasks that follow:

Exercises

1. Initials & Dept Code

   • We need to create a code by combining the name and department. Format is two‑letter initials followed by a dash and the first three letters of the department (e.g., RM‑SAL).

2. Corporate Email

   • Produce emails in the format <first>.<last>@corp.co.in, all lowercase.

3. Formatted Phone

   • Convert all phone numbers in a standard format to +91 98765‑43210.

4. Service Anniversary Text

   • Generate friendly text such as “Rahul joined on 01-Nov-23”.

5. Title Case Departments

   • Ensure every department appears in Title Case (e.g., “Human Resources”).

We recommend to solve these with Flash Fill. However, if required, try to deploy formulas and Power Query.

8. Final Thoughts

One of those “hidden gem” features is Flash Fill in Excel, which is easy enough for novices to use but strong enough to wow seasoned analysts when they have a tight deadline. It is ideal for one-time transformations or rapid prototypes before you formalize your solution, but it cannot take the place of dynamic formulas or reliable ETL tools.

Ask yourself, “Could Flash Fill do this for me?” the next time you find yourself editing a lengthy list by hand. Find out by pressing Ctrl+E; you might not turn back.

Also check out our other articles on –

How to Create Interactive Dashboards in Excel Using Slicers

How to Import Data into Excel from Web, CSV and PDF

Excel to Google Sheets: How to Switch and Sync Data

Microsoft’s Guide on Flash Fill