SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Microsoft Office Tutorials, Downloads, Tools and Programs like Excel, Word, Outlook, Project, etc.


Microsoft Excel 2013 Flash Fill Data Entry Tool

Microsoft Excel 2013 Flash Fill is one of new Excel features introduced with Microsoft Office 2013 product family. Flash Fill is a data assistant which helps Office 2013 users to enter data automatically by detecting a pattern between data columns and data values.

What is Flash Fill?

Flash Fill is a data entry tool that completes data entry for the Excel 2013 user. While the user is entering data in an Excel column, if it detects a pattern in your entries, Flash Fll will complete missing row values for the user.

As the name Flash Fill implies filling values into column cells will take a flash in time to complete for Excel 2013 using Flash Fill feature. The only requirement for successful flash fill task is to recognize a valid pattern in Excel 2013 users' data entries.

Microsoft Excel 2013 users can either activate flash fill feature by pressing Ctrl+E on the empty cell that they want to be filled automatically. Or they can follow the Excel menu: Data > Data Tools > Flash Fill

Microsoft Excel 2013 flash fill

Concatenate Cells using Flash Fill in Excel 2013

As a database developer I had frequently used Microsoft Excel to concatenate data columns to create SQL commands like Select, Update or Delete statements. I used to benefit from in-line formulas to concatenate two Excel columns into a third column. But using the new Excel Flash Fill feature, it is very easy to concatenate two cells without the requirement of the third column. Let's work on a sample case.

I have ID column with integer values stored.
In a second column titled as Query, I wrote the following text: "Select * from products where Id = 15"
Please note that 15 is the corresponding Id column value in the same line in Excel 2013 sheet.

So the simplest pattern that I expect Microsoft Excel 2013 flash fill engine to detect is concatenating a static text plus ID cell value. Let's see if Excel Flash Fill is genius enough to detect pattern I want it to apply automatically.

Go to the empty cell in the following row, and then press Ctrl+E to activate Excel 2013 flash fill automatic pattern detection and auto filling in values.

Microsoft Excel 2013 flash fill

Microsoft Excel 2013 will automatically fill that column values for you by concatenating text with ID column which is the pattert that Flash Fill detected.

A flash fill pointer will be displayed which enables Excel 2013 user to accept changes which is the default behavior. Excel user can also choose to undo Flash Fill using the context menu options easily.

Microsoft Excel 2013 flash fill

What happens if new Microsoft Excel 2013 Flash Fill feature fails to detect a pattern between data and sample column value you entered? As seen in below Excel message dialog screen, Microsoft Excel will request the user to supply more sample data which might help it to detect pattern between data and desired output.

Microsoft Excel 2013 flash fill

Microsoft Excel
We looked at all the data next to your selection and didn't see a pattern for filling in values for you.
To use Flash Fill, enter a couple of examples of the output you'd like to see, keep the active cell in the column you want filled in, and click the Flash Fill button again.

Microsoft Excel 2013 users can read Microsoft Excel 2013 Flash Fill samples for more examples on how this brand new feature can be used




Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.