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.
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.
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.
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.
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.
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.
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.
If you don’t get the solution to your question in this article or you are interested in learning more basic tips in Google Sheets, you may find the answers in the following articles.
Move onto the articles below to learn how to use Google Sheets, such as signing in, renaming, saving and printing a file, setting up notifications, changing themes and auto-completion.
Go to the following articles to learn basic operations, such as redo and undo, referencing a cell on another sheet in a formula, inserting a table, and putting an image into a cell.
Check the following posts if you want to learn how to insert, modify, and remove comments and note or tag someone in your comment.
Read these articles to learn how to show formulas, missing toolbar, or formula bar in Google Sheets.
Visit these articles to learn how to protect cells and view protected cells or ranges.
Are you learning this tip to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows and update numbers in real-time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. You can also customize these templates as you want without breaking the automated data inflow.
To learn more about LiveFlow, book a demo.
Learn how to do this step-by-step in the video below 👇