How Flash Fill Minimizes Retyping Data in Excel 2013 and Laterby
Retyping data that’s already in a spreadsheet can often be an exercise in frustration. Fortunately, the Flash Fill feature in Excel 2013 and later is watching as you work and, in certain cases, will offer to fill a column for you when it perceives a pattern in your data-entry work.
The feature is nuanced, so I’ll show you what to look for and how to force Excel to try to Flash Fill data on demand when it doesn’t offer to do so on its own.
As shown in Figure 1, you may have a list of names that need to be separated into two columns. Depending upon the length of the list, you may decide it won’t take long to just retype the data. If you embark on doing so in Excel 2013 and later, there’s a chance Excel will offer to complete the column for you.
To trigger an automatic Flash Fill, you must work down a single column. For instance, if you type a first name and then a last name into two separate columns, you won’t see the Flash Fill option. If you instead work down a single column, you may see a preview of the completed column when you get to the second cell. You must type slowly to do so, however, as this preview shows up fleetingly.
As shown in Figure 1, I started by entering the first name into cell B2. If I then move to cell B3 and start typing the next name, Excel offers to complete the series. Flash Fill only works on a single column at a time, and there must be a perceptible pattern to your work. If Flash Fill doesn’t trigger on its own, you can initiate it by choosing the Flash Fill command on Excel’s Data menu or by pressing Ctrl-E.
Flash Fill has the potential to save you time, but it’s an imperfect feature. You’ll notice in Figure 1 that Flash Fill chose “Jr.” as the last name in cell C4 and only captured the first part of the hyphenated name in cell B6. Be sure to review your work to ensure that the results end up as you intend.
Figure 1: Flash Fill saves you from manual data entry in Excel 2013 and later.
You’ll have two visual indications that Flash Fill has done its work. As shown in Figure 2, once Flash Fill has occurred, an icon will appear on-screen, from which you can choose to undo the fill or accept the changes. If there are any blank cells within your data, you will be able to select those cells.
A second indication will appear in Excel’s Status Bar; the bottom row of the Excel screen that typically contains the word Ready in the lower left-hand corner. A prompt will appear to confirm the number of cells affected, and you can click this prompt to select the affected cells.
Figure 2: Two on-screen indications confirm that Flash Fill has worked.
If Flash Fill can’t be applied to your data, you may encounter an audible sound or visible prompt. In some cases pressing Ctrl-E will result in an audible ding, signifying that the feature doesn’t see anything to do, or you may encounter the prompt shown in Figure 3.
Figure 3: This error prompt clarifies the conditions under which Flash Fill is available.
Flash Fill’s capabilities don’t stop at just autocompleting data though. You can also use it to transform data, as shown in Figure 4. Flash Fill will also extract data, such as initials from a list of names or text before the ‘@’ symbol in email addresses.
Figure 4: Flash Fill enables you to alter data.
Finally, Flash Fill doesn’t just work with textual data. You can also use it to apply formatting to numerical data, such as telephone numbers and Social Security numbers, as shown in Figure 5.
Figure 5: Flash Fill enables you to transform numbers, as well.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.