Microsoft Office Tutorials and References
In Depth Information
Tip 64: Using Flash Fill to Extract Data
Using Flash Fill to Extract Data
When you import data, it’s often necessary to clean up some of the text. For example, names may
appear in uppercase that they should be in proper case. One approach is to use formulas to modify
the text (see Tip 57). Another approach uses a feature introduced in Excel 2013: Flash Fill.
Flash Fill uses pattern recognition to extract data (and also concatenate data) from adjoining
columns. Just enter a few examples in a column that’s adjacent to the data, and then choose Data➜Data
Tools➜Flash Fill (or press Ctrl+E). Excel analyzes the examples you typed and attempts to fill in the
remaining cells. If Excel didn’t recognize the pattern you had in mind, press Ctrl+Z, add another
example or two, and try again.
Changing the case of text
Figure 64-1 shows a list of U.S. presidents in column A. Column B shows the result of using Flash Fill
to convert the text to proper case.
Start by providing a few examples: Type George Washington in cell B1 and John Adams in cell B2.
You’ll notice that Excel kicks in as soon as you start typing John Adams. It recognizes your pattern
(which is “make all text proper case”) and fills the column with the transformed text (in a light gray
color). You can press Enter to keep Excel’s suggestion, or continue typing more examples. At any
time, you can press Ctrl+E to have Excel fill the column.
Figure 64-1: Flash Fill quickly converted the names in Column A to proper case.