Mehrab Imtiaz
If you’ve been following recent updates, you will know about the new AI-powered addition to Google Sheets, the Smart Fill.
In this article, we will look at how we can apply and use this special feature to effectively help us in our data entry objectives.
Let’s get started.
What is Smart Fill in Google Sheets?
Smart Fill is an AI-powered autocomplete feature of Google Sheets. It looks for and automatically recognizes patterns during data entry. After which, the Smart Fill suggests autofill results based on the data entry pattern.
You might be thinking: Wait a minute, this sounds familiar.
It is if you’ve used Excel’s Flash Fill feature.
Where Smart Fill differs from Flash Fill is that Flash Fill only suggests the autofill of the plain values of the data entry, whereas Smart Fill generates a formula based on the data entry for the column.
And as we all know, formulas in spreadsheets are dynamic and can easily respond to changes in the referenced data.
How to Use Smart Fill: Autofill First Name Example
The simplest pattern you can produce in a spreadsheet is a list of first names from full names.
Let’s see what happens when we start typing the first names in the adjacent column:
While it did take a few tries, Google Sheets successfully picked up what we wanted to do and generated a Smart Fill suggestion for the column.
In many cases, this recognition of the pattern can be much faster. But understand that it is still a work in progress. It may still take 2-6 tries depending on the type of data.
Clicking on the tick mark will apply the suggestion in the column.
The result:
You may have noticed a formula in the formula bar. Let’s talk more about that in the next section.
Automatically Generate Formula (Autofill Last Name Example)
For this section, we’ve already brought out the Smart Fill suggestion for the last names:
But this time we will click on the “Show formula” link to see the auto-generated formula for this Smart Fill.
The formula that is generated in cell C3 is:
=RIGHT(A3,LEN(A3) - (FIND(" ",A3)))We already know that the RIGHT, LEN, and FIND functions are all text functions of Google Sheets. Meaning that the Smart Fill has correctly guessed that we are simply working with texts for this data entry.
And like all other formulas in Google Sheets, it can be manipulated or filled down the rest of the column as new data is added in the source column (column A).
Remember to Fill the Formula Back Up
This is an important point to note. You will notice that when you autocomplete a column in Google Sheets with Smart Fill, the first row of the column does not contain the formula generated by it.
So, always remember to use the fill handle to auto-fill the first row with the respective formulas generated by the Smart Fill.
Smart Fill with Column Headers
Applying column headers can make it much easier for users to decipher data, and it is also recommended for you to do so.
But surprisingly enough, Smart Fill also takes column headers into account!
For example, in the following image you can see that we have triggered the Smart Fill when entering the first names:
The formula is:
=LEFT(A2,FIND(" ",A2) - 1)Notice that the formula this time starts from the first row of the column (row 2), which is just under the column header.
The Smart Fill recognized the presence of a header and immediately updated the formula to accommodate all the valid rows.
Smart Fill From Different Worksheets in Google Sheets
Let’s try out the same method from a different worksheet.
As the Smart Fill suggestion pops up, we notice something different when we open “Show formula”. The formula has been updated to use VLOOKUP this time. Not only that, but the cell reference generated also points toward the first worksheet that also contains the full names in this arrangement.
The formula is:
=VLOOKUP(A2, Sheet1!$A$2:$B$21, 2, FALSE)While we are getting the autofill values that we want, the formula is not quite what we are looking for. Unfortunately, there is still no way to update what formulas we prefer in Smart Fill.
Also, keep in mind that this is an exceptional case where we are duplicating the method in a different worksheet. This is just to show that Smart Fill is thinking ahead of its users but is still not a perfect feature.
How to Turn Smart Fill On and Off in Google Sheets
Google Sheets Smart Fill is still not a fully understood feature for many users. Some users may even find the sudden pop-ups and suggestions to be inconvenient during work.
Thankfully, you can always turn off the Smart Fill.
You can find the Autocomplete, a.k.a. Smart Fill features from the Tools tab of Google Sheets.
Tools > Autocomplete
Simply uncheck the options that you don’t want interfering with the worksheet. You can always turn them on later.
Notable Limitations of Smart Fill
- The Smart Fill is still a new yet ambitious feature. It still has a way to go to be as visioned.
- Smart Fill takes a couple more tries than Excel’s Flash Fill to recognize patterns.
- The formulas that are generated cannot be controlled.
Final Words
Google Sheets Smart Fill is a wonderful and highly ambitious autocomplete feature. The automatic pattern recognition for formula generation is the first of its kind and it continues to improve.
A big advantage for new spreadsheet users is that they can analyze these generated formulas to better understand how they work in Google Sheets.
Feel free to leave any queries or advice you might have in the comments section below.
