How To Use Flash Fill

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

In this post you lot're going to larn everything you lot need to know nearly Flash Fill in Microsoft Excel.

What'south and so great well-nigh it?

Information technology's quick and like shooting fish in a barrel to learn how to apply, and it'll salvage you hours and hours of work writing formulas or VBA to do the same sort of things.

Let me Make full yous in on the details! 😆😂🤣

What Does Flash Fill up Do?

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

You only demand to provide a couple examples of the results you desire. Excel volition guess the pattern and fill in the residuum of the data for you lot.

The example shows how you can utilise Flash Make full to create an email accost from three separate columns containing the showtime name, last proper noun and company name.

In this case, providing just one case was enough.

Flash Fill was able to:

Extract the first letter from the kickoff proper name and transform it to lower example.
Transform the concluding proper noun and visitor name to lower example.
Combine these with periods, @ symbols and .com extensions to create the email address.

Pretty awesome and fashion easier than creating an Excel formula to practise the same matter.

Which Versions of Excel Have Flash Fill?

Flash Make full 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 non have Flash Fill.

I always recommend getting Office 365 so you'll always have the latest and greatest in Excel. Yous won't accept to worry well-nigh your version not having a feature.

Where is Flash Make full in Excel?

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

But…

Flash Fill is and so good, they too put information technology in the Dwelling house tab. Go to the Home tab ➜ Fill in the Editing department ➜ Wink Fill in the menu.

How to Use Wink Fill in Excel

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

The data your Flash Fill is based on will demand to exist in columns to the firsthand left of the location you want to fill. You lot tin't take any empty columns between.
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.
One time Excel has figured out the design, it volition preview the results in a low-cal greyness.

Automated Flash Fill up

Flash Fill will piece of work automatically subsequently entering a few examples if it'due south enabled in the Excel options.

Yous can become 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.

Notation: Flash Fill can't fill upward with the automated fill. You can offset your examples anywhere in the column, but Flash Fill will simply make full downward from there.

Using the Flash Fill Command in the Ribbon

If Wink Fill doesn't automatically fill up the data, y'all can manually activate information technology.

Using the Wink Fill control from the ribbon.

Blazon out the example data you want returned.
Select both the cells you want to fill and the cells with the examples.
Go to the Data tab ➜ printing the Flash Fill control in the Information Tools section.

Remember, this command is too found in the Home tab ➜ Make full in the Editing department ➜ Wink Fill in the menu.

Using the Flash Make full Keyboard Shortcut

At that place is besides a keyboard shortcut available for Flash Fill. The process for using the keyboard shortcut is similar to using the command in the ribbon.

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

Using Flash Make full from the Fill Handle

Flash Fill can also exist used from the fill handle. Select the case cells ➜ correct click and drag the fill handle downwardly ➜ release the right click ➜ cull Flash Fill up from the resulting menu.

Adding Flash Fill to the Quick Access Toolbar

Some other option for using Flash Fill is to add together it to the Quick Access Toolbar. This way the command will always be available regardless which ribbon tab yous're currently on.

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

Cull All Commands.
Select Flash Fill from the listing of commands.
Press the Add button.
Printing the OK button.

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

How to Enable or Disable Wink Fill

Information technology is possible to disable Flash Fill.

This tin be great if Flash Fill is beingness accidentally triggered when y'all don't want it.

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

You volition still be able to use Flash Make full with the commands in the ribbon or the keyboard shortcut. It merely won't automatically happen based on typing examples.

Examples of What You Tin can Do with Flash Fill

You'll exist 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 Proper name from a Full Proper noun

If you take a list of total names and so you can employ Flash Fill to excerpt office of the proper name.

This example uses Flash Fill to get the last name from a listing of names that are all in the <first> <last> format. Y'all could also easily excerpt the start name as well.

Concatenate Get-go and Terminal Names to Create a Full Proper noun

If you're starting out with a listing of first and final names, then you can use Flash Fill to combine the data into single jail cell.

This example uses Flash Make full to combine the commencement and last names into a<concluding>, <beginning> format.

Catechumen Names to Initials

If you need to get the initials from a listing of names, Wink Fill can handle this. Whether you have separate offset and last names or they are combined, Flash Fill up volition be able to extract those initials.

