How to use formula suggestions in Excel on the web

Formula suggestions in the newly released Excel online will save you time doing simple calculations. Here's how to use the formula suggestion feature in Microsoft Excel online .

Powered by advanced machine learning models, Formula Suggestions automates your calculations by suggesting relevant formulas based on input data and context. Think of it as a powerful autocomplete feature that can help new or seasoned Excel users.

Formula suggestions are now available to all Microsoft Excel web users in the English language.

What are formula hints in Excel?

After entering the '=' sign in a worksheet cell or formula bar, Microsoft Excel automatically suggests both the most suitable formula to use based on the data collected from the column or row.

The current suggested Excel formulas include aggregate functions: SUM , AVERAGE , MIN , MAX , COUNT , and COUNTA .

This automatic feature saves you time and reduces errors. On the other hand, it is powerful and flexible enough in allowing you to edit table data without messing with formulas and results. In addition, it also helps to select the range for the data, preventing errors from occurring when manually selecting.

 

How to use formula hints in Excel

Make sure you're using the web version of Excel, as this feature isn't currently available in Microsoft Excel for Windows and Mac. Here's how to use them.

1. Use '='

Start by typing ' = ' in the formula box, and wait for Excel to make relevant suggestions.

1. SUM : Calculates the sum of the range of cells.
2. AVERAGE : Calculates the average of a range of cells.
3. MIN : Calculates the minimum value from a range of cells.
4. MAX : Calculate the maximum value from the range of cells.
5. COUNT : To count the number of cells in a range of cells. It counts both numbers and text values ​​but ignores blank cells and cells containing formulas or non-numeric values.
6. COUNTA : To count the number of cells in a range of cells. It counts cells containing any data type, including numbers, text, logical values ​​(TRUE or FALSE), error values, and blank text ("").

How to use it is quite simple as shown below.

Sometimes, however, Excel doesn't immediately choose the operator you want. This is especially true with COUNT and COUNTA formulas.

In that case, type the first character or two in your function and you'll see the immediate formula hint that selected it.

 

If you have problems, like recipe suggestions not working consistently, click the Give feedback link at the bottom of the recipe suggestions box to send feedback to the developer.

2. Include contextual insights in your spreadsheets

The more accurate the data on the table, the more relevant the suggestions you get. Contextual data is essentially focused on column and row headers.

As you can see from the example below, Microsoft will suggest different formulas for the same column according to the row header. Lowest and Maximum have two different suggestions, even though they deal with the same range of cells.

MIN . function

MAX . function

SUM . function

AVERAGE function

 

3. Use formula hints in both columns and rows

This also works the same way across rows. Make sure all rows and columns are visible to avoid error results.

The Power of Automation in Excel

As you can see, the formula suggestion feature in Excel Online is easy to use, helping you quickly perform simple calculations. You will find yourself saving a lot of time when using function hints in Excel.

Hope the article is useful to you.