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

by Avantix Learning Team | Updated October 14, 2016

Applies to: Microsoft® Excel® 2013 and 2016 (Windows)

Flash Fill is a great new 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. We wrote about this tool in an earlier article which you may want to check out first – Extracting Data with Excel’s Amazing Flash Fill. Keep in mind that this tool is only available to Windows users with 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: 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 Control + Enter to enter the data in the cell.
  3. Press Control + E to Flash Fill.

Sample 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:

Microsoft Excel Flash Fill sample 1 of combining text and changing case.

After Flash Fill:

Microsoft Excel Flash Fill finished sample 1 of combining text and changing case.

Sample 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:

Microsoft Excel Flash Fill sample 2 of combining partial text and changing case.

After Flash Fill:

Microsoft Excel Flash Fill finished sample 2 of combining partial text and changing case.

Sample 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:

Microsoft Excel Flash Fill sample 3 of extracting dates.

After Flash Fill:

Microsoft Excel Flash Fill finished sample 3 of extracting dates.

Sample 4: Extracting numbers from text

In this worksheet, we entered only the numbers from A2 in B2 and then used Flash Fill:

Microsoft Excel Flash Fill sample 4 of extracting numbers from text.

After Flash Fill:

Microsoft Excel Flash Fill finished sample 4 of extracting numbers from text.

Sample 5: Extracting parts of numbers

In this worksheet, we entered only the first three numbers from A2 in B2 and then used Flash Fill:

Microsoft Excel Flash Fill sample 5 of extracting parts of numbers.

After Flash Fill:

Microsoft Excel Flash Fill finished sample 5 of extracting parts of numbers.

Sample 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:

Microsoft Excel Flash Fill sample 6 of extracting parts of numbers and applyhing formatting.

After Flash Fill:

Microsoft Excel Flash Fill finished sample 6 of extracting parts of numbers and applyhing formatting.

Sample 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:

Microsoft Excel Flash Fill sample 7 of extracting hours and minutes.

After Flash Fill:

Microsoft Excel Flash Fill finished sample 7 of extracting hours and minutes.

Sample 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:

Microsoft Excel Flash Fill sample 8 of extracting text data.

After Flash Fill:

Microsoft Excel Flash Fill finished sample 8 of extracting text data.

Sample 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:

Microsoft Excel Flash Fill sample 9 of adding text to numbers.

After Flash Fill:

Microsoft Excel Flash Fill finished sample 9 of adding text to numbers.

Sample 10: Removing unnecessary spacing

In this worksheet, we entered the address from A2 in B2 without the extra spaces and then used Flash Fill:

Microsoft Excel Flash Fill sample 10 of removing unnecessary spacing.

After Flash Fill:

Microsoft Excel Flash Fill finished sample 10 of removing unnecessary spacing.

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.

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.

Recommended Microsoft Excel training

Interested in taking your Excel skills to the next level? Check out our upcoming Excel 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 2019 Avantix® Learning Inc.

You may also like

How to Create 3D References in Microsoft Excel

You can use 3D references in Excel formulas to calculate across multiple worksheets that are structured in the same way. A 3D reference refers to the same cell or range of cells on multiple worksheets.

How to Freeze Row and Column Headings in Microsoft Excel

As many Excel worksheets can become quite large, it can be useful to freeze row and column headings so that they are locked in place when you scroll through your worksheet. In Excel, you can freeze both row headings and column headings or just one.

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
Article Name
10 Excel Tricks Using Flash Fill
Description
Check out these great Excel tricks. Excel's Flash Fill can automatically extract, combine and format data without using a formula. It does seem like magic. This tool is only available to Windows users with Office 2013 and later versions.
Author
Publisher Name
Avantix Learning

Pin It on Pinterest

Share This