We will continue with more usage scenarios for Flash Fill.
Flash Fill feature works only with Excel 2013 and later.
Please read the article Miraculous Automatic Data Cleanup in Excel before reading this article.
Now that we have understood the concept of how Flash Fill works, let us explore more scenarios.
Contents
Exceptions
Consider this flash fill.. from a recipe for French Dressing.
If you try to correct the bracket of the ounce, then it will provide wrong type of information to the Flash Fill logic and everything will go wrong.
In such cases, just go and change the bracket and DO NOT accept the changes. Just treat it as an exception and leave it at that.
Use the Undo Flash Fill Revision option to remove the changes done. This will also undo the manually corrected cell. So we are back to square one!
As long as you see that icon, you are in flash fill mode. We want to get rid of that Flash Fill mode so that we can correct the exceptions manually.
How do you come out of Flash Fill mode?
The only way available seems to type something outside the range. Now the flash fill icon disappears and you can make the required change without triggering another flash fill correction.
Date conversion
This is a very powerful usage. But you must use it correctly. Otherwise you will get confused. Here is an example, with flash fill already applied after typing the first example.
You will think last two dates were converted but something went wrong with rows 2 and 3. That is NOT the case. You are absolutely wrong here.
Date conversions depend upon the date format set using Control Panel – Regional Settings. In this case, the date format was mm/dd/yy. Therefore, when the first date example was typed as 23/12/2001, flash fill understood that this is supposed to be a date conversion.
It tried to do that by changing the dot to slash and then using mm/dd/yy for all rows. In case of row 2 and 3, the conversion worked- but the date and month component got reversed. We cannot see it here. But it has happened. This is absolutely wrong.
The row 2 and 3 was kept as a number because there is no date formatting applied to these cells. If you apply date formatting, you will see the mistake clearly.
11 Nov is safe because it has 11 in month and day position. However 3rd Feb became 2 March. Very tricky and dangerous.
A quick visual indication is that anything left aligned is text and right aligned are numbers. Dates are numbers. Therefore, if you are expecting dates in any column, all data should be right aligned.
So the concept is simple. First find out what is the control panel – regional settings date format. And then type the example date in THAT FORMAT. Now it will work as expected.
Removing unwanted spaces
This is easily done using Flash Fill. No need to struggle with coding / complex formulas.
Conditional Execution
This is a very powerful usage scenario. This is useful when data is not consistent.
The first row item Nitin(Mgr) was manually typed and the rest is flash fill.
Obviously, if the name or the designation is not available, the cell should be left empty. It should not attempt to do the concatenation.
Now, how do you inform this to Excel? While in Flash Fill mode, if you go and delete the third row Alex() value, it does not understand the logic.
Instead, put a dummy value in the wrong result area as follows and then it understands the logic instantly.
Here the first asterisk is manually typed. The rest of them were managed by Flash Fill. By adding that asterisk, we informed Excel that if Designation is empty, then don’t bother to add up the columns. Just put this character.
Now we have another variation where the Name is missing and Designation is available. So one last time we have to given another example and then things work perfectly.
Later, you can search and replace toe asterisk with blanks.
Merging columns
This is easily done.
Notice that we achieved two things here. One was to combine column 1 and 2 into column 3. But while doing so, we also created abbreviation or acronym from the name by picking up the upper case characters.
Deduplication
This is specially good with Pin Codes, Phone Numbers and the like where numeric data and noise has to be separated out.
Notice Flash Fill Mode
Whenever the Flash Fill icon is visible – you are in Flash Fill mode. In this mode, changes you make to the output cells are used as additional input for pattern matching and the Fill process is reapplied. This is like a live interaction happening between you and the mind of Flash Fill.
If you type anything outside the Flash Filled range, the icon disappears and then you are in regular Excel editing mode. Now whatever changes you make to the flash fill output will not be acted upon.
Flash Fill Keyboard Shortcut
Ctrl E is the keyboard shortcut for Flash Fill.
Flash Fill is NOT a formula
This is a very important thing to understand. Flash Fill is applied to the data at that point of time. It does not track whether the data changed after it was applied. Whenever you edit anything outside the context of Flash Fill, it disables itself. Therefore changes to the data do not reflect in the output any longer.
Flash Fill Usage Operating Procedure
- Make sure all users in your organization know about this feature and have detailed knowledge of its usage. You can point them to these two articles if required.
- Whenever a data clean up requirement comes up, check if Flash Fill can do it.
- For large data, it may take some time to fill all the rows. Therefore, try it on a subset of data before applying it to large sets.
- Be careful with revisions and iterations. When you change the input by providing another example, many outputs may change. It is important to preview those to understand whether there has been an unwanted side effect. This is done using the Highlight feature and then checking the highlighted cells.
- You need not provide the example on the first row of the data. Type it in any row which makes more sense and then use the Flash Fill
- Remember that Flash Fill is NOT automatically applied when more data is added at a later point of time. You will need to do this manually. If you already data which was Flash filled earlier and then you want to append more data, it may make more sense to create the flash filled columns BEFORE the append process.
- Flash fill is NOT a formula. Therefore, if the original data changes after Flash Fill has been applied, the original output does NOT change.
We will continue to explore more data cleanup scenarios in the next article.
Blood Moon
Let me leave you with a nice picture of Blood Moon astronomical phenomenon which happened yesterday. Image from NASA.
