The Excel Auto Fill Feature

A couple of days ago I discovered a very interesting double-click feature in Excel. One that probably already exists for ages – the oldest version that I was able to try it out on is Excel 2002 and it worked – but due to being used to other habits I just never found out about it.  Until now.

When a cell (or a range of cells) is selected, you see a thicker black border around the selection and the bottom-right corner has got a small square attached.

You can drag that squared corner down to get copy-like behaviour. More precisely, when one cell is selected and you drag it down then the value of the selected cell gets copied over into the cells further down, up until the cell where you stop dragging.

Note: the same effect also works horizontally but in real-life circumstances you probably won’t need it much.

When multiple cells are selected and you drag the bottom-right corner down, Excel will apply some logic to continue the series that it possibly detects.

For instance if you have two cells selected with the values 2 and 4, the next cells will get 6, 8, and so on. Or when the first cell contains 2010/10/30 and the second 2010/10/25, the next cells will get date values going down by 5 days per cell.

If you don’t like the way the series gets applied, there’s a dropdown poping up at the bottom-right of the new selection at the moment that you stop dragging. In that list you get several different Auto Fill Options, depending on the data type of your selected cells.

I’m sure this is no news to you so far.

But do you know what happens when you just double-click that small bottom-right corner instead of dragging it?

I didn’t, until I just tried it out this past week. The reason that I tried this was because I needed a formula copied down in about 20,000 rows (hey, I’m a data guy, remember?) and I didn’t want to waste my time waiting for Excel to scroll down just to the bottom of the list while dragging the corner. So I double-clicked and there came the discovery of the week! It applied the same function as what you get when you drag the corner down, all the way down to the last row of data! Isn’t that great? From now on I think I’ll always just double-click instead of drag, much faster!

And you even get the same popup to select another Fill Option.

Have Fun!

Valentino.