Press a Button to Clear a Cell Press Button Again to Fill a Cell

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

In this post y'all're going to learn everything you need to know nigh Flash Fill in Microsoft Excel.

What's so great nearly information technology?

It's quick and easy to learn how to use, and it'll salve you hours and hours of piece of work writing formulas or VBA to practice the same sort of things.

Permit me Fill up you in on the details! 😆😂🤣

What Does Flash Make full Do?

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

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

The example shows how you can use Wink Make full to create an email accost from three separate columns containing the first name, last proper noun and visitor name.

In this case, providing just i example was plenty.

Wink Fill was able to:

• Extract the first alphabetic character from the start proper noun and transform information technology to lower instance.
• Transform the last name and company name to lower instance.
• Combine these with periods, @ symbols and .com extensions to create the electronic mail accost.

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

Which Versions of Excel Have Flash Fill?

Wink Make full was released in Excel 2013, and any later versions will also have it. This includes Excel 2013, 2016, 2022 and Excel for Function 365.

Excel 2010 or any other previous version volition not take Flash Make full.

I ever recommend getting Function 365 so you lot'll ever take the latest and greatest in Excel. Y'all won't have to worry about your version not having a feature.

Where is Wink Make full in Excel?

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

Just…

Flash Make full is and so proficient, they also put it in the Home tab. Go to the Home tab ➜ Fill in the Editing section ➜ Wink Fill in the bill of fare.

How to Use Flash Make full in Excel

In order to use Flash Fill, your data will demand to be set up in a sure style.

1. The information your Wink Fill up is based on will need to be in columns to the firsthand left of the location you want to fill. Y'all tin 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 exist on the same row as the information it'due south based on.
3. Once Excel has figured out the pattern, information technology will preview the results in a light greyness.

Automatic Flash Fill

Flash Make full volition work automatically after inbound a few examples if it's enabled in the Excel options.

Yous can get information technology to automatically fill results by typing out the kickoff few examples. Excel will then show a preview in light grey and you tin take the results by pressing Enter.

Notation: Flash Fill tin can't fill up upward with the automated fill. You lot can start your examples anywhere in the column, just Flash Fill will just fill downward from there.

Using the Flash Fill Command in the Ribbon

If Flash Fill doesn't automatically fill the data, yous tin manually activate it.

Using the Flash Fill command from the ribbon.

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

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

Using the Flash Fill Keyboard Shortcut

In that location is as well 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 make full and the cells with the examples.
3. Press Ctrl + Eastward on your keyboard.

Using Flash Fill from the Fill Handle

Wink Fill tin can besides be used from the make full handle. Select the example cells ➜ right click and elevate the fill handle downward ➜ release the right click ➜ choose Flash Fill from the resulting carte.

Calculation Flash Fill to the Quick Access Toolbar

Another choice for using Wink Make full is to add it to the Quick Access Toolbar. This way the command will e'er be available regardless which ribbon tab you're currently on.

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

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

Using the Flash Make full control 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 Wink Fill.

This tin be neat if Flash Make full 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 all the same be able to use Flash Fill with the commands in the ribbon or the keyboard shortcut. Information technology but won't automatically happen based on typing examples.

Examples of What You Can Do with Flash Fill up

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

Extract the First or Last Proper noun from a Full Name

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

This case uses Flash Fill to get the last name from a list of names that are all in the <outset> <terminal> format. You could also easily excerpt the first proper name also.

Concatenate First and Terminal Names to Create a Full Name

If you're starting out with a listing of start and terminal names, and so you can use Flash Fill up to combine the data into unmarried prison cell.

This case uses Flash Fill to combine the first and last names into a<final>, <start> 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 volition be able to extract those initials.

This example uses Wink Make full to get the initials from both first and last name into the <first initial>. <concluding initial>. format.

Change Any Text to Upper Example

You tin modify the case of any text so that all letters are upper case capitalized letters.

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

Change Whatsoever Text to Lower Case

