More on Formula by Example
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.
Formula by Example can recognize several patterns like text transformations, date transformations, arithmetic calculations, row numbering, and forward filling.
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.
Looking to extract information about the dates from your table? With Formula by Example, that’s an easy task.
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.
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.
You can use Formula by Example to forward fill the rest of the column based on the first examples.
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.
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!
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:
… 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.
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!
