Part IV: Managing and Analyzing Data
FIGURE 25.15
After you enter an example of a decimal number, Excel gets all of the values correct.This simple example demonstrates two important points:■ (^) You must examine your data carefully after using Flash Fill. Just because the first
few rows are correct, you can’t assume that Flash Fill worked correctly for all rows.
■ (^) Flash Fill accuracy increases when you provide more examples.
Figure 25.16 shows another example: names in column A. The goal is to extract the first,
last, and middle names (if it has one). In column B, Flash Fill successfully gets all of the
first names using only two examples (Mark and Tim). Plus, it successfully extracted all
of the last names (column C) using Russell and Colman. Extracting the middle names or
initials (column D) didn’t work until examples that included a space on either side of the
middle name were included.
To summarize, Excel’s Flash Fill is an interesting idea, but it works reliably only if the data
is consistent. Even when you think it worked correctly, make sure that you examine the
results carefully. And think twice before trusting it with important data because there’s no
way to document the way the data was extracted. But the main limitation is that (unlike
formulas) Flash Fill is not a dynamic technique. If your data changes, the flash-filled col-
umn does not update.
You can also use the Flash Fill feature to create new data from multiple columns. Just provide a few examples of the
way that you want the data to be combined, and Excel will figure out the pattern and fill in the column. Using Flash
Fill to create data seems to work much better than using it to extract data. But then again, it’s also easier to create
formulas that create data from existing columns.