Microsoft Office Tutorials and References
In Depth Information
Troubleshooting Flash Fill
Troubleshooting Flash Fill
The following are some tips for making Flash Fill work correctly:
There can be no blank columns. It is not necessary to be in the
column immediately to the right of the data, but you can t have any
completely blank columns between where you want to Flash Fill and
the source data.
For the automatic Flash Fill to work, you should type the first
value and then immediately type the second value. Do not perform any
other commands between the first and second value. Don t type GG2, go
to Sheet 3, and then come back and type GG3. By then, Flash Fill has
stopped watching for patterns. The only exception is sorting. You
could type GG2, sort, type GG3, and Flash Fill will work.
Type a heading in the column that you are filling to prevent Flash
Fill from filling your heading. You could also bold the other head-
ings. Flash Fill follows the same rules that the Sort dialog and the
Ctrl+T Table dialog use to detect whether there are headings. If
Ctrl+T opens with the My Data Has Headings box checked, then Flash
Fill does not overwrite your headings. This matters more than you
might think, because the headings don t usually follow the pattern
of the data and confuse Flash Fill if it is trying to find a pattern.
Pressing Esc makes the Flash Fill preview go away. More than
once, I ve pressed Esc by mistake and lost the Flash Fill. Don t
worry. Type the first one or two cells and then use Ctrl+E or click
the Flash Fill icon on the Data tab to force Excel to run Flash Fill
Flash Fill only looks for patterns. Flash Fill does not under-
stand that AZ is the abbreviation for Arizona. It does not understand
that Jan 23 is another way to write 1-23. Flash Fill doesn t have
any opinions. Writing Awesome next to Bruce Springsteen does not
cue Flash Fill that you are trying to classify musical acts.
Beware the ambiguous example. In Figure 2.18 , you want to get the suf-
fix from the part code. By random chance, the first example is ambigu-
ous, with IT possibly representing the prefix or the suffix. In this
case, skip B2 and type ZZA in B3. Press Ctrl+Enter and then press
Ctrl+E to fill the entire column (see Figure 2.19 ) .
Search JabSto ::

Custom Search