See all How-To Articles

How to Use Flash Fill to Fill a Range in Excel

In this tutorial, you will learn how to use Flash Fill to fill a range in Excel.

 

flash fill range names 2

 

If you have a range of data, Excel has the ability to automatically recognize a pattern and fill another column based on that pattern. Let’s go through some examples to explain how Flash Fill works.

Flash Fill

Recognize Part of a Full Name

Say you have a list of full names in Column A (A2:A8) and want to list first names only in Column B.

  1. Enter the first name from the full name in A2. Flash Fill can then recognize a pattern.

 

flash fill names pattern 2

 

  1. Now, select the entered value (cell B2) and, in the Ribbon, go to Data > Flash Fill.

 

flash fill names 2

 

Excel gives you a list of all first names from Column A, as Flash Fill recognizes the pattern based on cell B2. The Flash Fill Options button appears in the right corner.

  1. Click Accept suggestions to keep data or Undo Flash Fill.

 

flash fill names accept 2

 

Recognize Numbers From Text

Flash Fill can also recognize patterns to extract numbers from a text string. In the following example, you want to extract phone numbers from Column A (A2:A7) and enter them in Column B.

 

flash fill numbers pattern 2

 

As in the previous example, you need to select the entered value (cell B2) and, in the Ribbon, go to Data > Flash Fill.

 

flash fill numbers 2

 

Excel automatically recognizes the pattern and extracted numbers in Column B as shown above. It doesn’t matter whether they are in the middle, beginning, or end of the string.