Sat 05 Sep 2015

Tips & Tricks 107 - Autofill on a Filtered List

By |Saturday, September 05th, 2015|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

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.....

Sat 27 Sep 2014

Tips & Tricks 55 - Quickly Fill in Cells with Dates from M to N

By |Saturday, September 27th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

1. Put the start date in the cell where you want your first date to be. Let's put 6/1/14 as an example.

2. Select that Cell and in Home Tab, go to Fill and Select Series

1

3. Select Series in Columns if you want Column to be populated (Most likely case). Put End Date in in stop value as we want to generate dates till that end date and OK to generate list of numbers. Step value consists of increments by which dates have to be incremented. (If you want to space out dates with one week interval, put 7 in step value)

1