Ah, yes, Microsoft Excel. It’s a tool that we love to hate and hate to love. If you’re in a job where you have to work on Excel sheets unnecessarily throughout the day, I really sympathize with you. I used to do such a job a long time ago and it’s good to now sit back and look at it nonchalantly.
Parsing data in Excel? Let’s do it our way | shutter
However, if you want to be more productive using the same functions when using Excel, let me suggest using Flash Fill.
It’s like magic
If you have never used Flash Fill before, it will feel like a miracle to you. It works on a simple principle of identifying patterns and filling empty rows with these patterns. Let’s see an example.
split name
Let’s do a simple task of separating first, last and initials of employees in Guiding Tech (and Albert Einstein) using MS Excel. Column B now has names as you would normally see in a database. Column C onwards is where you want one (or more) first and last name components.
So all you need to do first is enter the first person’s name manually. Fight Enter and in the next cell, do not type anything. Instead, click Pour until it’s full menu and then select Flash fill. And voila, all other names will popularize themselves.
If you don’t fully trust me, here’s the unedited video (converted to GIF for ease of viewing) for the next column.
Movies off
There is also a shortcut to achieve the same thing. Using the same sheet, you manually fill in the first cell as we did above and press Enter. Now, press CTRL + E at the same time and Flash Fill will automatically fill the corresponding data in the empty cells.
However, you should continue to cross-check that Flash Fill has filled in the data correctly. In the initials column, I see that it is not working properly. However, when I enter data for the first two names (instead of one) it can fill in the rest with no problem. Hey, still a lot of time saved.
Please note: This method works as long as the input column has the same transparent format. If there is any deviation, it will not recognize the pattern.
Also works with numbers
Before people start worrying, let me say, it works well with numbers too. Whether it’s a decimal with n numbers before or after the decimal point, or even a date that needs to be split into dd/mm/yy format.
Another video here (reconverted to GIF), shows me manually entering the first cell info and Flash fill was able to select a template and automatically fill in the missing information below.
Avoid complicated formulas
The whole idea to use Flash fill is to avoid complicated formulas in MS Excel. If you can think of other scenarios as well, let us know in our forum.
Categories: How to
Source: thpttranhungdao.edu.vn/en/
