Google Sheets Smart Fill

Google Sheets’ “SmartFill” is a new feature that closes the gap between Excel and Sheets in terms of functionality.

Like Excel’s FlashFill, SmartFill detects and learns patterns between columns to intelligently auto-complete data entry.

This feature is ideal for fixing “bad data” or changing data from one format to another.

Is it as good as Excel’s Flash Fill?  Could it even be better?  Let’s find out.

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.

MASTER GOOGLE SHEETS

GET THE COMPLETE COURSE

Learn anytime that fits your schedule.

Complete the Challenges. Practice. Apply.

Get Access