FlashFill Meets Get & Transform

Do you love FlashFill? Do you love Get & Transform queries? If so, you’ll love the most recent Excel update pushed out by Microsoft: New Column by Example. It is essentially FlashFill inside PowerQuery. It is way cool. Check it out.


Let’s say we have some data. It doesn’t matter where it is, it could be in an Excel table, a csv file, or some external database. But, we have some data and it looks a bit like this:

Data Table by Jeff Lenning

We need to clean it up a bit. For example, we need to split the Name column into first and last, and, capitalize the first letter of each name. We also need to combine the City, State, and Zip columns into one, and, capitalize the first letter of the city column, while we need all upper case for the state column. Then, we need lower case for the email column.

In Excel, we could use FlashFill to accomplish this, or, a combination of concatenation, text-to-columns, and functions such as UPPER, LOWER, and PROPER. Or, in the Get & Transform Query Editor, we could apply a series of transformations manually to handle the splitting, combining, and assigning the desired case.

But, what if we wanted the ease of FlashFill with the power of Get & Transform queries. Well, Microsoft pushed this out in a recent update. It is called New Column by Example. Let’s walk through the details.

Please note: Excel updates are provided automatically depending on your license, and if you have a perpetual license this update may not be available. Also, the Get & Transform tools are built-in to Excel 2016 for Windows and may not be available in other versions, or, you may need to download and install the PowerQuery add-in from Microsoft.


If you’d like to view the video version of the walkthrough, it is here. The detailed narrative is below the video.



We’ll walk through these key steps:

  • Pull data into the query editor
  • Name column
  • Address
  • Email

Let’s take them one at a time.

Pull data into query editor

First, we need to get the data into the query editor. We do this by selecting any cell in the table and clicking Data > From Table/Range. The data is pulled into the query editor, as shown below.

Query Editor by Jeff Lenning

With this done, let’s move to the name column.

Name column

Now, to split the Name column into two and capitalize the first letter of each name, we click Add Column > Column From Examples.

Just like FlashFill, we need to give it an example. So, we type the first name, in proper case, as shown below.

FlashFill by Jeff Lenning

When we hit Enter, we get to see a preview of the transformation in the remaining cells, as shown below.

Excel preview by Jeff Lenning

We click OK and the transformation is complete! We repeat this again to get the last name column.


Combining the city, state, and zip columns to create a single address column is just as easy. Add Column > Column From Examples. We enter the first example, which would be the city, state, and zip column, with the desired separators, as shown with the resulting preview, below.

Excel Transformation on Address by Jeff Lenning

We click OK to store the transformation. As you might imagine, the email address is just as easy.


We simply Add Column > Column From Examples. We enter the email address in lower case, click OK, and we are done.

If we wanted to clean things up a bit, we can delete any unnecessary columns, update the column labels, and click-and-drag to position the columns as desired. We can then click Close & Load to send the results back to Excel, as shown below.

Excel Results Table by Jeff Lenning

Thanks Microsoft for pushing out this update which combines two of my favorites!

If you have any other fun Get & Transform tricks, please share by posting a comment below!

Sample Excel file for practice: ColumnByExample




Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.


  1. Joyce on June 21, 2017 at 7:39 am

    We have Windows 13 at the office and not finding “Columns by example” feature

    • Jeff Lenning on June 21, 2017 at 7:42 am

      Hi Joyce … happy to help!

      So, that capability was pushed out by Microsoft in a very recent update, and, depending on your Excel license (perpetual or O365 subscription), you may not get the update.

      There are two ways to buy Excel 2016, a perpetual license and with a subscription. The perpetual license is the traditional way we buy Excel, we buy it once and can use it forever. This license doesn’t get the new feature updates. The O365 subscription is monthly, and, with the recurring subscription, Microsoft pushes new features out periodically.

      With all of the different Excel versions and licenses available, it is harder than ever to teach Excel because all features and functions aren’t available in all versions and platforms.

      Anyhow, hope this helps provide the background as to how the licenses work these days…thanks!


  2. Jerry on June 21, 2017 at 9:34 am

    Thanks, Jeff.

    But in your example, ny stayed ny, Ca stayed Ca, and Dc stayed Dc. In other words the FlashFill didn’t work on the state column. Any suggestions on how to make that part of the FlashFill automatic formatting, or do we have to go into Get and Transform formatting?

    Also, I wonder if there’s a way to easily transform ofarrell not to Ofarrell, but instead to O’Farrell.

    • Jeff Lenning on June 21, 2017 at 11:11 am

      Hi Jerry!
      FlashFill tries to detect a pattern, and does so by comparing your example to the data. So, in this particular workbook, the first state NC is all caps, and so when you type NC as the first one, Excel’s FlashFill will assume you just want to copy it as it is. It does it’s best to analyze and guess. If you change the data from NC to nc, and then enter NC and FlashFill it down, you’ll get the expected results.

  3. Joan Hauff on June 21, 2017 at 6:48 pm


    Thank you for showing this very powerful Power Query function :).


    • Jeff Lenning on June 21, 2017 at 6:49 pm

      Welcome … I love Power Query 🙂

  4. Evan Ames on September 28, 2017 at 5:53 am

    I cannot relate the time I spent fixing spreadsheets for attorneys where First and last name needed to be separated, need city, state and zip for a merge or making all the font formatting the same. I am not an Excel ace so it would mean banging my head into the formulas. So, I get to impress an attorney, and go on with my wonderful life.

  5. Joe on January 31, 2018 at 11:22 pm

    Hi Jeff,
    Stumbled on your site and your info is awesome.
    I even tested adding for a middle initial and as suffix and prefix. This tip helps fixing that State capitalization problem. When you do your example, put the example in the row you are fixing. I added the middle initial (A.) to Justin in row 3, so the example will go in the third row down. Worked for Sr. and Mr. and putting the example in row 2 fixed ny to NY along with the rest.
    Again, awesome info.

    • Mike Ward on February 27, 2018 at 4:33 pm

      Love it – in other words it fills up as well as down 🙂

  6. S on September 24, 2020 at 11:26 am


    As we see, we have to type value in each cell every time.

    If value in cell would be too long, how can we combine 3 cell’s data ?

    Thank you.

Leave a Comment