We often know so many tools. The big question is when and how do I use the tool? Sharing small snippets on using Excel flash fill along with a few use cases.
What is Flash Fill in Excel?
Excel Flash Fill is a special tool that analyses the information you are entering and automatically fills data when it identifies a pattern.
The Flash Fill feature was introduced in Excel 2013 and is available in all later versions of Excel 2016, Excel 2019, and Excel for Office 365. Those of you who prefer working from a keyboard most of the time, can run Flash Fill with this key combination: Ctrl + E
How to use Flash Fill in Excel?
Usually Flash Fill starts automatically, and you only need to provide a pattern. Here’s how:
1. Insert a new column next to the column with your data.
2. In the first cell of a newly added column, type the desired value.
3. Start typing in the next cell, and if Excel senses a pattern, it will show a preview of data to be auto-filled in the below cells.
4. Press the Enter key to accept the preview.
5. If the preview doesn’t appear, press Ctrl + E or Flash Fill option from the Data Ribbon
A few use cases:
1. Splitting any Text into columns based on a pattern (Name, Space, Symbol etc.)
2. Extracting specific content within a particular cell (Ex. Extracting PAN from GSTN, Invoice number from a cell containing number and text etc.)
3. Extracting specific data from the text which is not split into columns (Ex. In the case of bank audit, notepad file has all text in one column)
4. Combining data from several cells (Instead of a complex concatenate formula)
5. Clean up Data (space, special characters can be removed)
6. Format text, numbers, dates, phone numbers etc.
Sources and further Reading at:
*****
Compiled by CA Narasimhan Elangovan with inputs from Nakul Pranav, team KEN & Co.
Disclaimer : This publication contains information in summary form and is therefore intended for general guidance only. All information is provided in good faith. KEN & Co. makes no representation or warranty of any kind, express or implied, regarding the accuracy, adequacy, validity, reliability, availability, or completeness of any information provided. It is not intended to be a substitute for detailed research or the exercise of professional judgment. Detailed notification and regulations by the respective Departments is awaited.
Author Bio
