ExcelFrog Clear Excel Tutorials

home about

Excel: Awesome Things You Can Do With the Fill Handle

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.

Duplicate Things

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.

Delete Things

You can also delete anything with it, by using the same technique. But this time you need to drag the mouse upward.

Duplicate Things Faster

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:

• In the column B we can replace all existing values by "bbb".
• In the column E, since there are 5 values to its left, we will have 5 "bbb".
• In the column G, we cannot add more "bbb" with the double click technique since Excel has no idea how many it should duplicate it.

Create Sequence

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.

Use Flash Fill

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:

• Manually write the first pattern you want to get.
• Use the fill handle to duplicate the pattern you just wrote.
• Click on the small icon that appears below and select "flash fill".

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.

Conclusion

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 :-)

See all the tutorials on the homepage