Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

Flash Fill in Excel

Tags: flash excel

Flash Fill in Excel

Table of Contents

  • Flash Fill in Excel
  • How to Use Flash Fill in Excel?

What is Flash Fill in Excel?

I term Flash Fill as Life Saver. I term Flash Fill as productivity increaser. I term Flash Fill as a time saver and many more. Flash Fill in Excel understands your data pattern and repeat the same data pattern entered by you in excel.

One simple request to all our readers. Please install the latest version of Excel to see the magic of Flash Fill option in excel. Flash Fill is available from Excel 2013 and Excel 2016

How to Turn On Flash Fill in Excel 2013 and 2016?

By default, excel must have turned on Flash Fill option in your system. Just in case it has not turned on turn on by following below steps.

Go to File > Options.

Flash fill turn on

Then Go to Advanced > CheckBox Automatically Flash Fill.

Flash Fill turn on 1

How to Use Flash Fill in Excel 2013 and 2016?

Now take a look at some of the live common examples in the practical world.

Example #1 – Extract FY From the Invoice Number

I have data of Invoice numbers. I want to extract FY from this list. Applying many complex formulas will be a tough task for the intermediate user. But Flash Fill save me here.

Flash Fill Example 1

Step 1: Firstly we need to tell the excel what we are doing so type the first FY in the cell B2.

Flash Fill Example 1-1

Step 2: Now go to Data > Flash Fill.

Flash Fill Example 1-2

The Shortcut key to apply Flash Fills is:

Flash Fill shortcut key

Step 3: After typing the first FY into the cell now excel understands the pattern of the fill. We can Flash Fill in two ways here.

Firstly, drag the first entry until the end and Now click on the AUTOFILL option and select the Flash Fill.

Flash Fill Example 1-3

Now it will insert all the FY numbers from the invoice numbers columns.

Flash Fill Example 1-4

Secondly, after typing the first FY into the cell, press CTRL + E it will extract FY the invoice numbers columns.

Flash Fill Example 1-5

Example #2 – Extract First Name & Last Name

This is the common task we all have done in the past. Flash Fill can help us here.

Flash Fill Example 2

From the above list, I want to get first name & last name separately.

Firstly I will type the first name in B2 cell and last name in the C2 cell.

Flash Fill Example 2-1

Now I will go to B2 cell and press CTRL + E.

Flash Fill Example 2-2

I will go to C2 cell and press CTRL + E.

Flash Fill Example 2-3

Flash Fill Example 2-4

Example #3 – Format Numbers Using Flash Fill

Flash Fill not only extracts a portion of the cell but also helps us format the cell value. Now take a look at the below example. I have phone numbers and I want to format like this: 9056-2358-90.

Flash Fill Example 3

Firstly, I will type the format in the first cell.

Flash Fill Example 3-1

By this time excel already knows the pattern of the data series Now I will type CTRL + E.

Flash Fill Example 3-2

Flash Fill Example 3-3

Example #4 – Combine Two Values Using Flash Fill

Flash Fill not only separates first name and last name but also combines as well. Now I have first name & last name in two different columns, I want the full name in the next column.

Flash Fill Example 4

Type the required patter name in the cell C2.

Flash Fill Example 4-1

Now press the shortcut key to Flash Fill.

Flash Fill Example 4-2

Flash Fill Example 4-3

Limitations of Flash Fill in Excel 2013 and 2016

Flash Fill has its own limitations too. It is very important to know them:

  • Flash Fill is not dynamic. Remember it is not a formula to change the resulting cell if any changes.
  • Flash Fill can wrongly enter the data. Take look at the below example.

Flash Fill Limitations

In the above image, I wanted to extract the middle name. In the first cell I have typed the middle name and pressed CTRL + E. But for few names, there are no middle names so it has extracted the first name itself.

Things to Remember About Flash Fill in Excel 2013 and 2016

Flash Fill works based on the pattern of the data. If there is no pattern excel will show the below error message.

Flash Fill error

You can Download this Flash Fill Excel template here – Flash Fill Excel Template

Recommended Articles

This has been a guide to Flash Fill in Excel. Here we discuss how to Use Flash Fill in excel 2013 and 2016 using Data Tab, Shortcut key and Auto Fill option along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

  • Use Fill Handle in Excel
  • Filter Excel Shortcut
  • How to Conditional Format Pivot Table
  • Excel Circular Reference

The post Flash Fill in Excel appeared first on Learn Investment Banking: Financial Modeling Training Courses Online.



This post first appeared on Free Investment Banking Tutorials |WallStreetMojo, please read the originial post: here

Share the post

Flash Fill in Excel

×

Subscribe to Free Investment Banking Tutorials |wallstreetmojo

Get updates delivered right to your inbox!

Thank you for your subscription

×