Flash Fill (Excel feature in Office 2013)

Established: January 1, 2012

Our programming by example work (POPL 2011 (opens in new tab)), also recognized as CACM Research Highlights (CACM 2012 (opens in new tab)), ships as part of the Flash Fill feature in Excel in Office 2013. Here’s a small video (opens in new tab) illustrating this feature. Here’s another small video (opens in new tab) illustrating potential extensions.

Here’s the inside story of how it came about: Flash Fill Gives Excel a Smart Charge (opens in new tab)

Here are some other videos on FlashFill

Here is what popular media says about this feature

  • PC Magazine (opens in new tab): My favorite new feature, because it saves a tremendous amount of time-wasting effort, is called Flash Fill, and it’s one of many features where Excel acts as it it’s using its brain, not just its raw number-crunching power. With some experimentation, you may find that Flash Fill is smarter than you expect.
  • CNN Money (opens in new tab): “Excel 2013’s coolest new feature that should have been available years ago”
  • Tech Radar (opens in new tab): “If you’re trying to paste in data from a badly formatted report or an online credit card statement, the new Flash Fill feature is vastly easier than trying to work out how to split data into columns in just the right place. In fact it’s so good it feels like magic. Paste in the messy data, then start typing the piece of information you want to extract, like the date or the name of the company you made the payment to (without the unwanted details like the business number or foreign currency).”
  • Life Hacker Australia (opens in new tab): “Excel Flash Fill Is A Brilliant Time Saver”
  • PC Pro (opens in new tab): Simply type your intended target text in the next field, hit the Flash Fill button on the Data Ribbon, and Excel will complete the job for you, splitting out the text all down the appropriate column. Genius.
  • New Signature (opens in new tab): 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.
  • Ars Technica (opens in new tab): If you work with comma-separated data or other bulk-imported information in Excel—for example, Web analytics logs or server error logs or (heaven forbid) columnar format mainframe reports in text form—you probably spend a lot of time trying to extract bits of data stuck together with odd separators to get them into separate columns for analysis. Apparently, Microsoft’s Excel team heard about this. One of the shock-and-awe features of the Excel 2013 demo was the “flash fill” feature. This is a piece of background logic that watches what you’re putting into cells in a column and looks for patterns. If it recognizes the data as being extracted from text in an adjacent cell, it will automatically “ghost” the rest of the matching content into the column as you type
  • Big Hungry Geek (opens in new tab): I’ve been playing around with the Excel 2013 preview, and damn. It’s nice.
  • Sociable Blog (opens in new tab): Flash Fill Kiss formulas and macros goodbye, and say hello to Flash Fill. This new feature is the current buzz among Excel power users. From just one keyboard shortcut (CTRL+E), it learns your first move and fills in the rest of the data in the same way.
  • Times Of India (opens in new tab): Apart from the Metro interface and tight integration with Sky Drive, different programs part of Office 2013 have received some minor but useful features. The most notable feature in Excel is Flash fill. It reformats data automatically based on how you use Excel 2013 and auto-completes the remaining entries without needing a formula. Microsoft gives an example of a list that contains email ids with a pattern of firstname.lastname. Once a user fills two or three email ids, the others are prepared by Flash fill and the blank slots in front of the each name are automatically populated.
  • IoTechie (opens in new tab): “Flash fill is perhaps one of the biggest improvements to make Excel easier. The option will reformat and rearrange data automatically based on your own use of Excel 2013 and auto-complete remaining data with no formulas or macros required”
  • CRN (opens in new tab): For those who have done a fair amount of importing into Excel, you’ve undoubtedly had at least one encounter with the dreaded conundrum of having all the data go into a single column. Now, a feature in Excel 2013 called Flash Fill takes care of that. As you manually correct the import, moving it piece-by-piece into separate columns, this impressive feature observes your activity and offers to finish the job for you. That’s right. Excel 2013 moves data that was incorrectly placed into a single column and automatically moves it to multiple columns. In fact, Flash Fill recognizes any repetitive behavior pattern and offers to replicate it without macros or scripts.
  • ZDNet (opens in new tab): “One of the more useful features is Flash Fill, a technique that automatically formats pasted and manually entered data. If you’re adding data to a column, but haven’t explicitly used data formatting, Flash Fill will interpret your choices and automatically apply an appropriate format to additional content”
  • TechSpot (opens in new tab): “Excel’s particularly noteworthy new feature includes the new “Flash Fill” tool, which automatically separates data pasted from sites into separate columns”
  • PC World (opens in new tab): Microsoft has added several new whiz-bang data-analysis tools, including one called Flash Fill. When you take an element of data that you’ve already entered in one column and enter it in a second column, Flash Fill will predict that you intend to do that for every value in the second column, and will offer to fill in the second column for you accordingly.
  • Win8 Review: (opens in new tab)The new Flash Fill feature will likely be of interest to just about anybody using this program. Excel now keeps track of what is present in your spreadsheet already and tries to help out when it sees you filling in data using a pattern it sees in place elsewhere. The pattern analysis is quite accurate and will save a great deal of time in breaking fields apart or copying chains of information. To Flash Fill the column you’re working on, just select this option from the pull down Fill button on the Home ribbon.
  • pchelps: (opens in new tab)Quick Look: Excel 2013’s Flash Fill Feature
    This feature is on every tech journalist’s favorite Excel 2013 features list, and it’s no wonder.
  • pchelps blog: (opens in new tab)A Dozen Things You Should Know About Office 2013
    Excel’s new Flash Fill feature is getting ample buzz. No wonder: It automates processes such as splitting first and last name that used to require multiple steps using Text to Columns and/or lengthy nested formulas.
  • Wired (opens in new tab): Excel is now a lot easier for people who aren’t spreadsheet- and chart-making pros. The application’s new Flash Fill feature recognizes patterns, and will offer auto-complete options for your data. For example, if you have a column of first names and a column of last names, and want to create a new column of initials, you’ll only need to type in the first few boxes before Excel recognizes what you’re doing and lets you press Enter to complete the rest of the column.
  • TechWorld: (opens in new tab) 5 hot features of Microsoft Office 2013
    Introducing… Excel Flash Fill
    When downloading data to a spreadsheet, often it arrives with multiple pieces of information per cell that needs to be sorted out. With a new feature called Flash Fill, Excel figures out what information users are trying to pull into a separate column and automatically fills up a new column with that data. So if the task is to create a column of first names from a column of email addresses with the format firstname.lastname@business.com, users would type in the first name of the top address in a new column, then the second name and then Excel would fill out the rest of the new column with the rest of the first names, saving the bulk of the manual transcription.
  • Computer World UK (opens in new tab): “Office 2013 – Ten best features, according to Microsoft”
    Dress up your Excel data: New Flash Fill reformatting in Excel makes rearranging data much easier
  • Seattle Times (opens in new tab): 10 cool new tricks in Office 15

Here is what some blogs says about this feature

People

Portrait of Sumit Gulwani

Sumit Gulwani

Distinguished Scientist and Vice President