Check Out These 10 Great Examples using Flash Fill in Excel
by Avantix Learning Team | Updated September 20, 2023
Applies to: Microsoft® Excel® 2013, 2016, 2019, 2021 or 365 (Windows)
Flash Fill is a great tool that you can use to quickly clean or format data in your Excel workbooks. It can automatically extract, combine, clean and format data without using a formula. It does seem like magic. Here are 10 examples using Flash Fill in Excel.
In order to use Flash Fill, there must be a consistent pattern in the source data and you need to use Flash Fill in a column to the right of the data. Also, Flash Fill does not update so if the source data changes, you'll need to repeat the process.
Recommended article: Automatically Sum Rows and Columns with Excel's Awesome Quick Totals Tool
Do you want to learn more about Excel? Check out our virtual classroom or live classroom Excel courses >
Using Flash Fill
We're going to generate 10 Flash Fill examples and we'll be using the following steps:
- Enter the example data in a cell to the right of the original data.
- Press Enter to enter the example in the cell and move down one cell.
- Press Ctrl + E to Flash Fill.
For other ways to apply Flash Fill, check out the article How to Use Flash Fill in Excel (4 Ways with Shortcuts)
Example 1: Combine text and change case using Flash Fill (combine and format)
In the following worksheet, we entered the example in two different cases in C2 from the data in A2 and B2 and then used Flash Fill:
After Flash Fill:
Example 2: Combine partial text and change case using Flash Fill (combine and format)
In the following worksheet, we entered the example in capital letters in C2 based on data in A2 and B2 and then used Flash Fill:
After Flash Fill:
Example 3: Extract dates using Flash Fill (extract data)
In the following worksheet, we entered the example date data in B2, C2, D2 and E2 based on data in A2 and then used Flash Fill:
After Flash Fill:
Example 4: Extract numbers from text using Flash Fill (extact data)
In the following worksheet, we entered the example as the numbers only from A2 in B2 and then used Flash Fill:
After Flash Fill:
Example 5: Extract parts of numbers using Flash Fill (extract data)
In the following worksheet, we entered the example as only the first three numbers from A2 in B2 and then used Flash Fill:
After Flash Fill:
Example 6: Extract parts of numbers and apply formatting using Flash Fill (extract and format)
In the following worksheet, we entered the example currency symbol and then the first three numbers from A2 in B2 and then used Flash Fill:
After Flash Fill:
Example 7: Extract hours and minutes using Flash Fill (extract data)
In the following worksheet, we entered the example start hour in B2 and the minutes in C2 from time data in A2 and then used Flash Fill:
After Flash Fill:
Example 8: Extract text data using Flash Fill (extract data)
In the following worksheet, we entered the example company name from A2 in B2 and then the category in C2 (which originally appeared in square brackets at the end of the data in A2) and then used Flash Fill:
After Flash Fill:
Example 9: Combine text and numbers using Flash Fill (combine data)
In the following worksheet, we entered the example company prefix "AB" and then the number from A2 in B2 and then used Flash Fill:
After Flash Fill:
Example 10: Remove unnecessary spacing using Flash Fill (clean data)
In the following worksheet, we entered the example address from A2 in B2 without the extra spaces and then used Flash Fill:
After Flash Fill:
Flash Fill can do some amazing things. If Flash Fill doesn't seem to pick up the data correctly, try entering samples in 2 or 3 cells and then use Flash Fill.
Note: Names and addresses used in these samples were created for this article and are not intended to represent actual people, addresses, companies or organizations. Any similarities with actual people, addresses, companies or organizations is purely co-incidental.
This article was originally published on October 14, 2016 and has been updated for clarity and content.
Subscribe to get more articles like this one
Did you find this article helpful? If you would like to receive new articles, JOIN our email list.
More resources
How to Convert Text to Numbers in Excel (5 Ways)
How to Delete Blank Rows in Excel (5 Easy Ways with Shortcuts)
How to Replace Blank Cells with a Value from the Cell Above in Excel
How to Replace Blank Cells in Excel with Zeros (0), Dashes (-) or Other Values
How to Highlight Errors, Blanks and Duplicates in Microsoft Excel (Using Formulas)
Related courses
Microsoft Excel: Intermediate / Advanced
Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools
Microsoft Excel: Visual Basic for Applications (VBA) Macros | Introduction
Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.
Copyright 2024 Avantix® Learning
You may also like
How to Replace Zeros (0) with Blanks in Excel
There are several strategies to replace zero values (0) with blanks in Excel. If you want to replace zero values in cells with blanks, you can use the Replace command or write a formula to return blanks. However, if you simply want to display blanks instead of zeros, you have two formatting options – create a custom number format or a conditional format.
What is Power Query in Excel?
Power Query in Excel is a powerful data transformation tool that allows you to import data from many different sources and then extract, clean, and transform the data. You will then be able to load the data into Excel or Power BI and perform further data analysis. With Power Query (also known as Get & Transform), you can set up a query once and then refresh it when new data is added. Power Query can import and clean millions of rows of data.
How to Freeze Rows in Excel (One or Multiple Rows)
You can freeze one or more rows in an Excel worksheet using the Freeze Panes command. If you freeze rows containing headings, the headings will appear when you scroll down. You can freeze columns as well so when you scroll to the right columns will be frozen.
Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.
Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca
