Flash Fill Was Not Perfect

November 11, 2022 - by Bill Jelen

Problem: Some people in my data set do not have middle initials. Flash Fill is putting periods between the first and last name for those people.

Your data is missing some people's Middle Initials. The Flash Fill answer looks bad, with Rose space period space Mason. — Figure 816. Rose Mason is missing a middle initial, but Flash Fill still adds the period.

Strategy: After the initial Flash Fill, find a record and type the correction. Flash Fill will kick in again, correcting items that match the new pattern. Watch the Status Bar in the lower left to see how many records were corrected.

After manually correcting to Rose Mason, an on-grid drop-down appears that offers these choices:
Undo Flash Fill Revision
Accept Suggestions
Select All 0 Blank Cells
Select All 4 Changed cells. — Figure 817. Flash Fill watches for you to make the first correction.

After the correction, open the dropdown. You can select all of the newly changed cells. Use the Home tab to apply a fill color, so you can check the corrected cells.

Problem: Flash Fill is not working with numbers. Actually, I see the grey numbers appear, but then Excel erases them.

Strategy: Chad Rothschiller at Microsoft points out there are too many coincidences that can happen in numbers. With only 10 digits, Chad might think he knows the pattern but then screw up your data. So, Chad shows you the preview, but then withdraws it. It is up to you to go to the blank next blank cell and press Ctrl+E.

This article is an excerpt from Power Excel With MrExcel

Title photo by Miikka Luotio on Unsplash