Everything You Need To Know About Flash Fill In Microsoft Excel [15 Examples]

In this post you’re going to learn everything you need to know about Flash Fill in Microsoft Excel.

What’s so great about it?

It’s quick and easy to learn how to use, and it’ll save you hours and hours of work writing formulas or VBA to do the same sort of things.

Let me FILL you in on the details! 

EVBA.INFO

What Does Flash Fill Do?

Flash Fill is a data tool in Microsoft Excel that will allow you to combine, extract or transform data based on a few examples.

You only need to provide a couple examples of the results you want. Excel will guess the pattern and fill in the rest of the data for you.

The example shows how you can use Flash Fill to create an email address from three separate columns containing the first name, last name and company name.

In this case, providing just one example was enough.

Flash Fill was able to:

• Extract the first letter from the first name and transform it to lower case.
• Transform the last name and company name to lower case.
• Combine these with periods, @ symbols and .com extensions to create the email address.

Pretty awesome and way easier than creating an Excel formula to do the same thing.

Which Versions Of Excel Have Flash Fill?

Flash Fill was released in Excel 2013, and any later versions will also have it. This includes Excel 2013, 2016, 2019 and Excel for Office 365.

Excel 2010 or any other previous version will not have Flash Fill.

I always recommend getting Office 365 so you’ll always have the latest and greatest in Excel. You won’t have to worry about your version not having a feature.

Where Is Flash Fill In Excel?

The Flash Fill command can be found in the Data tab of the ribbon. Go to the Data tab ➜ Flash Fill in the Data Tools section.

But…

Flash Fill is so good, they also put it in the Home tab. Go to the Home tab ➜ Fill in the Editing section ➜ Flash Fill in the menu.

How To Use Flash Fill In Excel

In order to use Flash Fill, your data will need to be set up in a certain way.

1. The data your Flash Fill is based on will need to be in columns to the immediate left of the location you want to fill. You can’t have any empty columns between.
2. The examples provided need to be in the column immediately to the right. Each example needs to be on the same row as the data it’s based on.
3. Once Excel has figured out the pattern, it will preview the results in a light grey.

Automatic Flash Fill

Flash Fill will work automatically after entering a few examples if it’s enabled in the Excel options.

You can get it to automatically fill results by typing out the first few examples. Excel will then show a preview in light grey and you can accept the results by pressing Enter.

Note: Flash Fill can’t fill upward with the automatic fill. You can start your examples anywhere in the column, but Flash Fill will only fill downward from there.

Using The Flash Fill Command In The Ribbon

If Flash Fill doesn’t automatically fill the data, you can manually activate it.

Using the Flash Fill command from the ribbon.

1. Type out the example data you want returned.
2. Select both the cells you want to fill and the cells with the examples.
3. Go to the Data tab ➜ press the Flash Fill command in the Data Tools section.

Remember, this command is also found in the Home tab ➜ Fill in the Editing section ➜ Flash Fill in the menu.

Using The Flash Fill Keyboard Shortcut

There is also a keyboard shortcut available for Flash Fill. The process for using the keyboard shortcut is similar to using the command in the ribbon.

1. Type out the example data you want returned.
2. Select both the cells you want to fill and the cells with the examples.
3. Press Ctrl + E on your keyboard.

Using Flash Fill From The Fill Handle

Flash Fill can also be used from the fill handle. Select the example cells ➜ right click and drag the fill handle down ➜ release the right click ➜ choose Flash Fill from the resulting menu.

Adding Flash Fill To The Quick Access Toolbar

Another option for using Flash Fill is to add it to the Quick Access Toolbar. This way the command will always be available regardless which ribbon tab you’re currently on.

Right click anywhere in the Quick Access Tool bar or Ribbon ➜ select Customize Quick Access Toolbar.

1. Choose All Commands.
2. Select Flash Fill from the list of commands.
3. Press the Add button.
4. Press the OK button.

Using the Flash Fill command from the Quick Access Toolbar is the same process as using either the ribbon command or keyboard shortcut.

How To Enable Or Disable Flash Fill

It is possible to disable Flash Fill.

This can be great if Flash Fill is being accidentally triggered when you don’t want it.

Go to the File tab ➜ Options ➜ Advanced tab in the Excel Options ➜ Uncheck Automatically Flash Fill in the Editing options section.

You will still be able to use Flash Fill with the commands in the ribbon or the keyboard shortcut. It just won’t automatically happen based on typing examples.

Examples Of What You Can Do With Flash Fill

You’ll be able to do a lot with Flash Fill. Here are a few examples to see what’s possible with this amazing data tool.

Extract The First Or Last Name From A Full Name

If you have a list of full names then you can use Flash Fill to extract part of the name.

This example uses Flash Fill to get the last name from a list of names that are all in the <first> <last> format. You could also easily extract the first name as well.

Concatenate First And Last Names To Create A Full Name

If you’re starting out with a list of first and last names, then you can use Flash Fill to combine the data into single cell.

This example uses Flash Fill to combine the first and last names into a <last>, <first> format.

