Check Out These 10 Great Examples using Excel’s Flash Fill

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 and format data without using a formula. It does seem like magic. Here are 10 examples where we’ve used Flash Fill to clean up data.

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 use 10 Flash Fill tricks 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 use Flash Fill, check out the article How to Use Flash Fill in Excel (Beginner’s Guide).

Example 1: Combining text and changing case

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

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

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

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

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

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

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

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: Adding text to numbers

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

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 Use Flash Fill in Excel to Clean or Extract Data (Beginner’s Guide)

How to Delete Blank Rows in Excel (in 5 Simple Steps)

How to Quickly Fill in Missing Values from the Cell Above in Excel

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 live 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 training 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.

How to Lock the Position of a Slicer in Excel

How to Lock the Position of a Slicer in Excel

Slicers are a great tool in Excel to filter tables or pivot tables. You can lock the position of a slicer in an Excel worksheet using a two-step process. First, select the slicer or slicers and Disable resizing and moving in the Format Slicer task pane (and choose other options based on your preferences). Second, protect the sheet using the Review tab in the Ribbon. There are a few important options you’ll need to select to get this to work.

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
Excel Flash Fill Tricks to Extract or Clean Data (10 Examples)
Article Name
Excel Flash Fill Tricks to Extract or Clean Data (10 Examples)
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 and format data without using a formula. It does seem like magic. Here are 10 examples where we've used Flash Fill to clean up data.
Author
Publisher Name
Avantix Learning

Pin It on Pinterest

Share This