This post is not about the Autocomplete feature in Google Sheets (of course I have included that too at the end). That’s built-in to Sheets to speed up your data entry. How to autofill columns to the right in Google Sheets is also about speeding up your data entry, but it’s formula oriented.
I am talking about something like this. Assume you are entering a product code in cell A1, product name in cell B1 and the unit price or some other data in cell C1.
When the same product code repeats in any of the cells down in column A, as an example we can consider cell A6, I want the product name, and the unit price to be auto-filled in cell B6, and cell C6 respectively from the top.
This is what I am talking about.
How to autofill columns to the right in Sheets similar to this illustration?
We can use the function Vlookup in Google Sheets to populate data as illustrated above.
How to Autofill Columns to the Right in Google Sheets
To autofill columns to the right in Sheets, as said above, I am going to use the Vlookup function. Vlookup can use the value in the left column as the search key and lookup the top rows and return matching values.
Sample Data and Formula to Autofill Columns to the Right in Sheets:
The autofill is based on the values on the top. No matter, whether it’s in the Autocomplete built-in feature or my formula oriented autofill columns to the right feature.
What I am trying to say is you need at least one row with data. Then only the autofill columns to the right will work.
That row is row # 2, here in my example. Enter the following formula in cell B3 and drag down up to the row that you want.
=ArrayFormula(IFERROR(vlookup(A3,$A$2:$C2,{2,3},0)))
How this is going to speed up my data entry in Sheets?
Enter item codes in B3, B4, B5… etc. If the formula doesn’t return any values in column B and C, that means you are entering the item code for the first time.
For example, enter the item code C-002 in cell A3. The formula won’t return any value. Here you must manually fill the columns to the right.
Then enter C-001 in cell A4. See the formula fills the cell B4 and C4. No need to go up and copy the data. This way you can speed up your data entry in Sheets.
I have explained how to autofill columns to the right in Google Sheets. Now instead of going to the formula explanation, I am addressing some possible user queries here in this Q&A.
I am not going into the formula explanation because the above formula is easy to learn with my Vlookup tutorials. For that please refer to my Google Sheets Functions Guide.
Autofill Columns and Autocomplete Cells – Q&A
Q. How to autofill more columns?
A. My formula autofills values in two columns only. That’s column B and C. If you have more columns to autofill do as follows.
Suppose you want to autofill the column D and E also. Then change the values (column numbers) within the Curly Braces as below.
{2,3,4,5}Q. How to autofill selected columns, for example, column B and E, in the right side based on the value in the Left Column?
A. That’s not possible with a single formula as above. Since the columns are non-adjacents you must use multiple Vlookup formulas. Change the formula in cell B3 to;
=IFERROR(vlookup(A3,$A$2:$E2,2,0))Then use one more Vlookup in cell E3.
=IFERROR(vlookup(A3,$A$2:$E2,5,0))Then drag both the formulas down one by one. I have removed the Array Formula with Vlookup since the Vlookup only returns a single value, not an array.
Q. Can you explain how to turn on/off the Autocomplete (the built-in feature) in Sheets?
A. Why not? Though it has nothing to do with my formula, here is that info too as a bonus.
The Autocomplete feature is turned on by default in Google Sheets.
Autocomplete turned on:
Autocomplete turned off:
Go to the menu “Tools” and click on “Enable autocomplete” to remove the check mark. It will turn off the feature.
That’s all. This way you can autofill columns to the right in Google Sheets and also autocomplete cells. Enjoy!
Similar:
