Arrow left
Back to product guides
Google Sheets Tips

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

  1. Navigate to the “Tools” tab → “Autocomplete”
  2. Check (to turn on) or uncheck (to turn off) each function depending on your preference
How to turn on and off the “Autocomplete” functions in Google Sheets

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.

What Autocomplete feature looks like in Google Sheets

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.

  1. 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.
  2. Select the cells based on whose values you want to fill in other cells.
  3. 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. 

How the Autocomplete function works in Google Sheets

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.

How the Formula Suggestions works in Google Sheets

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.

How the Formula Suggestions changes its recommendation in Google Sheets

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.

How the Formula Corrections works in Google Sheets

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.

What are the other basic tips in Google Sheets?

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.

Google Sheets Sign-in: Explained

How to Rename a Google Sheet File

How to Save Google Sheets in Google Drive

How to Set Up Notifications in Google Sheets

How to Set Print Area in Google Sheets

Change Theme in Google Sheets: Explained

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.

Redo and Undo in Google Sheets: Explained

How to Reference Another Sheet in Google Sheets

How to Make a Table in Google Sheets

How to Insert an Image into a Cell in Google Sheets

Check the following posts if you want to learn how to insert, modify, and remove comments and note or tag someone in your comment.

How to Add a Comment to a Cell in Google Sheets

How to Tag Someone in Google Sheets

Comment History in Google Sheets: Explained

How to Insert a Note in Google Sheets

How to See Edit History in Google Sheets

Read these articles to learn how to show formulas, missing toolbar, or formula bar in Google Sheets.

How to Show Formulas in Google Sheets

How to Show Formula Bar in Google Sheets

How to Show the Toolbar Missing in Google Sheets

Visit these articles to learn how to protect cells and view protected cells or ranges.

How to Lock Cells in Google Sheets

How to View Protected Ranges in Google Sheets

Analyze your live financial data in a snap

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.

Automate any custom financial dashboard in Google Sheets with LiveFlow

Learn how to do this step-by-step in the video below 👇

Do you need personal help?

Our team of real people are here to help you any time between 9am and 10pm GMT.
Check Icon
Email us at: founders@liveflow.io
Check Icon
Call us at +1 (415) 650-1711