Convert Names To Initials

If you need to get the initials from a list of names, Flash Fill can handle this. Whether you have separate first and last names or they are combined, Flash Fill will be able to extract those initials.

This example uses Flash Fill to get the initials from both first and last name into the <first initial>. <last initial>. format.

Change Any Text To Upper Case

You can change the case of any text so that all letters are upper case capitalized letters.

This example uses Flash Fill to convert the entire sentence into upper case.

Change Any Text To Lower Case

You can change the case of any text so that all letters are lower case.

This example uses Flash Fill to convert the entire sentence into lower case.

Change Any Text To Proper Case

You can change the case of any text so that each word is proper case. This means each word will start with a capital letter and the remaining letters will all be lower case.

This example uses Flash Fill to convert the entire sentence into proper case.

Extract The Company Name From An Email Address

You can get the domain name from a list of email addresses. This will usually be the same as the company.

This example uses Flash Fill to extract and capitalize the domain of each email address.

Extract The Name From An Email Address

You can get the name from a list of email addresses. If the emails are all in the same format, then Flash Fill will be able to get the names.

This example uses Flash Fill to get the full name from each email address.

Create An Email Address From A List Of First And Last Names

Usually email addresses within a company follow the same format. You can use this fact to create a list of email addresses if you have a list of names.

This example uses Flash Fill to create the email address in the format <first>.<last>@companyname.com based on the list of first and last names.

Extract The Number From Any Text String

If you have text containing numbers, you can use Flash Fill to extract the numbers.

This example example gets only the number part from the text.

Extract The Letters From Any Text String

If you want only the text part of your data, Flash Fill can do that.

This example extracts only the letters from a product code that contains both letters and numbers.

Format Numbers As Phone Numbers

If you have a list of numbers then you can convert them into a phone number format.

This example uses Flash Fill to convert 10 digit numbers into phone numbers in the (###) ###-#### format.

Remove Excess Spaces Characters

I previously wrote about 4 ways to remove excess space characters from your text. I didn’t think about Flash Fill as a solution, but it can also remove all the extra spaces in your text.

This examples uses Flash Fill to remove all the extra space characters from the start, middle and end of the text data.

Flash Fill To Add Line Breaks In An Address

You can add line breaks in your data.

This example uses Flash Fill to combine the street, city and country one cell and separates them with a line break.

You could also use Flash Fill to remove the line breaks in your data and create a comma separated list instead.

Convert Text To Date Values

Values that look like dates but are actually text strings can be tricky to work with. Flash Fill can convert them to date serial numbers recognized by Excel.

This example uses Flash Fill on the numbers entered as text strings to convert them into proper Excel date values.

Flash Fill Options Button

After using Flash Fill, a floating options button will appear next to the returned results. Clicking on this button will open a menu with all the options available.

• You can Undo Flash Fill results. This will remove the data in any cells that have been filled by Flash Fill. You can do the same thing with Ctrl + Z.
• You can Accept suggestions from Excel. Before you accept the suggestions, you can edit any of the results to correct the Flash Fill.
• You can Select all N changed cells. This allows you to select all the filled cells. You can then further inspect these to insure the results are correct.

Flash Fill Error

If Flash Fill is unable to determine a pattern, then it will show the above error.

We looked at all the data next to your selection and didn’t see a pattern for filling in values for you.

To use Flash Fill, enter a couple of examples of the output you’d like to see, keep the active cell in the column you want filled in, and click the Flash Fill button again.

Potential Remedies When Flash Fill Fails

When Flash Fill fails to return any results or fails to return the correct results, there are possible actions you can take to remedy the problem.

1. Review your examples and correct any errors. A small spelling mistake or missing number can result in Excel failing to find a pattern.
2. Delete your examples and start over. Sometimes you might not be able to see your error when inspecting your examples, and starting over might help.
3. Provide Flash Fill with more examples. Excel may need a few more examples to get the correct pattern.
4. If Flash Fill doesn’t automatically run after providing the first couple examples, you can use the ribbon command or keyboard shortcut to make it run.
5. If Flash Fill doesn’t automatically run, it may be disabled. Check the Excel options menu to make sure the option to automatically run Flash Fill is enabled.

Limitations Of Flash Fill

While Flash Fill is very powerful, it does have limitations.

1. Results are not dynamic. Flash Fill values will not update when you change the values they’re based on. You will need to perform the Flash Fill again in order to update the values.
2. Flash Fill might not always return results. The pattern might be too complex for Excel.
3. Flash Fill may incorrectly identify the pattern and return undesired results. With lots of data, incorrect results might be hard to spot so you essentially need to trust a black box algorithm.
4. Flash Fill doesn’t fill results horizontally. Your data will need to be vertical.

Conclusions

Flash fill is quick and easy to use.

It’s also very powerful and can do all the hard work when is comes to changing your data based on a pattern. It will definitely save you time.

In a lot of cases, it will help you avoid complex formulas to manipulate your data.

Are you using Flash Fill yet?

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1