Ah, yes, Microsoft Excel. That one tool that we love to hate and hate to love. If you’re working in a job where you needlessly have to work on Excel sheets throughout the day, then I really feel for you. I was in one such job ages ago and it’s good to sit back now and take a nonchalant look at it.
If you, however, wanna be more productive while using similar functions while using Excel, let me suggest using Flash Fill.
Also on Guiding Tech
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.
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.
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.