This post has been republished via RSS; it originally appeared at: Excel Blog articles.
In December 2022, we announced Formula Suggestions was beginning to roll out to Excel web users. It is now available to all web users with English set as their primary language. Whether you’re new to Excel or have used it for years, Formula Suggestions helps you create formulas 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 formulas that you can apply to your data, along with a specified range.
Why use Formula Suggestions?
Formula Suggestions can help you work faster and smarter with your data in several ways. First, it can improve your productivity by reducing the time and effort you need to type and edit formulas. For example, look at how Formula Suggestions can help you calculate the total sales from each item:
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, notice how the totals are affected when you add a new row to the 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:
Third, Formula Suggestions can help you discover new possibilities of what you can do with your data, by showing you different formulas 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 formulas.
Formula Suggestions can suggest the following aggregation formulas:
- SUM
- MIN
- MAX
- AVERAGE
- COUNT
- COUNTA
These formulas 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. Let’s see a few examples below:
In this example, Formula Suggestions understands, based on the data and the context, that you want to find the minimum amount sold, and suggests the appropriate formula:
In the next example, Formula Suggestions shows you multiple suggestions for a specific range, depending on the context:
Formula Suggestions can also assist in cell-counting tasks – the COUNT formula counts cells that contain numbers, while 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 can be achieved by leveraging Formula Suggestions’ ability to predict the correct formula for each of the use cases:
Formula Suggestions can also suggest the right formula for your rows, not just your columns:
Who can benefit from Formula Suggestions?
Formula Suggestions currently provides suggestions for basic aggregation formulas - helping users who are not yet acquainted with these Excel formulas. However, experienced users can 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.
In the example below, you can see how much time Formula Suggestions could save even in a basic task of calculating an average:
Availability
At the time of this article, Formula Suggestions is available to Excel web users that have English set as their primary language.
The technology behind Formula Suggestions
Formula Suggestions is an AI-driven feature. It uses Machine Learning models to predict which formula and range the user will choose after typing “=”. The feature was developed in collaboration with the Calc Intelligence research team at MSR Cambridge.
Share your feedback with us
We developed this feature with our customers in mind. We want to hear from you if you think that there are ways we can improve Formula Suggestions. If you:
- found a bug,
- think the experience should be different,
- think we need to cover additional work scenarios,
… then please let us know by leaving your feedback below. You can also send us direct feedback from Formula Suggestions’ card by clicking on “Give Feedback”:
Additionally, you can submit your feedback about the feature by going to Help > Feedback.
Try it out!
In the Excel workbook below, you can play around with Formula Suggestions - Try writing a formula in any of the yellow cells: