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. (It’s only one of the ways to automatically fill cells.) 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 at the middle, beginning, or end of the string.