Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
More on Formula by Example
Published Feb 14 2023 09:00 AM 18.3K Views
Microsoft

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.

 

Using Formula by Example to reverse the first & last name orderUsing Formula by Example 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).

 

Creating an Excel table from a rangeCreating an Excel table from a range

 

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!

 

Extracting initials using Formula by ExampleExtracting initials using Formula by Example

 

And how about extracting the first, middle and last name of each person?

 

Extracting first, middle and last names using Formula by ExampleExtracting first, middle and last names using Formula by Example

 

Using Formula by Example, you can easily get rid of excess whitespaces in your data.

 

Removing excess whitespaces using Formula by ExampleRemoving excess whitespaces using Formula by Example

Date transformations

Looking to extract information about the dates from your table? With Formula by Example, that’s an easy task.

 

Using Formula by Example to perform date transformationsUsing Formula by Example to perform date transformations

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.

 

Arithmetic calculations using Formula by ExampleArithmetic calculations using Formula by Example

 

You can also use Formula by Example to apply different types of rounding to your numbers.

 

Rounding numbers using Formula by ExampleRounding numbers using Formula by Example

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.

 

Automatically number your rows using Formula by ExampleAutomatically number your rows using Formula by Example

Forward filling

You can use Formula by Example to forward fill the rest of the column based on the first examples.

 

Forward filling item numbers using Formula by ExampleForward filling item numbers using Formula by Example

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”:

Give Feedback about the featureGive Feedback about the feature

 

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! 

8 Comments
Co-Authors
Version history
Last update:
‎Feb 14 2023 11:15 AM
Updated by: