Check Out These 10 Great Examples using Flash Fill in Excel
by Avantix Learning Team | Updated January 25, 2021
Applies to: Microsoft® Excel® 2013, 2016, 2019 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 sample data in a cell to the right of the source data.
- Press Enter or Ctrl + Enter to enter the data in the 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 – Combining text and changing case using Flash Fill (combine and format)
In this worksheet, we entered data in two different cases in C2 based on data in A2 and B2 and then used Flash Fill:
After Flash Fill:
Example 2 – Combining partial text and changing case using Flash Fill (combine and format)
In this worksheet, we entered data in capital letters in C2 based on data in A2 and B2 and then used Flash Fill:
After Flash Fill:
Example 3 – Extracting dates using Flash Fill (extract data)
In this worksheet, we entered date data in B2, C2, D2 and E2 based on data in A2 and then used Flash Fill:
After Flash Fill:
Example 4 – Extracting numbers from text using Flash Fill (extact data)
In this worksheet, we entered only the numbers from A2 in B2 and then used Flash Fill:
After Flash Fill:
Example 5 – Extracting parts of numbers using Flash Fill (extract data)
In this worksheet, we entered only the first three numbers from A2 in B2 and then used Flash Fill:
After Flash Fill:
Example 6 – Extracting parts of numbers and applying formatting using Flash Fill (extract and format)
In this worksheet, we entered the currency symbol and then the first three numbers from A2 in B2 and then used Flash Fill:
After Flash Fill:
Example 7 – Extracting hours and minutes using Flash Fill (extract data)
In this worksheet, we entered the start hour in B2 and the minutes in C2 from time data in A2 and then used Flash Fill:
After Flash Fill:
Example 8 – Extracting text data using Flash Fill (extract data)
In this worksheet, we entered the 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 – Combining text and numbers using Flash Fill (combine data)
In this worksheet, we entered the company prefix "AB" and then the number from A2 in B2 and then used Flash Fill:
After Flash Fill:
Example 10 – Removing unnecessary spacing using Flash Fill (clean data)
In this worksheet, we entered the 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 2023 Avantix® Learning
You may also like
Excel XLOOKUP Function (Overview, Syntax + Formula Examples)
The XLOOKUP function is a replacement for Excel's traditional VLOOKUP function (as well as HLOOKUP and INDEX / MATCH functions). It has a new set of arguments and is available in Excel 2021 and 365. It allows you to look up a value from an array in a range or table and return one or more results. One of the primary benefits of XLOOKUP is that it can look up from columns to the left in a data set and return a range.
How to Use Go to Special in Excel to Find, Select, Replace and Format Data
You can use Go To Special in Excel to quickly find and select cells of a specific type within your worksheet. It only selects cells in the current worksheet, not the entire workbook. Go To Special also searches within the selected range if a range has been selected. You can find many useful things using the Go To Special dialog box including formulas, constants, blanks, visible cells and conditional formatting. After you select multiple cells with Go To Special, you can enter data in the selected cells by pressing Ctrl + Enter or by applying formatting.
How to Lock Cells in Excel (3 Ways)
It's easy to lock and protect cells in Microsoft Excel to prevent users from changing data or formulas. This involves a two-step process …
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
