This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
Last month, we announced Formula by Example starting to roll out to Excel web users. Formula by Example looks for patterns as the user enters data in the worksheet. When it recognizes a pattern, Formula by Example offers a formula to fill the rest of the column with the recognized pattern. The example below shows Formula by Example helping to reverse the first & last name order.
How to make Formula by Example work
Currently, Formula by Example supports Excel tables. Support for ranges is coming in a future release - stay tuned for more! If you wanted to try Formula by Example on a range of data today, convert it into a table – select range, and click Insert > Table (or use the Ctrl + L keyboard shortcut).
Now that you have a "Ctrl+L" table, Formula by Example suggestions will appear after you provide Excel with a few examples in a certain column. Excel scans the column to identify a pattern in your data. When it finds a pattern, Excel will show a suggestion.
What can Formula by Example do?
Formula by Example can recognize several patterns like text transformations, date transformations, arithmetic calculations, row numbering, and forward filling.
Text transformations
Formula by Example can help you with transforming and manipulating text strings. Looking for a way to extract the initials from each name? No problem!
And how about extracting the first, middle and last name of each person?
Using Formula by Example, you can easily get rid of excess whitespaces in your data.
Date transformations
Looking to extract information about the dates from your table? With Formula by Example, that’s an easy task.
Arithmetic calculations
Formula by Example will identify if you are trying to perform an arithmetic operation on different columns. Let’s say we want to find the total sales by multiplying the Price by Quantity. By typing the first couple of results, we will get Excel to complete the rest of the column for us with the arithmetic calculation formula.
You can also use Formula by Example to apply different types of rounding to your numbers.
Automatically & dynamically number your rows.
Formula by Example allows you to create a dynamic row numbers column from example. This could come in handy in cases where you’d want your numbering to dynamically adjust if you add or remove a row.
Forward filling
You can use Formula by Example to forward fill the rest of the column based on the first examples.
Availability
At the time of this article, Formula by Example is available on Excel web for all US English users of OneDrive for Microsoft 365 Personal or Family. Formula by Example is rolling out to users of SharePoint and OneDrive for Business. Additional language support will be available in a future release.
Why did we develop Formula by Example?
Filling column data based on a pattern is something that Excel has been able to do for many years, using a feature called Flash Fill. However, Flash Fill’s suggestions are only provided as static text. If you wanted to change some of your input data or reuse the suggestion on different cells, you wouldn’t be able to do so, because you wouldn’t get a formula as an output. With Formula by Example, you will now see a formula that you can easily change, copy, and reuse anywhere you want.
Another motivation we had in developing this tool was to educate users about the power of Excel formulas and show them how they can save time using different formulas. Not only users with limited formula experience can benefit from this feature, but also more advanced users. For example, sometimes we know that a certain formula could be used to solve a problem, but we are not sure which one or how we should use it. By using Formula by Example, we can type a few examples, and we’ll get that formula suggestion and Excel will do the rest of the work for us!
The technology behind Formula by Example
Formula by Example builds on the technology that enables Flash Fill, leveraging logical-reasoning-based symbolic techniques to efficiently search for formulas that match the user-provided input and output examples. It then ranks the formulas to pick one that likely matches the user’s intent – and if that’s not the case, the user can provide additional representative examples to guide the tool. Formula by Example is the next generation of the Flash Fill technology that covers a wider variety of transformations (including datetime and numeric manipulations) and generates readable Excel formulas. This formula-by-example technology has recently also been released in Power Automate and Power Apps. To learn more, check out the research by the PROSE research team.
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 by Example. 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 by Example’s 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 by Example. All you need to do is to type in a number of cells in any of the columns of the Excel table. If Excel recognizes a pattern, it will show Formula by Example suggestion! You can start by typing in the empty columns: "Row No.", "Full Name", "First 2 letters", "Month name". You can also try different patterns - Start with some of the examples described in this post. Let us know in the comments about your favorite Formula by Example use case!