EXCEL COPYING WITH THE FILL HANDLE

Lesson 7 - Quick Copying Using the Fill Handle

COPYING WITH THE FILL HANDLE

Excel has yet another way to Copy (not Cut) data in cells and this is via the fill handle . You may have noticed that when you select a cell or range of cells the bottom right of the outlined cell or range has a small black square. This is called the Fill Handle. The Fill Handle can be used for filling a range with increments of any choice we choose, in fact it can do much more than just this, but we will only look at using it to Copy and to increment. To see what we mean try this:

Type The Number 1 In Cell A1
Select Cell A1 And Place Your Mouse Pointer Over The Bottom Right Corner Until The Mouse Pointer Changes To A Small Black Cross.
Hold Your Left Mouse Button Down And Drag Down To Cell A10
Your Range Should Now Contain The Number 1 In All Cells

This is using the Fill Handle to Copy. Now try this:

Type The Number 1 In Cell B1
Select Cell B1 And Place Your Mouse Pointer Over The Bottom Right Corner Until The Mouse Pointer Changes To A Small Black Cross
Now Double Click The Fill Handle With Your Left Mouse Button

This should fill the range B2:B10 with the number 1. What Excel does is look in the range immediately to the left and if there is data in that range it copies down to the last cell containing data. If the range to the left is blank, it looks to the right, if there is data in that range it copies down to the last cell of that range. If both ranges on the left and right are blank, nothing happens when you double click the Fill Handle.

Let's say we have the number 5 in cell A1 and we wish to fill down to cell A100 in increments of 5.

To do this, follow these simple steps.

Type The Number 5 In Cell A1 And The Number 10 In Cell A2.
Select Both Cells A1 And A2.
Place Your Mouse Pointer Over The Black Square, Bottom Right Corner Of A2 Until The Mouse Pointer Changes To A Small Black Cross.
Holding Down The Left Mouse Button Drag Down To Cell A100.

Excel will fill the range A3:A100 with increments of 5 up to 500. What Excel does in this instance is look at the value of cell A1 and A2 (the selected cells) sees that there is a difference of 5 (10-5) and increments by that number.

As you can imagine this can be very handy even for a simple increment like this.

There is one more feature of the Fill Handle we will look at and that is what's known as Custom Lists. By default Excel has two types of Custom Lists, these are Days of the Week and Months of the Year. Try this simple exercise.

Type The Text January In Cell A1.
Select Cell A1 And Place Your Mouse Pointer Over The Bottom Right Corner Of A1 Until The Mouse Pointer Changes To A Small Black Cross.
Holding Down Your Left Mouse Button, Drag Down To Cell A12.

This time Excel will have filled the 12 Months of the year for us. If we had typed a day of the week Excel would fill the range with the 7 days of the week, looping and Starting again after it gets to the seventh. While these are the only two defaults, Excel would still recognize Jan or Mon or any other day or month abbreviation. Not only this but we could type any text followed by a number such as Quart1 and use the Fill Handle to increment by 1 so you would see Quart2, Quart3 or type Quart1 in A1, Quart3 in A2 and use the Fill Handle and Excel will increment by 2