You tin change the example of whatsoever text so that all messages are lower case.

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

Change Any Text to Proper Case

Yous tin can modify the case of any text then that each give-and-take is proper example. This means each discussion will start with a capital letter of the alphabet and the remaining messages will all be lower example.

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

Excerpt the Visitor Name from an Electronic mail Address

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

This case uses Wink Fill to excerpt and capitalize the domain of each email address.

Excerpt the Name from an Email Address

You lot tin can get the proper noun from a list of email addresses. If the emails are all in the same format, then Flash Fill volition be able to get the names.

This instance uses Flash Make full to get the full name from each email address.

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

Ordinarily electronic mail addresses within a visitor follow the same format. You tin use this fact to create a listing of email addresses if you take a list of names.

This example uses Flash Fill up to create the email accost in the format <first>.<final>@companyname.com based on the list of get-go and last names.

Extract the Number from whatsoever Text String

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

This case example gets only the number office from the text.

Extract the Letters from Any Text String

If y'all desire only the text part of your data, Flash Fill can practise that.

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

Format Numbers as Telephone Numbers

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

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

Remove Excess Spaces Characters

I previously wrote nigh four ways to remove excess space characters from your text. I didn't think about Wink Fill as a solution, but information technology tin can likewise remove all the actress spaces in your text.

This examples uses Flash Fill to remove all the actress space characters from the start, heart and finish of the text data.

Flash fill to Add Line Breaks in an Address

Yous tin add line breaks in your data.

This example uses Wink Fill up to combine the street, city and country i cell and separates them with a line interruption.

Yous could too use Flash Make full to remove the line breaks in your information and create a comma separated list instead.

Convert Text to Appointment Values

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

This case 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 push button will announced next to the returned results. Clicking on this button will open a menu with all the options available.

• You can Undo Wink Fill up results. This will remove the data in whatever cells that have been filled by Flash Make full. You can practice the aforementioned thing with Ctrl + Z.
• You tin Accept suggestions from Excel. Before y'all take the suggestions, you tin can edit any of the results to correct the Flash Fill.
• You can Select all Due north inverse cells. This allows you to select all the filled cells. You tin so further audit these to insure the results are correct.

Flash Make full Error

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

Nosotros looked at all the information next to your selection and didn't come across a pattern for filling in values for yous.

To apply Flash Fill up, enter a couple of examples of the output yous'd similar to see, keep the agile cell in the cavalcade you lot want filled in, and click the Wink Fill button again.

Potential Remedies When Wink Fill Fails

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

1. Review your examples and correct any errors. A small spelling mistake or missing number tin result in Excel failing to find a pattern.
2. Delete your examples and start over. Sometimes you might not be able to encounter 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 subsequently providing the first couple examples, y'all can use the ribbon command or keyboard shortcut to make it run.
5. If Wink Fill doesn't automatically run, it may be disabled. Check the Excel options menu to make certain the pick to automatically run Wink Fill is enabled.

Limitations of Flash Make full

While Wink Fill up is very powerful, it does have limitations.

1. Results are not dynamic. Flash Fill values volition not update when yous change the values they're based on. You volition need to perform the Flash Fill again in guild to update the values.
2. Wink Fill up might not always return results. The pattern might be also complex for Excel.
3. Flash Make full may incorrectly identify the design and render undesired results. With lots of information, incorrect results might be hard to spot so you substantially need to trust a black box algorithm.
4. Flash Fill up doesn't fill results horizontally. Your data will demand to exist vertical.

Conclusions

Flash fill is quick and easy to apply.

Information technology's also very powerful and can do all the difficult work when is comes to changing your data based on a pattern. It will definitely save yous fourth dimension.

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

Are you using Flash Make full yet?

About the Author

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Ability BI, Power Automate, Power Apps and SharePoint. You can notice other interesting articles from John on his blog or YouTube aqueduct.

fischerwhicated.blogspot.com

Source: https://www.howtoexcel.org/flash-fill/

Share this post