Summary
Are you tired of manually entering repetitive data into your spreadsheet? Or are you frustrated that Flash Fill doesn’t always work as expected? Well, stop there, because Microsoft Excel’s little-known tool, Formula by Example, can do the hard work for you.
At the time of writing (June 2025), Formula by Example is only available to those using an English-language version ofExcel for the Webwith a freeMicrosoft accountor a paid subscription. It isnotavailable in the Microsoft Excel desktop app.
Flash Fill, introduced to Microsoft Excel in 2013, was a time-saving game changer. It recognizes patterns in your data, and uses those patterns to suggest ways to automatically fill in the rest of a column.
However, despite its benefits, this tool has a severe limitation—if the source data changes, Flash Fill can’t go back and reapply the pattern to the new information. In other words, the values created by Flash Fill are static, which is useless if you’re trying to create a dynamic spreadsheet.
Another drawback of Flash Fill is that all the work is done behind the scenes. Yes, it can recognize patterns and complete the data for you, but you have no way of knowing how it did this. If, like me, you’re interested in understanding the inner workings of your spreadsheet, this is a frustrating shortcoming.
Luckily, Microsoft recognized these issues, and came up with an excellent solution—Formula by Example.
Formula by Example: Preparing Your Data
To use Formula by Example, first, go toExcel for the Web, and log in to your Microsoft account.
Then, either open an existing spreadsheet or click “Blank Workbook” to start with a blank canvas.
The important thing to note about Formula by Example is that it only works in formatted Excel tables, not regular ranges. So, select your data, and in the Insert tab on the ribbon, click “Table.”
If your table has headers, check the box in the dialog box, and then click “OK.”
If you prefer using keyboard shortcuts, select one of the cells in your contiguous range, and press Ctrl+A to select all the cells. Then, press Ctrl+L to launch the Create Table dialog box, and finally, press Enter to create your table.
Now that you’ve converted your data into an Excel table in Excel for the Web, you’re ready to use the Formula by Example tool.
Example 1: Transforming Text
There are many ways to transform text from one column to the next, including using theText to Columnstool or opening Excel’sPower Query Editor. However, Formula by Example is probably the most intuitive.
In this example, let’s say you want to extract people’s names into column B from their email addresses in column A. You also want the names to be capitalized.
Start by typing the first couple of examples of how you want the text to be transformed. As soon as Excel recognizes a pattern, a floating window appears that offers to fill the remaining cells in that column with a formula. At this point, you can either cast your eye down the column to see whether the remaining values are as expected, or click “Show Formula” to see the formula that Excel has generated to transform the data.
In this case, the formula is:
where thePROPERparts of the formula deal with capitalization, and theTEXTBEFORE,MID,LEFT, FIND, andLENparts of the formula deal with the positioning of the text to extract.
Take a moment to appreciate the complexity of this formula, and how long it might have taken you to generate it yourself. In situations like these, Formula by Example is a super handy tool that will save you lots of time.
If you’re happy with what’s been produced, click “Apply” to complete the column.
Now, because a formula has been used to create this text transformation, you can use it on other datasets—simply copy and paste the formula to the relevant cell, and update the parts of the formula that reference a specific column in the original table (in this case,[@Email]) with a new structured reference.
What’s more, you can learn about some functions you haven’t previously seen by reviewing the formula closely. Most importantly, however—and the biggest benefit of using this tool over Flash Fill—is that if the values in the precedent cells change, the formulas in the dependent cells will pick up these changes and update the result automatically.
The formula Excel generates can be specific to the structure of the original data. For example, if part of the formula looks for uppercase letters in the referenced cell, but it doesn’t have any, you may see an error. To avoid this, make sure your data is consistently structured and formatted.
In a similar example, here, Excel creates a formula that extracts the first three letters of a product code into the second column of the formatted table:
Then, I used the same principle in column C to extract the final three numbers. Here’s the formula it generated:
Example 2: Numbering Rows
One issue with formatted Excel tables is that they’re not compatible with dynamic array formulas, meaning you can’t use functions that spill the result into more than one cell, likeSORTorFILTER.
In this example, let’s say you want to add dynamic numbers to column A so that you may keep count of the number of players in the game at any given point.
One way to do this in an unformatted area of a spreadsheet is tonest the COUNTA function within the SEQUENCE functionto count the number of cells in column A that contain a value, and then use this to generate a sequence that increases by one in each row. However, this isn’t possible in an Excel table, as the SEQUENCE function produces a spilled array.
However, thanks to Formula by Example, there’s a workaround.
Start by typing the first few numbers into column A, and when Excel recognizes the pattern, check the formula, and click “Apply.”
Clicking “Apply As Value” inserts the numbers as values, rather than formulas, as though you typed them manually. Essentially, Excel is giving you the option to use Flash Fill instead of Formula by Example, which, in most scenarios, isn’t what you’ll want to do.
Now, you know that when you add or remove rows to or from the table, the count will update automatically. What’s more, you’ve learned that you can use theROWfunction and reference the column headers to overcome the table-spill incompatibility issue I discussed earlier.
Example 3: Working With Dates
Formula by Example doesn’t just help you transform text—it works just as well with dates.
In this example, after typing the first few years into column B, Excel recognized that I was taking this information from the Date column, and produced a formula accordingly:
In column C, the program saw that I was converting the month from the shorthand form to the full month name, so it generated the following formula:
Finally, in column D, it used the DAY function to complete my dataset:
As with example 1, if any of the original dates in column A change, the corresponding splits in columns B, C, and D will update accordingly. Similarly, if you add more rows to the bottom of the table, they’ll adopt the same formulas.
Example 4: Generating Arithmetic Calculations
Formula by Example in Excel for the Web can also work out how a result can be generated using a formula.
In this example, when I complete the first few multiplications manually, Excel spots the pattern and suggests a formula.
Then, in the next column, when I started rounding up the resultant values to the nearest 10, Excel recommended that I use theROUNDUPfunction to complete this operation.
Now, let’s say I made an error when inputting the number of stores in cell E3—instead of 6, it should have been 16. So, let me go back and amend that figure, and we’ll see whether the values in columns D and E update automatically.
Indeed, they do, because Excel used a formula, rather than Flash Fill, to calculate these values.
The best way to get used to Formula by Example in Excel for the Web is to give it a go and see what it can do. For example, as well as creating formulas for the scenarios in the screenshots above, this tool can remove unnecessary spaces from your data, forward-fill a column based on examples in the first few rows, and duplicate values from a previous column while adding extra characters.
