Tuesday, January 04, 2005

Using AutoFill in Excel

The Auto Fill function in Excel is very powerful. However it pays to know just what the different ways of using it are in order to get the desired results first time and thus make it as efficient as its supposed to be.
I'll explain what the Auto Fill function is and then explain how to use it.

Auto Fill allows you to rapidly complete a series without typing them. For example all the months of the year can be typed by simply typing "January" in a cell (without the quotes) then filling down using the drag handle.

There are three separate ways of using Auto Fill and they all operate diffrently.
The first way is to use the Edit Fill Right, Left, Up or Down menu functions (CTRL-R or CTRL-D for the shortcuts). The menu functions also allow you to fill across multiple selected worksheets.

The second way is to use the drag handle at the bottom right corner of the cell. This is a little black cross. (With no arrows). Click and drag this up left right or down to auto fill from the starting cell(s).

The final way is to double click the drag handle to fill a column to the limit of the next leftmost column.

Excel is quite intuiutive in how it guesses what you are trying to do. However until you know the rules you could be left wondering as to why you got different results for the apparent same thing.
I will describe a few examples only because there are many. Hopefully what I describe will be enough to give you the tools to learn more on your own.

1) Repeat the same data or formula.
Type January in a cell. Select the cell and 11 cells below. Use CTRL-D. You should end up with 12 cells of January. Try the same with a formula and fill right using CTRL-R. The format and formula will be "copied" right.
2) Create automatic series.
Type January in a cell. Click and drag the drag handle down 11 cells. Notice that the indicator identifies the result of each cell as you go past it. Letting go should give you 12 cells with the 12 months of the year in it.
3) Create repetitive non numeric series.
Type Do in a cell and cat in the cell below. Select both cells. Now click and drag the handle down a number of cells. You should end up with dog and cat repeated over and over.
4) Create stepped series.
Type any two numbers in two cells one below the other. Eg 10 and 13. Select both cells. Now click and drag the handle down a number of cells. You should end up with a range of cells all 3 apart. Eg 13,13,16,19...
5) Auto complete a column.
Create a column of data. Click the first cell to the right at the top. Enter a formula, eg SUM(cell to left). Double click the drag handle. The cell should be copied down as far as there are contiguous cells in the column directly to the left.
6) Create the last calendar day of each month.
Type 31/1/05 in a cell and 28/2/05 in the cell to the right or below. Select both cells. Now click and drag the drag handle left or right as required 11 cells. You should end up with the last calendar date of each month.

There are many more uses. The more esoteric involve the use of series. However quickly creating date ranges are very powerful. If you want to know more please email me. Keep sending the requests in for specific answers to your queries.


Posted by: Jethro | 8:47 PM |