25 March, 2022

Wonders of Excel: FILL HANDLE

As you probably know, Excel Fill Handle is located in the bottom right of any active cell or the bottom right cell of a selected range. It's a quite small black square visible in this snip:

Small it is, but it's very important to know how to utilize it, as it can be really helpful in eliminating lots of manual typing and saving time in working with data entry... and much more than this. It can be used for filling any range of cells with increments of any choice you want to select.

To utilize the Fill Handle, first you need to fill one or more cells with values (numbers or text, incl. dates and other custom lists). Hover your mouse pointer over the small square until it changes to a small black cross, then you have couple of choices: you can left-click, right-click or double-click (in some situations); click and drag the mouse in the direction you want to fill up the range of cells with series or custom lists that follow your pre-defined pattern.

In simple cases the pattern can be defined with just two numbers or two text entries. In case of more complex patterns you may need to enter values in three or even more cells to define your pattern. Custom patterns, like days of the week or months are identified even with just one entry (e.g. Mon, Feb).

All patterns are auto-filled with some increments (or decrements). E.g., pattern 1, 3 will continue filling up with 5, 7, 9, etc.; pattern Jan,Mar will continue filling up with May, Jul, Sep, etc. You can use e.g. a weekly increment (28-Mar-22, 04-Apr-22) to fill your list of dates with consecutive Mondays only, as illustrated here:

But there is more to it. What if you define your pattern using values entered in three cells, and what if the values are not in incremental or decremental order? Here are just three examples of such patterns (cells with yellow fills), defined randomly:

Look at the fragments of series located below them, and the increments/decrements shown to the right of them. It doesn't matter, how the numbers in the patterns vary - increase or decrease, linearly or not - all the subsequent numbers - with exception of n4 - created by the patterns increase/decrease linearly and in specific steps (increments/decrements) predetermined by each of the patterns.

In all instances of these 3-number patterns we can determine the value directly following the pattern (i.e. n4) using the following formula:

n4 = n3 + SUM(n1 : n3) / 3 - n1 

where n1, n2, n3 are consecutive numbers of the specific pattern.

And the value of the increment/decrement (step) can be determined using this simple formula:

step = 0.5 *(n3 - n1)

Just couple of the "wonders" possible with using that small Fill Handle. Quite interesting, isn't it?

No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.