Formula by Example

 ‘Formula by Example’ has now started to roll out to Excel web users.  ‘Formula by Example’ looks for patterns as the user enters data in the worksheet.  When it recognises a pattern, ‘Formula by Example’ offers a formula to fill the rest of the column with the recognised pattern.  

Consider ‘Formula by Example’ helping to reverse the first and last name order here, where we start typing in what we want:

At this stage, ‘Formula by Example’ interrupts us:

 We can click on ‘Show Formula’:

All you have to do now is ‘Apply’:

It’s Flash Fill with formulae (more on that later)!

Currently, ‘Formula by Example’ supports Excel tables.  Support for ranges is coming in a future release.  In the meantime, if you wish 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 – CTRL + T does not work in Excel for the web).

Once 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 recognise several patterns like text transformations, date transformations, arithmetic calculations, row numbering and forward filling.  It’s pretty simple:

‘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, Excel can complete the rest of the column 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 also use Formula by Example to forward fill the rest of the column based on the first examples.

At the time of writing, ‘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.

It should be noted that filling column data based upon 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.

‘Formula by Example’ builds upon the technology that enables Flash Fill, leveraging logical-reasoning-based symbolic techniques to efficiently search for formulae that match the user-provided input and output examples.  It then ranks the formulae 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 formulae.  This formula-by-example technology has recently also been released in Power Automate and Power Apps.