This example uses Flash Fill to go the initials from both kickoff and last proper name into the <start initial>. <last initial>. format.

Change Whatsoever Text to Upper Instance

You can modify the instance of whatsoever text so that all letters are upper instance capitalized letters.

This example uses Flash Make full to convert the unabridged sentence into upper case.

Modify Any Text to Lower Case

You can alter the example of any text so that all messages are lower case.

This example uses Wink Make full to convert the entire judgement into lower case.

Alter Any Text to Proper Case

Y'all can alter the example of any text so that each word is proper example. This ways each word will start with a capital and the remaining messages will all be lower instance.

This instance uses Flash Fill to catechumen the entire sentence into proper case.

Excerpt the Company Name from an Email Address

Y'all can get the domain name from a listing of email addresses. This will usually be the aforementioned as the company.

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

Extract the Name from an Email Address

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

This example uses Wink Make full to go the full name from each email accost.

Create an Email Address from a List of First and Last Names

Ordinarily email addresses within a company follow the same format. You lot can utilize this fact to create a listing of email addresses if yous take a list of names.

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

Extract the Number from any Text Cord

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

This case instance gets only the number part from the text.

Extract the Letters from Any Text String

If yous desire but the text part of your data, Flash Make full tin do that.

This example extracts only the letters from a production 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 telephone number format.

This example uses Wink Fill up to convert 10 digit numbers into telephone numbers in the (###) ###-#### format.

Remove Excess Spaces Characters

I previously wrote about iv ways to remove backlog space characters from your text. I didn't retrieve about Flash Fill as a solution, just it can as well remove all the extra spaces in your text.

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

Wink 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, urban center and country one jail cell and separates them with a line break.

You could also use Flash Fill up 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 catchy to piece of work with. Wink Make full tin can convert them to date serial numbers recognized past Excel.

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

Wink Fill up Options Button

After using Wink Fill, a floating options push button will appear next to the returned results. Clicking on this push will open a carte du jour with all the options available.

Y'all can Disengage Flash Fill up results. This will remove the data in whatever cells that accept been filled past Flash Fill. You can do the same thing with Ctrl + Z.
You can Accept suggestions from Excel. Before you take the suggestions, you can edit any of the results to correct the Flash Fill.
You can Select all Northward changed cells. This allows you lot to select all the filled cells. You tin can then further inspect these to insure the results are correct.

Flash Fill Error

If Flash Fill is unable to determine a blueprint, so it will testify the above mistake.

We looked at all the data next to your choice and didn't run into a design for filling in values for y'all.

To apply Flash Fill up, enter a couple of examples of the output y'all'd like to see, keep the active cell in the column y'all desire filled in, and click the Flash Fill push once more.

Potential Remedies When Wink Fill Fails

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

Review your examples and correct any errors. A small spelling mistake or missing number tin result in Excel declining to find a pattern.
Delete your examples and start over. Sometimes you might non be able to run into your fault when inspecting your examples, and starting over might help.
Provide Flash Make full with more examples. Excel may need a few more than examples to become the correct pattern.
If Flash Fill doesn't automatically run after providing the first couple examples, you tin can use the ribbon command or keyboard shortcut to brand it run.
If Flash Fill up doesn't automatically run, it may be disabled. Check the Excel options menu to brand certain the option to automatically run Wink Fill is enabled.

Limitations of Wink Fill

While Flash Make full is very powerful, it does have limitations.

Results are not dynamic. Flash Fill values volition not update when you lot alter the values they're based on. Yous will need to perform the Flash Fill again in lodge to update the values.
Wink Fill might not always return results. The pattern might exist too complex for Excel.
Flash Fill up may incorrectly identify the pattern and return undesired results. With lots of data, wrong results might be hard to spot so yous essentially demand to trust a black box algorithm.
Flash Fill up doesn't fill up results horizontally. Your data will need to be vertical.

Conclusions

Wink fill up is quick and piece of cake to use.

It's also very powerful and tin do all the hard work when is comes to changing your data based on a pattern. It volition definitely save y'all time.

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

Are you using Wink Fill up even so?

Near the Author

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a diversity of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen trouble solver and has a passion for using technology to make businesses more efficient.