To enter data in a cell using examples already in your workbook, you can use the flash fill feature.

Excel is one of the powerful suits provided by Microsoft. It incorporates user friendly tools and amazing features and one such incredible feature is Flash Fill. This tool eliminates the manual process where the user had to invest their time by typing the data. Not only does it take hours to be performed but often leads to many typing errors. To save time and prevent typo errors Excel came with the Flash Fill option that executes the same operation automatically (if it involves any pattern) in a flash.

In this tutorial we will briefly discover the fundamentals of the Flash Fill functionality including its definition, shortcuts, various Flash Fill options and will perform examples using Flash Fill in Excel.

What is Flash Fill?

"Excel Flash Fill is a special feature introduced by Microsoft that examines the data entered by the user and if it involves any kind of pattern it automatically fills the remaining information in a single click."

Flash Fill quickly copes with several different tasks that otherwise need complicated formulas or even lengthy VBA code, unlike splitting and merging strings, sorting the data and correcting deviations, formatting the content or the numbers, turning dates figure to the required format, and a lot more.

Every time you apply Flash Fill to your Excel worksheet at the backend to accomplish that, it merges millions of multiple small programs, sorts those program snippets utilizing machine-learning algorithms, and ascertains the flawless job. To our surprise, all these activities are done in milliseconds. Therefore delivering the output almost immediately!

Where is Flash Fill in Excel?

In Excel, to locate the Flash Fill tool, follow the given steps:

1. Go to the Data tab.
2. In the Data Tools group, you will find the Flash Fill option.

Flash Fill shortcut key

One of the reasons why Excel is popular among its users is because of its shortcut keys. Excel provides shortcut key keys for almost every operation. Those who quickly want to access Flash Fill without using a mouse can run it directly from the keyboard using the shortcut keys: Ctrl + E.

Steps to implement Flash Fill in Excel

Whenever Excel comes across any type of pattern, it automatically suggests the output using the Flash Fill tool. Therefore to implement this tool, the user only needs to provide a pattern.

1. Insert three columns in your Excel worksheet. The first column should include the First name, in the next mention Last name, and the third one mention Full name.
   
   
2. In the third column, we will combine the first name and last name. Manually type in the output for the first two cells as shown below.
   
   
3. Select the cells, Go to Data-> Data Tools -> Flash Fill, and if Excel senses a pattern, it will automatically fill the data for the other entries.

How to apply Flash Fill using shortcut

In most conditions, Flash Fill fits in automatically as soon as Excel verifies the data in the form of some pattern. If, due to any reason, the suggestion or the preview does not appear, you can apply the Flash Fill tool manually by following the below steps:

1. Enter the data in the first cell and press Enter. Repeat the same for the second cell.
   
   
2. Press the Flash Fill shortcut keys from your keyboard, i.e., Ctrl + E. All the data values will be automatically filled in down the column.
   
   

Excel Flash Fill options

Flash Fill is a very versatile tool that allows the user to automate their data entry in Excel. The Flash Fill Options button is displayed next to the auto-filled cells. Clicking on the options menu button will open the following window:

• Undo the output of Flash Fill.
• Select blank cells whose values are not filled by the Flash Fill tool.
• Select the formatted cells, for instance, to change the formatting of all cells at once.

Excel Flash Fill examples

While working in Excel, you often need to format the data, such as pin code, phone numbers, country code, etc. You may also want to remove the leading or trailing spaces between your content or the improper use of punctuation in your content. If you work manually, it could take long hours to fix all these errors, and still, there are chances of errors, whereas the same task can be accomplished in a blink of time with the help of Flash Fill.

Let's understand the capabilities of this versatile tool through some real-life examples:

1. Extract text from cell (split columns)

Extracting content from Excel data is one of the basic operations carried out by millions of Excel users daily. Before Flash Fill came into the picture, users needed to do complicated text manipulation or even intricate VBA coding.

For example:

NOTE: Flash Fill is used to extract any substring, numbers from string, zip codes, or even allows the user to split cells in Excel.

2. Concatenate data from different cells

Flash Fill allows the user to concatenate the data in an Excel spreadsheet. Earlier, without Flash fill, it was a lengthy process as the user needed to apply the formula first to concatenate and then format it with proper punctuation.

For Example:

In the below spreadsheet, we need to combine the first name, middle name, and last name to a new column.

It can combine two cells that can further be separated with characters such as space, comma, semicolon, etc., Type in the value in the first two cells and apply the flash fill.

After implementing Flash Fill, all the cells will be automatically filled and will have the following output.

3. Clean data

Maybe you are working with Excel worksheets that are extracted from a database or other data source platforms. The data extracted comes along with unfinished formattings, such as it could contain leading space or undefined punctuations.

For example, in the below spreadsheet, we have undefined characters and leading and trailing spaces in our data.

Type the first name without any preceding space, repeat the same for the second cell and apply the Flash Fill. You will get the following output.

4. Format text, numbers and dates

Formatting your Excel spreadsheet is necessary as it makes your data look clean and make it more readable. Sometimes while working with Excel your data is formatted in some style while you want it in another. In the new column type the first two values exactly as you want them to look like , and apply the Flash Fill.

Limitation of Flash Fill

Undoubtedly, Flash Fill is a special tool, but it too has some restrictions. Below given are some limitations of this tool:

1. The Output of Flash Fill is static.
   Flash Fill results are static, which means they do not update automatically. Therefore, unlike Excel formulas, it won't affect the Flash Fill result if you make any modifications to the original data.
2. Flash Fill may fail to recognize a pattern or may give the wrong output.
   In some cases, Flash Fill may not deliver the expected output. It happens mostly when your data is organized or formatted differently.
3. Skips the cells that contain non-printable characters.
   If some cells of your original data contain spaces or other non-printable characters, Flash Fill ignores those cells.
4. It converts the numbers to alphanumeric strings.
   While using Flash Fill for numbers, please note that this tool converts your numbers to string values.

How to turn off Flash Fill

Though by default Excel automatically turns on the Flash Fill feature. But sometimes, the automatic suggestion or the preview could be disturbing, or the user doesn't want the data to be filled automatically. In that case, they can turn off or disable the Flash Fill tool from their Excel worksheet. Follow the below steps:

1. Open your Excel worksheet; from the ribbon bar, click on File> Options.
2. From the left panel, go to Advanced.
3. Scroll down and look for Editing options. Untick the Automatically Flash Fill box. Click on OK.

Do you have selected a cell with the formula Which of the following can you use to copy the formula to an adjacent cell?

Which of the following is true when you copy and paste formulas using the fill handle?

When you run the spell checker in a workbook Excel automatically check the spelling in all worksheets at once?

Which of the following lets you edit the content of a cell?