Formula Suggestions in Excel

Back in December last year, Microsoft announced ‘Formula Suggestions’ was beginning to roll out to Excel web users.  Now, it is available to all web users with English set as their primary language (rules me out then: I use profane).  Whether you’re new to Excel or have used it for years, ‘Formula Suggestions’ helps you create formulae more quickly and accurately by offering relevant suggestions based on your data and context.  All you need to do is type "=" at the beginning of a cell, and Formula Suggestions will show you different formulae that you can apply to your data, along with a specified range.    

‘Formula Suggestions’ can help you in several ways.  First, it can improve your productivity by reducing the time and effort you need to type and edit formulae.  For example, look at how Formula Suggestions suggests a simple summation here:  

Second, ‘Formula Suggestions’ can provide you with robust formulas that will not break if you alter your data as it automatically adjusts the ranges to match your data structure.  For example, totals are affected when you add a new row to a table.  If you use Formula Suggestions, the range will automatically update to include the new row, but if you manually add the cells, it will not – as in the following example:

Here, in cell D14, I have typed in the rather dreadful formula

However, in cell I14, I have started to type the formula in to prompt ‘Formula Suggestions’ and then used the formula recommended.  This gives similar calculations:

This will clearly give a better row should team members be added (rows inserted) or removed (rows deleted).

Third, ‘Formula Suggestions’ can help you discover new possibilities of what you can do with your data, by showing you different formulae that are relevant for your analysis.  Whether you are a beginner or an expert in Excel, ‘Formula Suggestions’ can offer you a convenient and intuitive way to create formulae.  ‘Formula Suggestions’ can suggest the following aggregation formulas:

• SUM
• MIN
• MAX
• AVERAGE
• COUNT
• COUNTA

These formulae are widely used in Excel to perform various calculations and analyses on data, such as finding the sum, minimum, maximum, average or count of values in a range.

For example, ‘Formula Suggestions’ may also assist in cell-counting tasks.  The COUNT formula counts cells that contain numbers, whilst COUNTA will count all non-empty cells regardless of the type of data they contain.  Using the correct formula is crucial to get the correct results, and this may be achieved by leveraging ‘Formula Suggestions’ ability to predict the correct formula for each of the use cases.

Formula Suggestions currently provides suggestions for basic aggregation formulae, helping users who are not yet acquainted with these Excel formulae.  However, experienced users may benefit greatly from this feature as well, as it suggests not only the appropriate formula but also the appropriate range for that formula.  The range suggestions can save a great deal of time, and can also prevent us from selecting incorrect ranges, which may lead to wrong results. 

At the time of this article, Formula Suggestions is available to Excel web users that have English set as their primary language.