Autofill Function in Google Sheets: Explained
In this article, you will learn about four autocomplete functions in Google Sheets.
If you enable autofill functions, Google Sheets guess what your want to do and helps you fill in cells with the right values, insert suitable formulas, and revise incorrect formulas.
How to turn on and off Autofill Functions in Google Sheets
- Navigate to the “Tools” tab → “Autocomplete”
- Check (to turn on) or uncheck (to turn off) each function depending on your preference
What is Autocomplete and how to use it in Google Sheets?
The Autocomplete function helps when you want to fill cells with the same formula as the feature gives you autofill suggestions with a preview of each value. If you want to accept the suggestions, click the check mark in the pop-up or press “Ctrl”+”Enter” (for Windows) or “⌘”+”Enter” (for Mac).
So, this function save your time a little as you don’t need to copy and paste a formula into other cells by yourself. If you feel this feature is useless, you can switch off it by unckecking “Enable autocomplete” in the menu.
Apart from the function above, Google Sheets allows you to fill in other cells automatically based on a series of existing values under a certain regularity.
- Type in a value such as text, numbers, or dates in at least two cells next to each other in a row or column. To use the Autocomplete, the cells must be a series of values (e.g., 1, 2, and 3) lacrosse the selected range.
- Select the cells based on whose values you want to fill in other cells.
- Drag the small blue box at the bottom right corner of the selected range any number of cells down or across or just double-click the small blue box.
For example, in the sample table in the picture below, cells C5, C6, and C7 are to be filled with values of 102, 103, and 104, respectively.
What is the Formula Suggestions and how to use it in Google Sheet?
The Formula Suggestions function shows its recommended formula(s) based on values surrounding the cell you are going to input a formula. The suggestions pop up when you type “=” sign in the cell. If you go with the suggestion, press the “Enter” key, and if not, keep typing the formula you originally wanted to insert.
Look at the following example. Assume you are going to calculate the total sales amount from Customer A to E. As you can see, when you input “=”, the recommended formula shows up with a computation result by the formula. This is how the function works.
As we mentioned in the beginning part of this section, this function change suggestions according to the values around the cell you are going to type in the formula.
As you can see in the picture below, in which all values are unchanged except for one in cell B15 from the previous example, because we added “Average” in cell B15, the suggestion from Google Sheets has changed from the SUM formula to the Average function.
What is the Formula Corrections and how to use it in Google Sheets?
The Formula Corrections function helps you to identify and modify an irregular formula, and suggest the correct formula by taking into account the formula in surrounding cells, columns, or rows.
If you have vertically or horizontally input a series of values based on a certain formula and suddenly insert a different formula, the function identifies the irregular formula and give you suggestion on how to revise it.
For instance, in the following picture, we have input two formulas of addition 200 to a value one cell above in cells C11 and C12. If you insert a formula of multiplying 200 by a value one cell above in cell C13, the Formula Corrections shows the seemingly right formula to fix the potential mistake.
What is the Named Functions Suggestions in Google Sheets?
This function works similarly to “Formula Suggestions” but as an essential assumption, you need to register Named Functions in your Google Sheets beforehand. Check this article to learn Named Functions.
Conclusion
These functions are very helpful when you want to input data or formulas by following a certain rule or regularity as the functions help you fill cells with suitable values or formulas, or identify and revise incorrect input.
However, if you need to insert various formulas or input different values, it may make sense for you to turn off the function as seeing suggestions every time may be irritating and annoying for you.
