Recently Google Sheets introduced an AI-powered option that can reduce the tediousness of doing repetitive data entry tasks.
In October 2020, Google rolled out the very useful Smart Fill feature in Sheets.
Developers powered the Google Sheets Smart Fill feature with Artificial Intelligence that recognizes the patterns present between your provided examples and existing data columns then suggests an intelligent autofill to complete the remaining data entry.
This feature was announced in June 2020, and Google promised us that it would bring a revolution into the data entry world!
Through this article, you will learn different ways you can use the Smart Fill option in Google Sheets to make your data entry super easy.
Download the example workbook and follow along to find out how! 😉
What Does the Smart Fill Do?
Whenever the Smart Fill detects a pattern, it searches through a vast library of formulae for a specific one that can do this task.
It then suggests that the task can be done using a specific formula, and if you accept the suggestion, it fills the remaining column with that formula.
You usually only need to show a few patterns to it to make it work, and Google replicates those through the entire column.
Sometimes, one or two examples of the desired output will be enough for AI to detect and suggest a way of obtaining the results across the entire dataset.
However, it can take longer, and you will have to provide examples until that time.
As shown in the example above, it took just two entries for Smart Fill to Identify the pattern. The third step suggested a formula that could do this task for you.
📝 Note: The feature can sometimes fail to understand the pattern if it is very complex. In that case, it will no longer provide you the suggestions.
Where to Find the Smart Fill Options?
You can enable Smart Fill through the Tools menu from the ribbon. Click on the Tools menu ➜ Autocomplete. Once you click on Autocomplete the submenu will open and there are three options.
- Enable autocomplete
- Enable formula suggestions
- Enable formula corrections
You can select all these options to enable Smart Fill. However, you can enable any of these three options if you want. The choice is yours, but the feature will work best with all items enabled!
How Can You Use Smart Fill
It’s easy to use the Smart Fill suggestions inside Google Sheets. Follow the steps below.
- The data you use for Smart Fill should be in columns that are to the left of the column where you are going to apply the feature. Please keep in mind that the data and result columns should not have any blank column inbetween. They should be adjecent to each other.
- Based on the data from left, the output column or result column should be filled manually for first two or three rows. This will allow the AI engine to understand the pattern and find a formula that replicates it.
- Finally, Smart Fill will show you the preview of the result with the Auto Fill or Smart Fill window suggestion. You can check what formula it is using to generate the result. If you are ok with the suggestions, you can click on the checkmark to accept it, or you can reject it with the X button.
📝 Note: There is also a shortcut key to accept the suggestions provided by Smart Fill. You can use the combination of Ctrl + Shift + Y keys from your keyboard to accept the suggestions on Windows. If you are on iOS, use ⌘ + Shift + Y to accept.
Examples: What Can You Do With Smart Fill
The Smart Fill tool will allow you to fill data based on a specific pattern.
But it is not limited to this single task. You can actually do a lot with the Smart Fill in Google Sheets.
Here are some examples of what can be done with Google Sheets Smart Fill.
Extract First and Last Name from Full Name
Working on a data entry project will be a frequent situation for a lot of users.
You might have a column of data where the first and last names have been joined together into a Full Name, and you wish to have the First and Last Name separated into two different columns.
There are many ways to split text in Google Sheets, but the easiest method by far is to use Smart Fill.
In this example, you have a Full Names of 20 individuals, and you want to extract the First and Last Names from those in two different columns.
- To get started, in cell B2 type the first name Josephine for the first entry.
- keep typing the first names for subsequent rows until Smart Fill finds a pattern. Usually, the AI finds a pattern after first two or three entires. But in some cases, it might take longer.
- As soon as the AI recognizes a pattern, it will open up the Smart Fill window with an output preview across all 20 rows of column B. You can see what formula the AI is using to separate the first name. If you are happy with the suggestions, click on the checkmark to accept these suggestions else you can hit the X to reject the suggestions. If rejected, the AI will not provide you any further suggestion for that column.
Do the same procedure as above to extract the last name from the text in column C.
= LEFT ( A3, FIND ( " ", A3 ) - 1 )
The formula Smart Fill uses to separate the first names is above.
= RIGHT ( A6, LEN ( A6 ) - ( FIND ( " ", A6 ) ) )
Above is the formula Smart Fill uses to fill the last names in column C.
📝 Note: Even if Smart Fill uses a formula to fill the cells, the first cell you typed manually is not converted into formulas. The next example will show you how you can fill the formula up automatically as well.
Extract First and Last Name from Full Name With Column Headers
In the previous example, you have seen that the Smart Fill doesn’t apply the formula from the first row when extracting names.
However, if you use column headers for the first and last name columns, the AI will fill the entire columns with formulas starting from the first row.
Follow the steps from the previous example to extract the first and last names through Smart Fill.
As you can see in the screenshot above, the first and last names are extracted from column A.
The only difference is after you accept the suggestions provided by Smart Fill, the formula used is populated from the first row in columns B and C.
This will make your entire data extraction dynamic. If next time you have a different set of full names, you can just replace the values in column A and columns B and C will update the first and last names automatically because they are all formulas.
💡 Tip: Always use column headings in your datasets, this way the formula will be populated across the entire column.
Combine First and Last Name to Create Full Name
Now imagine a situation that is precisely the reverse of the previous one.
You have the first and last name in two separate columns, and you want to combine them both to form a full name.
You could use various functions or the ampersand operator to do this task. But Smart Fill is also equally capable of doing the task for you.
- Similar to the first example, type the first few names for the system to understand the pattern to fill the cells. Here, the first and last names are combined together with a comma and a space as delimeter.
- Type the names for first two or three cells. Once Smart Fill recognizes your typing pattern, It will autofill the remaining cells with a formula that combines the first and last names.
= CONCATENATE ( A2, ", ", B2 )
The data entry pattern is such that the first and last name are combined together with comma and space as a delimiter together, and hence the formula with which the AI fills is as above.
📝 Note: Even if the formula mentioned above is for the second cell, keep in mind that it is getting filled across rows, and at each occurrence uses a relative cell reference that changes to the current row.
Convert Full Name to Name with Initials
You may prefer to use initials instead of full names and Google Sheets Smart Fill can help you in this task as well.
Smart Fill can help you convert the full name into initials complete with periods. It doesn’t matter if you have a full name in a single column or the first and last name stored in two different columns. It works perfectly in both these situations and fills your data.
If you are new to Sheets and don’t know much about the formulas that can be used to work dynamically, this tool will definitely help you learn the formulas it suggests.
You have to feed it a pattern for the first two or three cells like the previous examples. The pattern is pretty simple. Initials of first and last names are combined together with a period as a delimiter.
= CONCATENATE ( LEFT ( A2, 1 ), ".", LEFT ( B2, 1 ), "." )
The Smart Fill identifies this pattern and then fills all the cells with the above formula that does this task.
=CONCATENATE(LEFT(A4,1),".",MID(A4,FIND(",",A4) + 1 + 1,FIND(", ",A4) + 2 - (FIND(",",A4) + 1)),".")
If you have a full name, the Smart Fill can still identify this pattern and returns the initials by using this formula instead.
This formula is quite long, and believe me, it would take a while to write it yourself!
But that is the beauty of the Smart Fill, right? It’s saved you from writing such a monster formula!
Change Any Text to Upper Case
Smart Fill can also help you in situations where you want to change the case of your data.
Using this feature, you can change the entire sentence into uppercase where all words are in capitals.
You will need to type the first few sentences manually and Smart Fill will work and suggest a fill.
= UPPER ( A2 )
The formula Smart Fill uses is quite simple in this case.
Change Any Text to Lower Case
Smart Fill can convert a sentence into the lower case as well!
= LOWER ( A2 )
Type the first few examples until it recognizes the pattern and what you are trying to do. Once it understands the pattern, Smart Fill will complete the entire column with the above formula.
Change Any Text to Proper Case
Suppose you want to change the text into a proper case so that the first letter of each word should be a capital. For such cases, Smart Fill helps as well. It can convert the text into a proper case and fill the cells accordingly.
= PROPER ( A2 )
You can type a few sentences of the pattern to feed the AI, and it will use the formula above to convert subsequent texts into a proper case.
📝 Note: Sometimes in complex sentences with questions, exclamation marks, or standard contractions, this feature doesn’t understand the pattern and fails to suggest any formula to fill the columns.
Extract the Company Name from an Email Address
Consider you have data with a list of emails for employees.
You want to extract the domain or company name out of all those emails.
Google Sheets Smart Fill can help you extract the domain or company name from an email address.
= MID ( A2, FIND ( "@", A2 ) + 1, FIND ( ".", A2 ) - 1 - ( FIND ( "@", A2 ) ) )
For the first few emails, type the company name manually. The tool will recognize the pattern and ultimately fill all the rows with the above formula.
💡 Tip: You can also extract the full name from an email id that is in first_name.last_name@domain.com
format. Try it yourself and see how a Smart Fill can write a complex formula for you.
Creating an Email Address From First and Last Name
Suppose you need to generate unique email IDs for your team members based on their first and last names.
The email domain remains the same for each email and the same format is used. Smart Fill can help you with this task.
= CONCATENATE ( A2, ".", B2, "@oksheets.com" )
Write the first few emails with the company domain, and Smart Fill will populate the result across all the cells with the help of the above formula.
Data Cleaning With Smart Fill
Suppose you have data as shown in the screenshot above, and you want to separate different components of the Address into different columns.
You could try to do this with various formulas, or you can try Smart Fill to get this done.
To extract the City details follow these steps.
- In column D (City column), start typing names for first few cells. In the Address column, cities appear after the street addres and are separated by a comma and space.
- Smart Fill will understand the pattern that you are trying to extract the second component of the Address and suggest a formula.
- It will then populates all cells with a formula that can extract the City name from the entire address.
=MID(C2,FIND(",",C2) + 1 + 1,FIND(CHAR(160),SUBSTITUTE(C2,",",CHAR(160),2)) - 1 - (FIND(",",C2) + 1))
Above you can see the formula that is generated for this task.
To extract the Province details.
- You will again need to type a few Province names manually for system to understand the pattern.
- The Smart Fill tool then automatically identifies the pattern and fills the cells with a formula.
=MID(C2,FIND(CHAR(160),SUBSTITUTE(C2,",",CHAR(160),2)) + 1 + 1,FIND(CHAR(160),SUBSTITUTE(C2,",",CHAR(160),3)) - 1 - (FIND(CHAR(160),SUBSTITUTE(C2,",",CHAR(160),2)) + 1))
The above formula is generated to extract the Province details.
= REGEXEXTRACT ( C2, "[A-Z]{2,}" )
Similarly, you can extract the State details from the Address. The formula Smart Fill uses is as shown above.
Notice that it uses a REGEX function and will even generate the needed RegEx expression string!
Extract Text Before Numbers in a Alphanumeric Text
If you have an alphanumeric text and you only want the letters before the numeric value, you can use the Smart Fill option to get those.
= REGEXEXTRACT ( A2, "[A-Z]{2,}" )
As usual, start typing for the first few rows and after that Smart Fill will automatically detect the pattern to fill in the remaining cells with the above formula.
Remove Extra Spaces From Sentences [TRIMING]
If you have sentences with extra space between words, it is tedious to search and remove them manually. Smart Fill can help you remove these extra spaces.
= TRIM ( A2 )
Just type the first few sentences correctly, and then Smart Fill automatically recognizes the pattern and fills the cells with the above formula to remove the additional spaces.
These are just a few ways of using Smart Fill in Google Sheets.
Benefits of Using Smart Fill
- If you use Smart Fill, you can save all the time it would take to fill the columns manually.
- The AI Powered feature can provide you the required formula with just a few examples provided.
- It makes data entry jobs dynamic. Next time you have the same task, you just need to change the data from first column and remaining columns will automatically update based on the dynamic formula.
Potential Remedies if Smart Fill Fails
- If you see that Smart Fill cannot provide you with a solution, keep a close eye on your typing. Even a small spelling mistake, case error, one additional space etc. can cause this feature to be unable to find the pattern.
- If you can’t any errors in the examples you have typed, it’s sometimes best to delete the examples and start it over again. Spotting a mistake can be difficult at times.
- Don’t expect it to work well based on a single example. A pattern might be hard for AI to understand with only one example and it might provide the wrong formula. Always provide at least three examples.
- If it doesn’t automatically generate a preview of filled rows, you might need to enable the feature.
Drawbacks of Smart Fill
- The Smart Fill tool is relatively new to Google Sheets. The AI powered feature, is still in development and might fail due to inability to recognize the pattern. However, it will get better as time goes on.
- It currently doesn’t work well with number and date data in text. Right now it’s not able to extract numbers or dates from the text.
- Smart Fill still fails at reletively simple transformations such as when you want to combine text with line breaks.
Conclusions
The Google Sheets Smart Fill is a very useful tool to have for your data entry operations.
It provides dynamic ways to tackle various data transformations and saves time by filling cells for you.
However, it is in the early days, and maybe after a few more years, it will have solutions for other data-related questions.
What do you think about this ultimate guide to Smart Fill? Have you discovered any other tricks with Smart Fill? Let me know in the comments below!
0 Comments