Andrews

Posted on Nov 15, 2021 • Originally published at geekexcel.com on Nov 11, 2021

FLASH FILL Options in Microsoft Excel 365!

#completeguidetoflash #exceltutorials #excel

A Complete Guide to Know About Flash Fill in Excel:

Excel Flash Fill Option is mainly used to automatically fill the data in the column using the example pattern. In this article, we are going to learn the Flash Fill options in Excel 365.

Versions of Excel have Flash Fill Option:

Excel Flash Fill was released in Excel 2013, and other later versions (Excel 2013, 2016, 2019, and Excel Office 365) have this.
Excel 2010 or any other previous versions didn’t have Flash Fill Option.

Flash Fill in Excel:

You can find Flash Fill Option in two ways in Excel 365.

On the Data Tab in Ribbon Command. In the Data Tools ** Section, **Select the Flash Fill Icon.

On the Home tab, in the Editing Section Select Fill Option, then Click Flash Fill from the menu.

Use Flash Fill in Excel:

Here we are going to see an example of Flash Fill for how to use it.
In the below example, we want to join the first and second names of a person and city names to create an E-mail Id.
We give the pattern to Excel for the first cell. The remaining cells are filled with the Flash Fill option.

We can use Flash Fill by other options in Excel. Let us see one by one.

Automatic Flash Fill:

Flash Fill will automatically fill results with the help of the few examples we have given.
Excel then shows a preview in light gray color, and then you have to *press Enter * key to see the results.
Flash Fill cannot fill upward cells in the column, and it can only fill the downward cells from the example.

Flash Fill Command in the Ribbon:

If you want to activate the Flash Fill option manually, you need to know the following instructions.

You need to ** type the example data** that you want to be returned.
Select the cells you want to fill and the cells you have given examples.
Then, go to the Data tab. In the Data Tools section, select the Flash Fill Command.
Don’t forget this command also found on the Home tab, in the Editing Section Choose Fill Option and Select Flash Fill Option from the menu.

Flash Fill Keyboard Shortcut:

In Excel, everything has shortcut options. So Flash Fill also has a shortcut key.
You have to do some actions before using Shortcuts.
Type the example data which you want to be returned.
Select both the cells you want to fill and cells with examples.
Now, press CTRL + E on your keyboard.

Flash Fill from the Fill Handle:

We will use flash Fill from Fill Handle.
To do this, Select the example cells and Right-Click on it, then drag the Fill Handle Down. Then release right-click from a mouse.
Choose Flash Fill Option from the menu.

Add Flash Fill to the Quick Access Tool Bar:

Right-Click on the ** Quick Access Tool Bar** or the Ribbon to view the Excel Options Window.
Click the Quick Access Toolbar from the menu.
Select All Commands and Choose Flash Fill from the commands.
Hit the Ok button.
Now, We will add the Flash Fill Icon to the Quick Access Tool Bar.

Enable or Disable Flash Fill:

You can disable Flash Fill Option in Excel.
To do this, you need to go to the File tab on the Ribbon. Select Options from the menu.
Then Select the Advanced tab; uncheck ** the Automatically Flash Fill** option from the Editing Options.
Hit the Ok button.

Still, you will use Flash Fill with the help of Ribbon Command and Shortcut.

Practical Examples for Flash Fill in Excel:

Let’s see some set of examples for Flash Fill, and it will help you understand clearly.

Extract the Last or First Name from a Full Name:

You can easily ** Extract the Last or First Name** from a full name.
For example, we will Extract the First Name from a full name using Flash Fill like the below.

Concatenate First and last Name to Create a Full Name:

The following example uses Flash Fill to Combine the First and Last Names into a format.

Convert Names to Initials:

Flash Fill can ** extract the Initials** from the first and last names.
If you have separate first and last names, they will be combined and removed the initial from it.

Change Any Text to Upper Case:

You can change the text case for your data using Flash Fill.
Here, we have shown the example for changing the text to Upper Case.

Change Any Text to Lower Case:

You can change the text case for your data using Flash Fill.
Here, we have shown the example for changing the text to Lower Case.

Change Any Text to Proper Case:

You can change the text case for your data using Flash Fill.
Here, we have shown the example for Change the text to Proper Case.
The excellent case will change the Mixed Case of data.

Extract the Company Name from an E-Mail Address:

You can get a Company Name from the E-Mail Address using Flash Fill.

Extract the Name from an E-Mail Address:

The following example shows that you can Extract Name from the E-mail Id.

Create an E-Mail Address from the list of First and Last Name:

This example shows you how to create an E-mail address in the format .@companyname.com using Flash Fill Option.

Extract the Number from Any Text String:

If your text contains a number, then you can extract the number from the text strings using Flash Fill.

Extract the Letters from Any Text Strings:

If you want to extract the text part of your data, then use the Flash Fill method to do that.

Format Numbers As Phone Numbers:

The following example uses Flash Fill to convert 10-digit phone numbers to (###) ###-#### format.

Remove Excess Space Characters:

The following example shows you to remove extra space characters ( from the start, middle, and end of the text ) using Flash Fill.

Convert Text to Date Values:

Flash Fill can convert text strings into date values.

Options Button in Flash Fill:

After using Flash Fills, a floating options button appear at the end of the column. If you click this button it will show some options.

Flash Fill Error:

If the Flash Fill cannot determine any pattern, it will show like the below image.

Remedies when Flash Fill shows this Error:

Go through your example and correct any errors. A small spelling mistake can result in Excel failing to find a pattern.
Provide more examples for Flash Fill. It may need a few more examples to find the correct way.
If the Flash Fill option doesn’t run, it may be disabled. Then Check the Excel Options menu to see whether the automatically run flash fill option is enabled or not.

Limitations of Flash Fill:

The results are not dynamic.
Flash Fill may not always return the result. The pattern can be too complex for Excel.
Flash Fill does not fill the results horizontally, and it can only fill results vertically.

End of the Article:

In the above article, you can understand how to use Flash Fill and practical examples and their limitations. Drop your feedback in the comment section. Thanks for visiting Geek Excel. Keep Learning!!

DEV Community