If you, however, wanna be more productive while using similar functions while using Excel, let me suggest using Flash Fill.
It’s Like Magic
If you’ve never used Flash Fill before, it will feel like magic to you. It works on a simple principle of identifying patterns and filling up vacant rows with these patterns. Let’s look at an example.
Dividing Names
Let’s take a simple task of separating names, first names and initial of employees within Guiding Tech (and Albert Einstein) using MS Excel. Column B currently has names like you’d normally find in a database. Columns C onwards is where you want only one (or more) element of the full name. So, all you need to do is first type the name of the first person manually. Hit Enter and in the next cell, don’t type anything. Instead, click on the Fill menu and then choose Flash Fill. And voila, all the other names will self-populate on their own. If you don’t fully trust me, then here’s an unedited video (converted to GIF for easy viewing) for the next column.
Keyboard Shortcut
There is also a keyboard shortcut to achieve the same thing. Using the same sheet, you fill out the first cell manually like we did above and hit Enter. Now, hit CTRL+E at the same time and Flash Fill will automatically fill out the blank cells with the corresponding data. You should, however, keep cross-checking if Flash Fill has filled the data correctly, or not. In the initials column, I saw that it wasn’t doing it right. But, when I entered the data for the first two names (instead of one) it was then able to fill out the rest without any issues. Hey, that’s still a lot of time saved. Please Note: This method works as long as the input column has the same format throughout. If there is any deviation, it won’t recognize the pattern.
Works With Numbers too
Before everyone starts to freak out, let me state, it works fine with numbers too. Whether it be decimals with n numbers before or after the decimal point, or even dates that need to be segregated into dd/mm/yy format.
Another video here (converted to GIF again), showing that I entered the first cells info manually and Flash Fill was able to pick up the pattern and filled the missing info below automagically.
Avoid Complex Formulas
The whole idea to use Flash Fill is to avoid complex formulas within MS Excel. If you can think of other scenarios too, do let us know in our forums. The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.
