Dynamic Flash Fill📝
Are you aware that Flash fill can be dynamic in Excel, or you have no idea about what flash fill is? Don't worry I got you covered, Read On!
If you are a modern Excel user, there are high chances you may come across the Flash Fill option (famously known by its shortcut Ctrl + E). If you are not aware of the functionality below were a quick intro to the option:
Flash Fill automatically fills your data when it senses a pattern. For example, you can use Flash Fill to separate first and last names from a single column, or combine first and last names from two different columns.
Note: Flash Fill is only available in Excel 2013 and later versions. (For more details visit Microsoft Support Page.
There are two ways to use Flash Fill in Microsoft Excel, Automatic & manual mode.
🤖 Automatic Mode
How to enable Automatic Flash Fill?
Navigate to the File tab » Options » Advanced » Editing Options » Tick the Enable AutoComplete for cell values & Automatically Flash Fill
How it will work?
As you type in the adjacent cells of the original data and if Excel senses any pattern, it will suggest the relevant content for all the remaining data (Flash fill option), if we find it correct then we can press Enter and proceed further, if not then press Esc. Whereas in Manual Flash Fill you have to manually select the Flash Fill option or press Ctrl + E to get the relevant data.
⚡Dynamic Flash Fill (Power Query)
For this, we are going to use the Column form Examples option available under Add Column tab of the Power Query editor. Say from the above example we require the person’s Name, State, and Age.
Steps to be followed:
Navigate to the Data tab » Get & Transform Data group » From Table/Range
Create a Table (Rename it if you want)
Power Query Editor will be opened, rename the Query if you want to (I have changed the same into Dynamic Flash Fill
Navigate to Add Column tab » General group » Column from Examples
Rename the column & type the required example text for 1st cell and it will sense the pattern and fetch you the output for other rows along with Formula used to extract those results (which will help us to dynamically update the data according to our modifications & additions).
Do the same for all the required columns
Remove the Original data column
Navigate to the Home tab » Close group » Close and Load to » Select the destination location for loading the table.
Advantages 👍🏻
Works Dynamically when adding or modifying the data in the original table. After adding or modifying the existing data, hit the refresh option (or) Alt + F5 in the Splitted data table it will fetch you the updated data containing the modified data.
Initial Lengthy Process and subsequent easy access to the functionality, this option may consume some time in setting up the initial query but can be easily accessed thereafter by just hitting the refresh option.
ƒ Alternative by Latest M365 Text Functions
The following formulas can be used individually or combined to get the same results as Flash Fill:
TEXTSPLIT
TEXTAFTER
TEXTBEFORE
DROP
TAKE
TEXTJOIN
That’s from my side for today guys, see you in the next newsletter, and subscribe to our newsletter it’s free and most importantly useful in your day-to-day work!