Excel 2013 Flash Fill Pattern Recognition

​Excel may not seem like the most exciting part of the recent Office 2013 Preview, but one of the new features is quite clever, and is worth a second look. 

Excel has always included some basic pattern recognition, but Microsoft and the Excel team have really upped their game this time around. It now has the ability to pattern fill from multiple strings within a single column. This is obviously tough to describe, so here is an example:

 Let’s assume you have a table of clients, and you need to create an email address for each client. A logical way to organize this list is to use the first letter of their first name, and the last name, followed by the domain their email is hosted on.  In this example, we will use the following names and domains:

John Smith, Jenny Doe, Adam Hugh, and Ryan Long​	Contoso.com, Global.net, and Wierwood.org

If the names are entered in a column, the domains in another column, and the emails in a third, you would usually need a formula to compose the email. In Excel 2013 however, it begins to recognize a pattern after the first two entries. In the image, you can see that it assumes the emails should be made of the letter ‘j’ followed by the last name and the domain name. This is a logical assumption considering the first two names start with ‘j’. Hitting enter upon seeing its autocompleted entries based on the pattern would fill out all names with the pattern of ‘j’ + <last name> + <domain>.

This is not the correct pattern though. To correct the pattern, simply changing the first letter of the first incorrect entry, in this case, changing the ‘j’ to an ‘a’ in Adam Hugh’s email address will update the pattern and correct all email addresses below it.

Excel 2013 has a whole host of other new features but this single addition will result in significant time savings for many individuals working with data. Finding the limits of its pattern recognition abilities will be left to another post.