In Summary
- Google Sheets has started rolling out Formula Suggestions where Google automatically recommends formulas based on the data in use.
- These suggestions use Google’s Machine Learning Model to evaluate suggestions by considering the entered data, labels of rows and columns, and other formulas used in the sheet.
- Google says that the new Formula Suggestions will be rolled out to Personal, Google Workspace, GSuite Premium, and all other users within 15 days.
- Here’s how you can enable and use Formula Suggestions in Google Sheets.
Google Sheets has been gaining popularity with its free-to-use online model, making editing easier while sharing sheets. With already a few advantages up its sleeve against its competitors, Google has now pushed a new feature in Google Sheets where you can get automatic suggestions for formulas based on the entered data. Here’s how you can get Google Sheets Formula Suggestions on your computer right now.
Table of Contents
What are Google Sheets Formula Suggestions?
As the name says, Google Sheets Formula Suggestions will suggest some formulas based on the data being entered in the sheet. In its blog post, Google says that it has trained a Machine Learning Model to achieve this. For instance, when you have a column of certain data and try to use a command for some formula, Google will analyze the data in that row, column, or table and suggest you a suitable formula.
How to Get Google Sheets Formula Suggestions?
The new Google Sheets Formula Suggestions feature is being rolled out to users in phases. Google has kept it under observation to monitor it and collect feedback from early users. To enable Google Sheets Formula Suggestions, click on “Tools” and click on “Enable formula suggestions“. Even if you don’t see this option, the chances are that it would still work once you try to enter a formula command.
Now navigate to the cell where you wish to use your formula. Once you type “=” Google will show you some aware suggestions based on the data from the table. From our experience, Google recommends SUM and AVERAGE quite regularly as these are two of the most commonly used formulas in Google Sheets. As per Google, this new feature also considers the labels and headings for rows and columns while suggesting a formula.
I Don’t See “Enable Formula Suggestions Under Tools, What to Do?
The new Google Sheets Formula Suggestions feature is being rolled out to users in phases, including personal users, Google Workspace, G Suite Basic, G Suite Business, etc. As per the company, it will take around 15 days for this feature to be rolled out to everyone. However, in most cases, formula suggestions show up in Google Sheets even after the toggle being missing from the tools section. So do try using a formula first and see if it is working for you.
How to Use Formulas for Google Sheets?
You can use formulas for Google Sheets in multiple ways, like using the formula box and more, but it is an easy way to get started.
- Select the cell where you want to store your result.
- Click on the Function button (Σ) on the toolbar, and select the operation you wish to apply.
- Now press the Ctrl key on your keyboard, and select the cells across which you want to apply the formula. You can manually select cells one by one, or scroll your mouse over a row or column to select multiple cells.
- Press the Enter key on your keyboard. Your result is now stored in the selected cell based on the applied formula.
You can also enter the formula manually in this manner by using formula shortcuts.
Note: You can also use this method to use a custom formula.
- Select the cell where you want your result.
- Type “=” followed by your formula command.
- Now enter the cell addresses across which you wish to apply the formula.
Syntax for applying formula in a row or column
=AVERAGE(C2:C6)
Syntax for applying a formula across selective cells
=AVERAGE(C2,C6)
Your result will now be stored in the selected cell.
It is that easy to apply a formula in sheets. Here is a list of some basic spreadsheet formulas along with Array Formulas and Complex Formulas.
Related: 11 Best Google Sheets Calendar Templates
How to Get Formula Hints Working on Google Sheets?
Google Sheets provides Incremental Suggestions in the form of hints, where it shows a dropdown menu of all applicable formulae when you enter the first alphabet. Sometimes, this drop-down menu does not show up while typing the formula. In such cases, you can press ‘Shift + F1‘ on your keyboard to get formula hints working in Google Sheets. If you are someone who keeps forgetting formulas like us, then the hints work as a great formula suggestion tool.
How to autofill formulas in Google Sheets?
To apply a formula across an entire row or a column:
- First, execute the formula for a single row/column and store the result in an adjacent cell.
- Now place your mouse cursor on the blue square located in the bottom right corner of the cell where your result is stored.
- Hold the left click on your mouse and drag it to the point where you wish to apply the same formula.
With the new Google Sheets Formula Suggestions, Google detects specific patterns in data and automatically shows a popup where you can apply the same formula across an entire cell or a row. You can allow this autofill by clicking the green tick with your mouse or by pressing ‘Ctrl + Enter‘ on your keyboard. This works very well as an Auto Complete Function and comes really handy when working with a huge database.