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:

  1. Enter the sample data in a cell to the right of the source data.
  2. Press Enter or Ctrl + Enter to enter the data in the cell.
  3. 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:

Excel Flash Fill example combining data and changing case with names.

After Flash Fill:

Excel Flash Fill Example combining text and changing case with names finished.

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:

Excel Flash Fill Example combining partial text and changing case with names.

After Flash Fill:

Excel Flash Fill Example combining partial text and changing case with names finished.

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:

Excel Flash Fill example extracting dates.

After Flash Fill:

Excel Flash Fill Example extracting dates finished.

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:

Excel Flash Fill Example extracting numbers from text.

After Flash Fill:

Excel Flash Fill Example extracting numbers from text finished.

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:

Excel Flash Fill Example extracting parts of numbers.

After Flash Fill:

Excel Flash Fill Example extracting parts of numbers finished.

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:

Excel Flash Fill Example extracting parts of numbers and formatting.

After Flash Fill:

Excel Flash Fill Example extracting parts of numbers and formatting finished.

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:

Excel Flash Fill Example extracting hours and minutes.

After Flash Fill:

Excel Flash Fill Example extracting hours and minutes finished.

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:

Excel Flash Fill Example extracting text data.

After Flash Fill:

Excel Flash Fill Example extracting text data finished.

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:

Excel Flash Fill Example adding text to numbers.

After Flash Fill:

Excel Flash Fill Example adding text to numbers finished.

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:

Excel Flash Fill Example removing unnecessary spacing.

After Flash Fill:

Excel Flash Fill Example removing unnecessary spacing finished.

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

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), 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 onsite training or an online course on a date that's convenient for you.

Copyright 2021 Avantix® Learning

You may also like

3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells

3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells

You can apply strikethrough to text or values in Excel to cross out or create a line through a cell or part of a cell. There are 3 common ways to apply strikethrough in your Excel worksheets – using a built-in keyboard shortcut, using the Font dialog box, or by adding a command to the Quick Access Toolbar.

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 | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Summary
10 Excel Flash Fill Examples (Extract, Combine, Clean and Format Data with Flash Fill)
Article Name
10 Excel Flash Fill Examples (Extract, Combine, Clean and Format Data with Flash Fill)
Description
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.
Author
Publisher Name
Avantix Learning

Pin It on Pinterest

Share This