In this tutorial, you will learn how to use Flash Fill to fill a range in Excel.
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.
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.
- Enter the first name from the full name in A2. Flash Fill can then recognize a pattern.
- Now, select the entered value (cell B2) and, in the Ribbon, go to Data > Flash Fill.
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.
- Click Accept suggestions to keep data or Undo Flash Fill.
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.
As in the previous example, you need to select the entered value (cell B2) and, in the Ribbon, go to Data > Flash Fill.
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.