Microsoft Office Tutorials and References
In Depth Information
Learning some handy data-entry techniques
case, the Flash Fill feature might save the day. But keep in mind that Flash Fill works
successfully only when the data is very consistent.
Flash Fill is a new feature in Excel 2013.
Flash Fill uses pattern recognition to extract data (and also concatenate data). Just enter a
few examples in a column that’s adjacent to the data, and choose Data
Fill (or press Ctrl+E). Excel analyzes the examples 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.
Figure 13.8 shows a worksheet with some text in a single column. The goal is to extract the
number from each cell and put it into a separate cell. The Text to Columns Wizard can’t do
it because the space delimiters aren’t consistent. It might be possible to write an array
formula, but it would be very complicated.
The goal is to extract the numbers in column A.
To try using Flash Fill, activate cell B1 and type the ﬁ rst number ( ). Move to B2, and type 20
the second number ( ). Can Flash Fill identify the remaining numbers and ﬁ fill them in? 6
Choose Data ➪ Data Tools ➪ Flash Fill or Home ➪ Editing ➪ Fill ➪ Flash Fill (or press Ctrl+E)
and Excel ﬁ fills in the remaining cells in a ﬂ ash. Figure 13.9 shows the result.