Autofill in Sheets in Google Sheets

Google Sheets is a powerful spreadsheet application that allows you to create, edit, and share spreadsheets online. One of the features that makes Google Sheets so useful is the ability to automatically fill data in cells based on a pattern or a series. This can save you time and effort when entering data or creating formulas.

In this article, we will explain how to use autofill in Google Sheets, and what are the benefits and limitations of this feature. We will also show you some examples of how to use autofill for different tasks, such as creating a list of dates, numbers, or text.

What is Autofill in Google Sheets?

Autofill is a feature that allows you to automatically fill data in cells based on the data in adjacent cells. For example, if you have a cell with the value “January”, you can use autofill to fill the next cells with “February”, “March”, and so on.

Autofill can also be used to fill data based on a formula or a function. For example, if you have a cell with the formula “=A1+1”, you can use autofill to fill the next cells with “=A2+1”, “=A3+1”, and so on.

Autofill can detect patterns and make suggestions to help automate data entry. You can use autofill for tasks like extracting the first name from a given list of full names or finding values in a range or table¹ ².

How to Use Autofill in Google Sheets?

To use autofill in Google Sheets, follow these steps:

On your computer, open a spreadsheet in Google Sheets.
In a column or row, enter data or formulas in at least two cells next to each other.
Highlight the cells. You’ll see a small blue box in the lower right corner.
Drag the blue box any number of cells down or across.
If the cells form a series of dates, numbers, text, or formulas, the series will continue across the selected cells.
If the cells don’t form a series, the list of values will repeat across the selected cells.

Tip: You might notice autofill suggestions with a preview of the values. To accept the suggestion, press Command ⌘ Enter on Mac or Ctrl Enter on Windows/Chromebook¹.

How to Turn Off Autofill Suggestions in Google Sheets?

If you don’t want to see autofill suggestions, you can turn them off by following these steps:

On your computer, open a spreadsheet in Google Sheets.
At the top, click Tools > Autocomplete.
Deselect Enable autocomplete¹ ².

Examples of Using Autofill in Google Sheets

Here are some examples of how to use autofill in Google Sheets for different tasks:

Creating a List of Dates

If you want to create a list of dates in Google Sheets, you can use autofill to fill the cells with sequential dates based on a pattern. For example, if you want to create a list of dates for every Monday in 2023, you can do the following:

In cell A1, enter the date “2023-01-02” (the first Monday of 2023).
In cell A2, enter the formula “=A1+7” (this will add 7 days to the previous date).
Select both cells A1 and A2.
Drag the blue box down until you reach the end of 2023 (or as far as you want).
You will see a list of dates for every Monday in 2023.

You can also use autofill to create a list of dates with different intervals, such as every day, every week, every month, every year, etc.

Creating a List of Numbers

If you want to create a list of numbers in Google Sheets, you can use autofill to fill the cells with sequential numbers based on a pattern. For example, if you want to create a list of numbers from 1 to 100 with an increment of 5, you can do the following:

In cell A1, enter the number “1”.
In cell A2, enter the formula “=A1+5” (this will add 5 to the previous number).
Select both cells A1 and A2.
Drag the blue box down until you reach 100 (or as far as you want).
You will see a list of numbers from 1 to 100 with an increment of 5.

You can also use autofill to create a list of numbers with different increments, such as 2, 10, 100, etc.

Creating a List of Text

If you want to create a list of text in Google Sheets, you can use autofill to fill the cells with sequential text based on a pattern. For example, if you want to create a list of names with the prefix “Mr.”, you can do the following:

In cell A1, enter the name “Mr. Smith”.
In cell A2, enter the name “Mr. Jones”.
Select both cells A1 and A2.
Drag the blue box down as far as you want.
You will see a list of names with the prefix “Mr.”.

You can also use autofill to create a list of text with different patterns, such as suffixes, initials, abbreviations, etc.

Extracting Data from Text

If you want to extract data from text in Google Sheets, you can use autofill to fill the cells with the extracted data based on a function. For example, if you want to extract the first name from a given list of full names, you can do the following:

In column A, enter the full names of some people (e.g., “John Smith”, “Mary Jones”, etc.).
In cell B1, enter the function “=LEFT(A1,FIND(” “,A1)-1)” (this will extract the text before the first space in cell A1).
Drag the blue box down to fill the rest of column B.
You will see a list of first names extracted from column A.

You can also use autofill to extract data from text with different functions, such as RIGHT, MID, LEN, SPLIT, etc.

Finding Data in a Range or Table

If you want to find data in a range or table in Google Sheets, you can use autofill to fill the cells with the found data based on a function. For example, if you want to find the capital city of a given country from a table of countries and capitals, you can do the following:

In column A, enter the names of some countries (e.g., “USA”, “China”, “India”, etc.).
In column B and C, enter a table of countries and capitals (e.g., “USA” and “Washington DC”, “China” and “Beijing”, “India” and “New Delhi”, etc.).
In cell D1, enter the function “=VLOOKUP(A1,B:C,2,FALSE)” (this will look up the value in cell A1 in column B and return the corresponding value in column C).
Drag the blue box down to fill the rest of column D.
You will see a list of capital cities for each country in column A.

You can also use autofill to find data in a range or table with different functions, such as HLOOKUP, INDEX, MATCH, FILTER, etc.

Benefits and Limitations of Autofill in Google Sheets

Autofill in Google Sheets has many benefits and limitations that you should be aware of before using it. Here are some of them:

Benefits

Autofill can save you time and effort when entering data or creating formulas.
Autofill can detect patterns and make suggestions to help automate data entry.
Autofill can work with different types of data, such as dates, numbers, text, formulas, or functions.
Autofill can work with different patterns or series, such as linear, exponential, geometric, or custom.

Limitations

Autofill may not always detect the correct pattern or series that you want.
Autofill may not always make accurate or relevant suggestions for your task.
Autofill may not work with complex or irregular patterns or series that require manual input or adjustment.
Autofill may not work with data that is not available through Google’s public knowledge graph.

Conclusion

Autofill in Google Sheets is a feature that allows you to automatically fill data in cells based on a pattern or a series. It can save you time and effort when entering data or creating formulas. It can also detect patterns and make suggestions to help automate data entry.

However, autofill also has some limitations that you should be aware of before using it. It may not always detect the correct pattern or series that you want. It may not always make accurate or relevant suggestions for your task. It may not work with complex or irregular patterns or series that require manual input or adjustment. It may not work with data that is not available through Google’s public knowledge graph.

Therefore, you should always check and verify the results of autofill before using them for your spreadsheet. You should also use other features and functions in Google Sheets to complement autofill and enhance your spreadsheet experience.

Autofill in Sheets in Google Sheets