Excel Flash Fill comes in handy when using string manipulation functions like the trim() method to the entire column. Now, the process would not seem tedious for a couple of rows but can be a little hectic when you have thousands of rows. Excel Flash Fill can be employed to finish the time-consuming task within a couple of keystrokes.
What Is Excel Flash Fill?
Excel Flash Fill is a feature of Microsoft Excel where Excel can sense a pattern in the cell and apply the logic to extract a similar resultant pattern out of the remaining cells in the table.
The definition could be a little confusing, so go through an example.
Example for Excel Flash Fill
Now, assume that you have an automobile product data table. The table has one column that stores the geographical region code, manufacturer code, and automobile type code.
Example:
Let us assume that you have an automobile code as “NOR-HON-SUV”
Here, NOR is North for Geographical Code, HON is Honda for Manufacturer Code, and SUV is an automobile type code.
Imagine you need to identify these patterns individually and generate three different columns based on geographical region code, manufacturer code, and automobile type code from the original column.
This could be done using a bunch of functions like TRIM() etc. But, the process could seem a little complicated and time-consuming. What if you could do it with a few keystrokes?
That is precisely where Excel Flash Fill comes to play. You can press a set of keystrokes, and the data will be automatically split and segregated.
Sounds interesting, right? Now, enter the next section and try to understand how exactly Excel Flash Fill works in real-time.
How to Implement Excel Flash Fill?
In the following example, you will include product-related data. In column A, you have the product code, product serial number, assembly code written altogether. Now, the goal is to separate them into three different columns. As shown in the first row of the image.
Now to do so, all you have to do is go to the next successive cell and press and hold the control key in Windows or Command key in Mac, then press the alphabet 'E.'
CTRL + E or CMD + E
Now, sometimes, very rarely, Microsoft Excel might fail to recognize the patterns; in such scenarios, you can help it by filling two or more rows to help Excel get familiarised with the pattern requirements. Then, you can press CTRL+E or CMD + E to fill all the successive cells.
A sample is shown in the image below.
The final resultant data table would look like this in the image shown below.
This feature can be applied to any type of data, regardless of string length or data type.
For example, print the content in the 4th to 8th location of strings with different string lengths.
Let us consider the example below. In this example, you have strings of different lengths, and also, you have included a cell with numerical data.
This example has filled the first two resultant cells so that excel could compile the expected pattern. Now, in the third cell, it has used the Excel Flash Fill shortcut, CTRL + E or CMD + E, and the remaining cells have gotten filled with data between the 4th to 8th location of strings as shown below.
With this, you have come to an end of this tutorial on Excel Flash Fill.
Looking forward to a career as a Business Analyst? Check out the Business Analytics Certification Training with Excel and get certified today.
Wrapping Up
Barcode in Excel can be your next step in learning data analytics. Barcodes are computer-readable information generated using a collection of black lines and white spaces. Using Excel, you can represent your data in the form of barcodes or even encode your confidential data.
Interested to learn more about Business Analytics? Or wish to get trained and certified to become a successful Business Analyst? Then feel free to reach out to the Business Analytics certification course from Simplilearn. Ranked among the five topmost business analytics courses. This Simplilearn program is offered in partnership with Purdue University is an outcome-driven training and certification program that helps you master the fundamental concepts of statistics and data analytics.
Have any questions for us on this tutorial on "Excel Flash Fill"? If you do, or maybe you have doubts about our certification course, do reach out to us by sharing them as comments below. Our team of experts will address them and will be happy to answer them at the earliest.
