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

Flash Fill in Excel

How to use Flash Fill in Excel?

How to use Flash Fill

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.)

A few use cases  A few use cases 2

2. Extracting specific content within a particular cell (Ex. Extracting PAN from GSTN, Invoice number from a cell containing number and text etc.)

Extracting specific content Extracting specific content 2

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)

Extracting specific data Extracting specific data 2

4. Combining data from several cells (Instead of a complex concatenate formula)

Instead of a complex concatenate formula Instead of a complex concatenate formula 2

5. Clean up Data (space, special characters can be removed)

space, special characters can be removed space, special characters can be removed 2

6. Format text, numbers, dates, phone numbers etc.

Format text, numbers, dates, phone numbers etc. Format text, numbers, dates, phone numbers etc. 2

Sources and further Reading at:

https://www.ablebits.com/office-addins-blog/2019/03/06/flash-fill-excel-examples/#what-is-Excel-flash-fill

https://support.microsoft.com/en-us/office/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7

*****

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

Qualification: CA in Practice
Company: KEN & Co.
Location: Bengaluru, Karnataka, IN
Member Since: 15 Mar 2020 | Total Posts: 6
I am a futurist, a GRC Professional, Key Note Speaker and partner of KEN & Co., Chartered Accountants, Bengaluru, India. I am an International Speaker on IS Assurance, Data Analytics, Cyber Security, Forensic Audit, Data Privacy Laws and Emerging Technologies. I am a Blockchain Functional Consul View Full Profile

My Published Posts

More Under CA, CS, CMA

Leave a Comment

Your email address will not be published. Required fields are marked *

Search Posts by Date

April 2021
M T W T F S S
 1234
567891011
12131415161718
19202122232425
2627282930