Excel Formula by Example

Excel can now write formulas for you. Simply give it an example or two and it’ll suggest a formula to complete the task.

Remember how amazing Flash Fill was when you saw it for the first time? Well, Formula by Example is 10x better because it isn’t a one time and done solution.

Being a formula, it automatically picks up any changes in the data.

excel formula by example

Formula by example is still in the early stages of development and only available on Excel for the Web for all US English users of OneDrive for Microsoft 365 Personal or Family subscriptions.

This also means the functionality you see here may change before it’s generally available.

Try it out in the embedded workbook below.

Video
Try it Out Here
Text Split
Text Join
Calculations
Dates
Row Numbering
Forward Filling
Feedback

Watch the Video

Try Out Formula by Example

Formula by Example

Formula by example recognises patterns in your data entry for text, dates, arithmetic, row numbering and forward filling.

Currently formula by example only works in Excel Online in Excel Tables. To create a table in Excel Online we use the keyboard shortcut CTRL+L or insert them via the Insert tab of the ribbon:

insert table via ribbon in Excel Online

The functionality will eventually extend to ranges, but the first release is limited to Tables.

Splitting Text

Extracting text from a cell is now easy to do with formula by example. Here it splits the text after the period:

extract web domain using formula by example

In the more complex example below we see Formula by Example split text into First Name and Last Name columns. Notice how it also corrects case and ignores middle initials:

excel formula by example extracting names

It can also rearrange data. For example, reformatting the numbers below to include a hyphen between each set of 3 digits:

reformat numbers with formula by example

Text Join

Joining text is similar. Again, it corrects case in the names on the 6^th and 7^th rows:

joining text

Calculations

Formula by example can also handle math equations. It is triggered when it detects manual entry of data that could be derived with a formula:

math with excel formula by example

Dates

Extracting date information from a list of dates is also easy:

extract dates

However, it still struggles to return the date for the month end. In the example below it allows me to enter all the dates without suggesting the EOMONTH function that it used in the example above:

excel formula by example doesn't use EOMONTH function

Row Numbering

Dynamic row numbering is easy. Just start to enter consecutive numbers and Excel will suggest a dynamic formula that automatically adjusts when you add or delete rows:

dynamic row numbering

Forward Filling

Similar to row numbering, you can give Excel a set of examples and it will detect the pattern and suggest a formula to complete the column:

filling a column with excel formula by example

Notice the number on row 11 isn’t correct. It should be E-010. Simply change it manually and Formula by Example will suggest a correction to the formula.

Feedback

Formula by Example is still in development. If you have feedback about how it should work, more scenarios or bugs, use the ‘Give Feedback’ button to send it direct to the Excel team at Microsoft:

feedback