Here’s a frequent scenario in Excel: you need to create a new column that reflects patterns identified in an existing one.
Excel provides several options for accomplishing this. First, there’s Flash Fill, which is impressively quick and user-friendly. However, it is not the most scalable option as it doesn’t create a reusable formula or methodology for processing new data.
Alternatively, Column by Example within Power Query allows for the generation of explicit formulas applicable to fresh data. Despite its precision, it is somewhat unwieldy and buried within the Power Query interface.
Fortunately, Excel has introduced a middle ground with its Formula by Example feature that combines the simplicity of Flash Fill with the repeatability of Column by Example. This blog post explores the tool:
Availability & setup
Formula by Example feature is currently available in the online version of Excel. Additionally, users must have a OneDrive account, with the file saved on OneDrive to utilize this function.
Moreover, the dataset in question must be formatted as a table. This formatting can be easily achieved in Excel for the web by using the keyboard shortcut Ctrl + L:
Ensure that the data source for this example includes headers.
Applying the formula
For the initial example, I would like to create a table in this column that combines my separate City and State columns into a format that reads City, State. Consequently, the first entry would be “Atlanta, GA,” followed by “Austin, TX,” and so on.
I could certainly employ a simple Excel formula to craft this new field on my own. However, it is more efficient to let Excel handle the task, saving me time and effort.
Begin by entering City, State in cell C1. As the data source is already formatted as a table, adding this in C1 will cause the table to automatically update to incorporate this new header. In the subsequent data rows under this new column, provide Excel with a few examples of your intended output.
After inputting examples in just a few rows, Excel will likely recognize the pattern and suggest auto-filling the remainder of the column with the corresponding formula for you:
You can request Excel to provide a formula preview before incorporating it into your column. Remember that these are structured table formulas, so it’s advisable to refresh your knowledge of Excel tables before diving into this feature.
If the suggested formula is satisfactory, click “Apply.” If it isn’t what you desired, click “Ignore” and provide Excel with more examples to better meet your expectations.
Great job!
Let’s practice with an additional example to ensure proficiency. This time, you have a list of dates from which you wish to extract the month names.
Follow the familiar process: convert your list into a table, give a few examples, and so on. After completing these steps, you should see a formula like this one:
Excel’s capacity to leverage artificial intelligence for creating new fields from existing ones is well-established. However, “Formula by Example” might stand as the premier integrated solution, merging Flash Fill’s user-friendly operation with Power Query’s “Column by Example” feature, which allows for rule viewing and application to new data.
How is this AI?
Formula by Example embodies AI by employing machine learning algorithms that recognize and anticipate patterns within data.
For instance, when a user inputs a set of example data entries into Excel and outlines the desired output, this AI-infused tool evaluates the data to propose a formula capable of producing the specified results across the entire dataset. It essentially ‘learns’ from the inputted examples, deciphers the patterns, and extrapolates a generalized solution to the broader data set.
What questions do you have about using formula by example in Excel, or AI-powered Excel more generally? Let me know in the comments.
Leave a Reply