Do you know the fill handle feature in Microsoft Excel? When you put your mouse in the bottom right corner of a selection, it transforms into a black cross. That's the fill handle.
This little thing can do some awesome things, and that's what we will cover in this short tutorial.
The most basic thing you can do with the fill handle is to duplicate the content of cells. It can be texts, numbers, or formulas.
Just select the cell(s) you want to duplicate, and drag the selection with the fill handle in the direction you want. It's really simple and fast.
You can also delete anything with it, by using the same technique. But this time you need to drag the mouse upward.
There's another way to duplicate data: double click on the fill handle. That's faster, however Excel will only duplicate a value if it knows how many time it should do it. Here's an example to better understand what this means.
You can see that:
Now let's see something more interesting: you can create sequence of numbers.
Select at least 2 numbers and try to duplicate them as explained earlier. Excel will understand that you are trying to continue a sequence, and will create the correct numbers.
It also works with dates. In the example below you can see weekdays (Monday, Tuesday, ...), months (January, February, ...), and full dates. This time only one item per column is required.
The most powerful feature of the fill handle is called flash fill. It lets you extract patterns from data. For this to work you need to:
Here's an example where we extract the first name from a column containing the full names of Star Wars characters.
And it works the same way for more complex patterns. Here I split a serial number in 2, and add a commas between the parts.
This is really awesome, and can save you a ton of time! However I recommend you to double check the results, because sometimes weird things can happen.
It's impressive that such a small tool can do so much. Once you master the fill handle, you can become a lot more productive with Excel. And spending less time in Excel is always a good thing :-)