Flash Fill is a feature in Microsoft Excel that automatically fills in data for you based on a pattern it detects in your actions which is introduced in Excel 2013 and available in the later versions of Excel. For example, you can use Flash Fill to quickly extract the first names from a list of full names to the adjacent column without manually entry as below demo shown. This tutorial shows 4 ways on applying Flash Fill and provides 5 examples for explaining how to use Flash Fill in Excel.

How to apply Flash Fill in Excel

• By pressing Enter key | With shortcuts | Via the Excel Ribbon | With the Fill Handle

5 examples for using Flash Fill

• Extract text | Combine cells | Add text | Change formatting | Change string order

FAQ

• Flash Fill Limitations | Enable or disable Flash Fill | Flash Fill not working

Video: How to use Flash Fill

How to apply Flash Fill in Excel

There are four different methods to apply Flash Fill in Excel, and this tutorial will demonstrate each of them for you.

• Apply Flash Fill automatically by pressing Enter key
• Apply Flash Fill with shortcuts
• Apply Flash Fill via the Excel Ribbon
• Apply Flash Fill with the Fill Handle

Amazing! Using Efficient Tabs in Excel Like Chrome, Edge, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

Apply Flash Fill by pressing Enter key

To apply Flash Fill automatically in Excel using the Enter key, follow these steps:

Step 1: Start Typing the Pattern

In the cell where you want the Flash Fill to begin, type the first example of the pattern you want Excel to follow.

For instance, for extracting first names from full names in the list of A2:A6, type the first name in the cell next to the full name, here we type in B2.

Step 2: Initiate Flash Fill

Press Enter key after typing the first example. Move to the next cell down in the same column.

Step 3: Trigger Flash Fill

Start typing the second example; here, we enter the second first name from cell A3. As you type, Excel will recognize the pattern from the first cell and show a greyed-out preview of the Flash Fill for the rest of the column.

Step 4: Confirm with Enter

If the preview correctly shows what you want, press Enter key to confirm. Excel will automatically complete the rest of the cells based on the pattern it recognized from your initial entry.

Apply Flash Fill with shortcuts

Using shortcuts to apply Flash Fill in Excel is a quick and efficient way to manipulate data. Here’s how you can do it:

Step 1: Start Typing Your Pattern

In a cell adjacent to your data, begin by manually entering the first item of the pattern you want Excel to replicate. For example, if you are extracting first names from a list of full names, type the first name next to the first full name.

Step 2: Initiate Flash Fill

Press Enter key after typing the first example. Move to the next cell down in the same column.

Step 3: Use the Flash Fill Shortcut

Press the following keys on your keyboard:

Ctrl + E

Step 4: Review the Filled Data

Excel will instantly fill the remaining cells based on the pattern you started. It’s a good idea to review the data to ensure that Flash Fill has applied the pattern correctly.

Apply Flash Fill via the Excel Ribbon

Flash Fill is located under the ‘Data’ tab in the Excel Ribbon. Here is how to apply Flash Fill via the ribbon.

Step 1: Start Typing Your Pattern

In a cell adjacent to your data, begin by manually entering the first item of the pattern you want Excel to replicate. For example, if you are extracting first names from a list of full names, type the first name next to the first full name.

Step 2: Initiate Flash Fill

Press Enter key after typing the first example. Move to the next cell down in the same column.

Step 3: Apple Flash Fill in the ribbon

Select Data tab, then go to the Data Tools group, select Flash Fill.

Step 4: Review the Filled Data

Excel will instantly fill the remaining cells based on the pattern you started. Review the data to ensure that Flash Fill has applied the pattern correctly, if not, adjust them manually.

Apply Flash Fill with the Fill Handle Option

Another way to apply Flash Fill is by dragging the fill handle. This method is intuitive and works similarly to copying a formula or value down a column.

Step 1: Start Typing Your Pattern

In a cell adjacent to your data, begin by manually entering the first item of the pattern you want Excel to replicate. For example, if you are extracting first names from a list of full names, type the first name next to the first full name.

Step 2: Use the Fill Handle

Hover your mouse pointer over the bottom-right corner of the cell you type the first example. The pointer will change to a plus sign (+), which is the fill handle.

Step 3: Drag Downward

Click and drag the fill handle down the column, over the cells where you want Flash Fill to apply. Then release the mouse.

In some cases, at this stage, Excel automatically populates the cells correctly based on the first example you entered. However, in most cases, to get accurate filling, you should proceed to the next step.

Step 4: Choose Flash Fill in the Auto Fill Options menu

1. Click at the Auto Fill Options button in the bottom-right of the selected cells.
   
2. Choose Flash Fill option.
   

5 examples for using Flash Fill

Taking advantage of Flash Fill in Excel to tackle various tasks is remarkably efficient. In this guide, we present five scenarios to demonstrate the power of Flash Fill.

