Tips & Tricks 107 – Autofill on a Filtered List

Everybody is quite aware about Autofill. There are various ways to Autofill. Refer to following article for Autofill –

Article 7 – Generate a Sequence of Numbers

Now, apply a filter on your range and all the techniques fail. If you drag, all cells are filled with 1 and no other techniques also work. The reason is that Autofill works only on a contiguous range. Once, you apply filter, the range becomes non-contiguous.

Now, here comes the trick –

1. Apply the filter.
2. Let's assume that first row is 3 and you wanted to fill it in column B.
3. Put following formula in B3 and drag down
=COUNTIF($B$1:B2,"<>"&"")

If you don't want to drag down –
3.1 Put the above formula in B3.
3.2 Select all the cells including B3.
3.2 Press F2
3.3 CTRL+Enter

Above steps will fill the filtered list with 1, 2, 3…..