Smart Fill in Action
We have a list of names…
… but we need to sort the list by the last name. To do this, we would need to separate the first and last names into separate columns.
Without using a variety of text manipulation functions like Left, Mid, Right, Len, Search, Find, Replace, etc., we can use SmartFill.
The key here is to provide an example of how you WISH your data were structured.
In the column directly to the right of the data, click the cell next to the first entry in the list and type the first name. Next, type the first name of the next entry in the list.
NOTE: Depending on the data, you may be required to provide between 2 and 6 examples for SmartFill to determine the needed strategy to fulfill the request.
Sheets’ SmartFill has presented us with a list of what it thinks we would like to continue if we were typing the entire list manually.
To see the underlying formula that has been created to achieve such a result, click the Show Formula link.
This displays the formula that will be used to generate the results.
To accept the results, click the Green Check button or press CTRL-Shift-Y. The cells have been filled with the needed formula.
Take a look at the formula you DIDN’T have to write.
=LEFT(A1, FIND(" ", A1) - 1)To create the list of last names, repeat the process but by typing a few last names as an example.
The formula for generating the last names is not one that most people would enjoy writing.
=RIGHT(A1, LEN(A1) - (FIND(" ", A1) ) )Smart Fill in Action
The above example showcases a key difference between Sheets’ Smart Fill and Excel’s Flash Fill.
Where Smart Fill creates a formula in the cell that you can modify and perhaps use as a learning tool to discover how data is manipulated formulaically, Excel’s Flash Fill only produces the results as plain data, as if you had manually typed the data.
Having a formula means that the results are dynamic. If the source data changes, such as name changes or a spelling error is corrected, the spawned data will update.
With Excel’s static results, changes in the source data would require a complete recreation of the results using Flash Fill or at least a manual update of spawned results.
!!! BEWARE !!!
Because the inserted formula began in the cell where the pattern was discovered, any cells above that will only contain manually typed data.
You should repeat the formula “up the column” to convert the manually entered data into dynamic results.
Discovery Across Sheets
When working with a table that has headers, we gain a few abilities that we didn’t have in the previous examples.
The first ability sends the formula to all cells in the column up to the header row.
Because the table has headers, SmartFill is assuming the items above should also have the formula.
But that’s not the most interesting feature. Examine the formula created by SmartFill.
=VLOOKUP(A2, Sheet1!$A$1:$C$24, 2, FALSE)SmartFill has performed a lookup operation on the results from the previous example.
The unfortunate part of this is that if we wished for the “Left/Find” formula solution, we don’t get the opportunity to express a preference.
Turning the Smart Fill Feature On and Off
If the Smart Fill feature is failing to operate, or you do not wish to use the feature, you can check the operational status by selecting Tools -> Enable Autocomplete.
SmartFill Limitations
There appear to be several scenarios where SmartFill cannot resolve examples that Excel’s Flash Fill discovers easily.
SmartFill is still in its infancy, where Flash Fill has been around for seven years. Flash Fill has had a bit of a head-start but I’m certain SmartFill will catch up sooner than later.
Conclusions
- Smart Fill creates and applies formulas for dynamic results.
- Considers data from other sheets when discovering patterns.
- Cannot handle more complex patterns…yet.
Practice File
Get your own copy of the file to practice HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
