Excel for Windows Gets a Feature That Could Save You Hours

Microsoft has announced that Formula by Example, an intuitive tool that generates formulas based on patterns in your Excel spreadsheet, has arrived in Excel for Microsoft 365 on Windows. Before now, this feature was only available in Excel for the web, so it's a welcome extension if you prefer using the desktop version of the spreadsheet software.

Those of us who have used Excel for years are familiar with Flash Fill , which recognizes patterns as you populate a column and uses them to suggest ways to automatically complete the range. However, the biggest drawback of this feature is that it can't reapply the pattern to new data if you edit the precedent cells.

To give credit to Microsoft, it recognized this was an issue and did something about it. Specifically, it introduced a new tool—Formula by Example—which, instead of automatically filling the range with static data, suggests a formula that can do the job dynamically. When you use this feature, not only do changes to the precedent cells trigger the dependent cells to update automatically, but you also don't have to waste time constructing complex formulas.

What's more, if you want to reuse the generated formula in another dataset elsewhere, you can copy and paste it as required. You could also go one step further and review the resultant formula to learn about functions you haven't used before.

To use Formula by Example, first, you must format your data as an Excel table (select a cell in the data set, and press Ctrl+T), as the tool doesn't work on regular ranges. Then, enter the first few values into a range from top to bottom, where each cell's content follows the same pattern as in the other cells.

In the screenshot below, when the first and last initials of the names in columns A and B are typed into cells C2, C3, and C4, the Formula by Example floating window appears. At this point, clicking "Show Formula" reveals the suggestion.

Once you've verified the suggestion by scanning the column visually to make sure the formula returns what you expect, click "Apply."

You can then see the formula in the formula bar at the top of the Excel window.

Other use cases include summing all numerical values in a row, extracting people's names from their email addresses, splitting parts of serial numbers or codes, or introducing dynamic row numbering in a database.

To take advantage of Formula by Example in Excel for Microsoft 365 on a Windows PC, you must have a Copilot license, which comes as standard with Microsoft 365 Personal, Family, or Premium subscriptions, unless you downgrade your Microsoft account before the next billing date. If you don't have a Copilot license, you can still make use of this handy formula automation tool in Excel for the web . To date, there's no word from Microsoft on when it plans to make Formula by Example available to those using Excel for 365 on a Mac.

Source: Microsoft