Hey everyone, did you know that Google Sheets has a really handy formula suggestion feature? It’s designed to help you work with formulas faster and more efficiently by suggesting functions based on the data in your sheet. It’s on by default, but you can turn it off if you prefer the manual approach. Let’s see how it works.
Using Formula Suggestions in Sheets
Basically, when you start typing a formula by entering the = sign in a cell, Sheets will display a list of potential formulas and functions. You can quickly insert these into your spreadsheet by pressing the Tab key or by simply clicking the suggestion you want. This feature uses machine learning to understand your data and make relevant suggestions. Just note it works best when your data has clear patterns.
Example 1: Summing a Range
Let’s say you’ve got a list of fruit prices and you want to calculate the total. Usually, you’d use the SUM function, right?
With formula suggestions, you don’t have to write it all out. Just type = in the cell below the price list (like B11) and Google Sheets will show some suggestions. Make sure you are entering the = sign at the bottom of the data or next to the range you want to use to calculate.
You’ll notice that the SUM formula is one of the suggestions.
To select the suggested formula, just hit Tab or click on it. You can also use your up and down arrows to pick a suggestion and then press Enter. In this case, let’s pick =SUM(B2:B10).
If you change your mind or want to skip the suggestions, just press the Esc key, or click on the X button on the suggestion box.
Once you’ve selected a suggestion, it will appear in the cell, but it won’t run just yet. You can make any changes if needed, like changing the range of cells. When you’re happy with it, just press Enter again to execute the formula.
The result will appear, and the next cell will be automatically selected.
Now, if you prefer not to use this feature, there are a couple of ways to disable it.
Disabling Formula Suggestions
Method 1: Using the suggestions box
You can click the ‘X’ on the suggestions box when it appears.
Method 2: Using the shortcut key
You can disable suggestions by pressing the F10 key.
Method 3: Through settings
You can also disable this feature by going to the ‘Tools’ menu and selecting ‘Enable formula suggestions’ from Preferences.
Example 2: Finding the Maximum Value
Okay, the last example was pretty straightforward with SUM. But what if you need to do something more complex? Let’s try finding the maximum value.
Here we have a list of salespersons and their sales. We want to find out the largest sale amount.
Let’s type “Maximum” in cell A14 below the list. This will act as a label for the result.
Now when you type = in cell B14, Google Sheets will understand based on the label and data and suggest SUM, AVERAGE and also the MAX function.
Hit the Tab key to select MAX(), and press Enter.
Press Enter again to execute the formula.
And there you go! The maximum sales amount has been calculated.
These formula suggestions are great for avoiding errors and make data analysis much more convenient, especially for those who are new to Google Sheets formulas.