• Example 1: Using Flash Fill to extract part of text (Split to columns)
• Example 2: Using Flash Fill to combine cells
• Example 3: Using Flash Fill to add text
• Example 4: Using Flash Fill to change formatting
• Example 5: Using Flash Fill to change string order

Example 1: Using Flash Fill to extract part of text (Split to columns)

Flash Fill is a powerful tool for tasks like separating first names from full names, extracting specific parts of addresses, or isolating numbers from text. It simplifies tasks that would otherwise require complex formulas or manual data entry.

Suppose you have a list of names and ID numbers in column A and you want to extract names in column B.

Here's a step-by-step guide on how to do it:

Step 1: Enter the First Example Manually

Click on cell B2 (next to the first data you want to extract from) and manually type the first name extracted from cell A2.

Step 2: Use Flash Fill:

• Automatically:

  1. After typing the first name in B2, press Enter key to go to B3.
  2. Begin typing the second name. Excel may automatically show a greyed-out preview of the first names for the rest of your list based on the pattern.
     
  3. Press Enter key to accept this Flash Fill suggestion.
     

• Manually:

  If Excel doesn’t automatically suggest the fill, after typing the first name in B2, press Enter key to move to next cell in the same column, then press Ctrl + E. This will prompt Excel to use Flash Fill and fill down the column based on your initial input.

For getting the ID number from the list of A2:A5, repeat above steps to flash fill.

Example 2: Using Flash Fill to combine cells

Flash Fill in Excel is a time-saving tool for tasks like combining names, addresses, or any data split across multiple cells. It eliminates the need for complex formulas or manual copying and pasting, streamlining your data management tasks.

Supposing you have three columns containing names, titles and ages, you want to combine each row in these three columns to one cell, please follow the steps below:

Step 1: Enter the First Example Manually

Click on cell D2 (next to the data you want to combine) and manually type the combination.

Step 2: Use Flash Fill:

• Automatically:

  1. After typing the first combination, press Enter key to go to D3.
  2. Begin typing the second combination. Excel may automatically show a greyed-out preview of the combinations for the rest of your list based on the pattern.
     
  3. Press Enter key to accept this Flash Fill suggestion.
     

• Manually:

  If Excel doesn’t automatically suggest the fill, after typing the first combination in D2, press Enter key to move to next cell in the same column, then press Ctrl + E. This will prompt Excel to use Flash Fill and fill down the column based on your initial input.

Example 3: Using Flash Fill to add text

Using Flash Fill to add text to your data can significantly speed up data entry and ensure consistency across your dataset.

Here we will add “kg” at the end of each cell in range A2:A5, please follow the steps below:

Step 1: Enter the First Example Manually

Click on cell B2 (next to the data you want to add text) and manually type the first example.

Step 2: Use Flash Fill:

Press Enter key to move to next cell in the same column, then press Ctrl + E. This will prompt Excel to use Flash Fill and fill down the column based on your initial input.

Example 4: Using Flash Fill to change formatting

Flash Fill can be used for various formatting changes like converting text to lowercase, title case, or even reformatting phone numbers and dates. It's a powerful tool that can replace manual re-typing or the use of complex formulas for simple text formatting tasks.

Here we want to change the text in range A2:A5 to capitalize first letter, please follow the guide below:

Step 1: Enter the First Example Manually

Click on cell B2 (next to the data you want to change case) and manually type the first example.

Step 2: Use Flash Fill:

• Automatically:

  1. After typing the first example, press Enter key to go to B3.
  2. Begin typing the second combination. Excel may automatically show a greyed-out preview of the combinations for the rest of your list based on the pattern.
  3. Press Enter key to accept this Flash Fill suggestion.
     
     

• Manually:

  If Excel doesn’t automatically suggest the fill, after typing the first example in B2, press Enter key to move to next cell in the same column, then press Ctrl + E. This will prompt Excel to use Flash Fill and fill down the column based on your initial input.

Tip: Or you can apply Flash Fill by select Flash Fill in the ribbon, or dragging auto fill handle and check the Flash Fill Option.

Example 5: Using Flash Fill to change string order

Changing the order of strings in Excel using Flash Fill is an efficient way to reorganize data without manual editing or complex formulas. Here’s how to do it:

Step 1: Enter the First Example Manually

Click on cell B2 (next to the data you want to change the order) and manually type the first example.

Step 2: Use Flash Fill:

Press Enter key to move to next cell in the same column, then press Ctrl + E. This will prompt Excel to use Flash Fill and fill down the column based on your initial input.

Excel's Flash Fill truly revolutionizes data manipulation, significantly reducing time and effort. Whether it's for text reformatting or automating data entry, Flash Fill adeptly handles various tasks. I hope you found this tutorial beneficial. For more handy Excel tips and tricks, feel free to explore further here. Your journey to mastering Excel continues!