How to Make Use of the Flash Fill Feature in Excel (4 Ways with Shortcuts)

1. Activating Flash Fill through the use of a keyboard shortcut

The following are the steps to use Flash Fill via a keyboard shortcut: 

1. To extract, combine, clean, or format data from one or more columns of data, select the first cell to the right of the column or columns of data you wish to extract, combine, clean, or format.

2. Specify the value to be extracted, combined, cleansed, or formatted in the appropriate field (such as the first name from a cell in the same row in a column to the left). As a result of the fact that Flash Fill is case sensitive, be sure to enter the information in the appropriate case.

3. Press the Enter key.

4. In the cell below the first value, press the Ctrl + E keyboard shortcut. Microsoft Excel should automatically fill the cells in the current column with the data values from the adjacent column(s).

 

2. Using Flash Fill in the Ribbon  

To use Flash Fill, go to the Ribbon and select Flash Fill from the drop-down menu.

1.To extract, combine, clean, or format data from one or more columns of data, select the first cell to the right of the column or columns of data you wish to extract, combine, clean, or format.

2. Specify the value to be extracted, combined, cleansed, or formatted in the appropriate field (such as the first name from a cell in the same row in a column to the left). As a result of the fact that Flash Fill is case sensitive, be sure to enter the information in the appropriate case.

3. Press the Enter key.

4. Choose the cell that is immediately below the first value and then click the Data tab on the Ribbon. Flash Fill can be found in the Data Tools section. Microsoft Excel should automatically fill the cells in the current column with the data values from the adjacent column (s).

The Flash Fill command can be found in the Ribbon in the following locations:

 

3. Using Flash Fill in conjunction with a context menu 

To use Flash Fill with a context menu, complete the following procedures:

1.To extract, combine, clean, or format data from one or more columns of data, select the first cell to the right of the column or columns of data you wish to extract, combine, clean, or format.

2. Specify the value to be extracted, combined, cleansed, or formatted in the appropriate field (such as the first name from a cell in the same row in a column to the left). As a result of the fact that Flash Fill is case sensitive, be sure to enter the information in the appropriate case.

3. Press the Enter key.

4. In the cell directly beneath the first value, type a new value in the cell directly beneath it (such as the first name from a cell in the same row in a column to the left). It should appear in a pop-up window that contains the generated data if Excel is able to recognize the pattern.

5. Press the Enter key. Microsoft Excel should automatically fill the cells in the current column with the data values from the adjacent column (s).

If the data is inconclusive, the three-value method described above should be used.

 

4. Using Flash Fill in conjunction with a Smart Tag

These are the steps to take in order to use Flash Fill with a Smart Tag:

1.To extract, combine, clean, or format data from one or more columns of data, select the first cell to the right of the column or columns of data you wish to extract, combine, clean, or format.

2. Specify the value to be extracted, combined, cleansed, or formatted in the appropriate field (such as the first name from a cell in the same row in a column to the left). As a result of the fact that Flash Fill is case sensitive, be sure to enter the information in the appropriate case.

3. Press the Enter key.

4. Select the cell in which the first value was entered and drag the Fill handle (located in the bottom right corner) all the way down to the final cell to be filled. It is necessary to display a Smart Tag.

5. From the Smart Tag drop-down menu, select Flash Fill.

Example 1: Extracting first and last names from a document using Flash Fill

If you have a column of names, you can extract the first and last names from adjacent columns, as shown below (column B and C data were generated using Flash Fill): If you have a column of names, you can extract the first and last names from adjacent columns, as shown below (column B and C data were generated using Flash Fill):

Example 2: we will use Flash Fill to combine the last and first names

Because first and last names are contained within the same columns in Excel, it is possible to reorganize the data as last name followed by comma and then first name, or to generate emails for individuals who work within a single company, as shown in the following example (column C and D data were generated using Flash Fill):

Example 3: Using Flash Fill to properly format phone numbers

Using Flash Fill, it's possible to format phone numbers by adding brackets that weren't previously present in the data, as shown in the example below (column B data was generated by using Flash Fill):

Example 4: Extracting names from email addresses with the help of Flash Fill

If you look at the following example (column B data was generated using Flash Fill), you can see that you can use Flash Fill to extract the text that appears before the @ symbol from email addresses:

For the purpose of getting you started, here are a few Flash Fill examples. It is also possible to use Flash Fill with date and time data, so give it a shot.