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

by Avantix Learning Team | Updated July 31, 2020

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.

We wrote about this tool in an earlier article which you may want to check out first – How to Extract or Clean Data with Excel’s Amazing Flash Fill. Keep in mind that this tool is only available in Excel 2013 and later versions.

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

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.

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.

After Flash Fill:

Excel Flash Fill Example combining text and changing case 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.

After Flash Fill:

Excel Flash Fill Example combining  partial text and changing case 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.

Related

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

Recommended 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 classroom training courses are delivered at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some courses may also be delivered at an alternate downtown Toronto location). Contact us if you’d like to arrange custom training at your office on a date that’s convenient for you.

To request this page in an alternate format, contact us.

Copyright 2020 Avantix® Learning 

You may also like

How to Convert Cm to Inches in Excel (or Inches to Cm)

How to Convert Cm to Inches in Excel (or Inches to Cm)

You can convert cm to inches (or inches to cm) using formulas with operators or functions in Excel. This is useful since the regional settings on a computer affect the default measurement system used in Excel and other programs.

How to Hide Comments or Notes in Excel Workbooks

How to Hide Comments or Notes in Excel Workbooks

You can hide comments and notes in Excel workbooks. In addition to adding comments in Excel, there is a little known function called the N function that you can use to enter notes directly within a formula. The N function has been around for a long time so you can use it in older versions of Excel.

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 Inc.

Pin It on Pinterest

Share This