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.

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.

---

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:

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:

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:

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

Text Join

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

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:

Dates

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

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:

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:

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:

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: