EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Excel Flash Fill

Excel Flash Fill

Dikshita Jain
Article byDikshita Jain
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.

Start Your Free Excel Course

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

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., fulln[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
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests
 137+ Hours of HD Videos
36 Courses
13 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.9
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

Download Flash Fill Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Flash Fill Excel Template

EDUCBA

डाउनलोड Flash Fill Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more