EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tools Excel Flash Fill
 

Excel Flash Fill

Shamli Desai
Article byShamli Desai
Madhuri Thakur
Reviewed byMadhuri Thakur

What is Flash Fill in Excel?

Flash Fill in Excel is a feature you can use to automatically format a dataset by simply providing an example of how you want the data to appear.

For example, let’s say you have a column of dates in the format “dd/mm/yyyy”. If you start typing the year in a new column, Flash Fill will recognize this pattern and automatically fill in the rest of the years for you in the same format.

Flash Fill in Excel

 

 

It is a very useful feature that simplifies data manipulation tasks and makes it easier to work with large datasets. We can use flash fill instead of using complex formulas or multiple functions.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Excel Flash Fill Shortcut

Shortcut for Windows: “Ctrl + E”
Shortcut for Mac: “Cmd + E”
Table of Contents
  • What is Flash Fill in Excel?
    • Flash Fill Shortcut
    • How to Enable/Disable Flash Fill in Excel?
    • How to Use It?
    • Examples (Single and Multiple Columns)
    • What to do if Excel Flash Fill is Not Working?
    • What is Flash Fill vs. Autofill in Excel?

How to Enable/Disable Flash Fill in Excel?

Steps to “Turn ON” Flash Fill

1. Open your Microsoft Excel file

2. Go to the “File” menu (located at the top left corner) and click on it.

file

3. In the menu that appears, click on “Options.” It will open the Excel Options dialog box.

option

4. In the Excel Options dialog box, click on the “Advanced” tab on the left side.

5. Check the “Automatically Flash Fill” option

6. Click “OK”

Flash Fill in Excel-advance

Steps to “Turn OFF” Flash Fill

To disable Flash Fill, follow steps 1 to 4 from the previous instructions, and in step 5, uncheck the “Automatically Flash Fill” checkbox.

How to do it

How to Use Flash Fill in Excel?

Let’s understand with an example how to use Flash Fill in Excel. Follow these steps.

1. Enter the data in the first column.

Flash Fill in Excel-Example 2

2. In the second column (e.g., cell B2), provide an example of how you want the data to appear. For instance, we want “Daisy_Clooney” to appear as “Daisy Clooney“, so we have added it in cell B2.

example 2-2

3. To initiate Flash Fill, press “Ctrl + E” (Windows) or “Cmd + E” (Mac). Excel will automatically fill the cells based on the pattern you provided.

Note: You can apply Flash Fill from the “Data” tab on the Excel ribbon. To do this, locate the “Data Tools” group and then click on the “Flash Fill” button.

Flash Fill in Excel-shortcut

Excel Flash Fill Examples

You can download this Flash Fill Excel Template here – Flash Fill Excel Template

Example 1:

Suppose you have an alphanumeric list of codes and want to extract numbers from them to create a new column containing only the numbers. Here’s how to do it:

Step 1: Enter the data in column 1.

given

Step 2: Insert a new column where you want the extracted numbers to appear. 

Flash Fill in Excel-add column

Step 3: Type the desired data in cells B2 and B3 to show the pattern you want.

Step 3

Step 4: Use Flash Fill

  • Press “Enter” after inputting the values in cells B2 and B3.
  • Go to the “Data” tab on the Excel ribbon.
  • In the “Data Tools” group, click “Flash Fill” or use the shortcut “Ctrl + E“.

Flash Fill in Excel-ribbon

Excel will automatically fill the remaining cells in Column B based on the pattern you set.

RESULT

Note: In some cases, Excel might not recognize the pattern correctly, or there might be discrepancies in the data. You can manually adjust individual cells if necessary.

Example 2: Extract City Name from Address

We have a list of addresses with city names mixed in, and we want to separate just the city names to make a new list. Here’s how you can do it using Flash Fill.

Given:

Flash Fill in Excel-eg 1 given

Solution:

1. Add a new column where you want to extract the city names.

Example 2.2

2. Manually type “Denver” in cell B2.

example 2.3

Result: Use the flash fill shortcut “Ctrl + E” to apply the same for the rest of the cells in Column B.

eg 1 result

Example 3: Extracting Acronyms from Full Forms

We have a list of words with their acronyms (short forms), like “Be Right Back (BRB)” and “Laugh Out Loud (LOL).” If you want to get only the acronyms (BRB and LOL) into a new column, here’s how you can do it.

Given:

eg 2 given

Solution:

1. Add a new column where you want to extract the acronyms

eg 2.2

2. Manually type “BRB” in cell B2.

example 2.4

Result: Use the flash fill shortcut to apply the same for the rest of the cells in Column B.

Flash Fill in Excel-EG 2 RESULT

Example 4: Use Flash Fill for Multiple Columns

You have a dataset containing employee information such as Employee ID, Department, and Full Name. Your task is to create an Email ID for each employee in the dataset using the information provided in the Department and Full Name columns.

Given:

eg 3 given

Solution:

1. Add the desired column.

eg 3.2

2. Add desired data to the first row of the Email ID column, i.e., [email protected]. So the input will be [email protected]

Flash Fill in Excel-eg 3.3

3. Click Ctrl+E to apply the same for the rest of the cells in Column D.

Result:

eg 3 result

What to do if Excel Flash Fill is Not Working?

When Excel’s Flash Fill feature isn’t functioning as expected, it may display an error message, as seen below.

ERROR

To resolve the issue, follow these steps:

  1. Check Flash Fill Settings: First, ensure that Flash Fill is enabled in Excel settings. Go to the “File” menu, select “Options,” then click on “Advanced.” Ensure the “Automatically Flash Fill” option is enabled.
  2. Use a Recognizable Pattern: The data you want to format must follow a recognizable pattern. Remember that Flash Fill works best when the pattern is consistent and distinguishable.
  3. Check Excel Version: Confirm that you are using a supported Excel version (2013 and later) since Flash Fill may not be available in older versions.

What is Flash Fill vs. Autofill in Excel?

Feature Flash Fill Autofill
Purpose Extract and format data based on patterns. Extend a series or copy values/formulas.
Activation Automatic based on detected patterns. Manual drag-and-fill or use of a fill handle.
How to use it? In the Excel ribbon, go to Data>Data tools>Flash Fill, or you can use Ctrl+E. Drag the fill handle in the bottom right corner of a selected cell to fill adjacent cells.
Functionality Automatically detects patterns and applies transformations without formulas. Repeats patterns, fill series, and copies data or formulas.
Usage Commonly used for data formatting tasks. Commonly used for filling series and duplicating data.
Customization Limited control over the transformation. Can be customized using different fill options, like fill series, copy cells, etc.
Data dependencies Doesn’t rely on formulas or existing data. Fills data based on existing values or formulas.

What are Flash Fill Limitations?

  1. Complex or inconsistent data may lead to inaccurate results.
  2. Non-English or non-standard characters might cause issues.
  3. Complex patterns can produce unexpected outcomes.
  4. It may overwrite existing data if not used carefully.
  5. It only works within a single column (Column A) and cannot fill data in multiple columns at once.
  6. Not available in versions older than Excel 2013.
  7. Skips cells with spaces or non-printable characters.

Recommended Articles

This EDUCBA article guides you on flash fill-in Excel. We explain how you can use this feature to transform data easily. We have added 4 examples along with a downloadable template. For more such articles, please check the recommendations below:

  1. Data Table in Excel
  2. What is Excel Combo Box
  3. Excel AutoFill
  4. Excel Fill Handle

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Flash Fill Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Flash Fill Excel Template

EDUCBA

डाउनलोड Flash Fill Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW