Using AutoFill

Perhaps one of the most useful features of Excel is a shortcut known as AutoFill. AutoFill can make the tedious task of writing lists, any series of data, or repeating formulas on long columns or rows of data infinitely easier by doing all typing for you.

AutoFill with a Series:

1. In any cell, type the first number or text-number combination in your series. For example, you may wish to add the date to a long series of data, or you may wish to rank data ordinally. Here are shown several examples of the beginnings of a series that may be used with Autofill.

2. Highlight the cell or cells to be AutoFilled. Notice on the lower right-hand corner of the selected area -- you should see a small blue square, which is known as the AutoFill Handle. Click and drag this handle downward or across (depending on whether you want data in column or row format) and your selection box will expand. When you let go of the mouse button, the newly selected cells will fill with data to continue your series.

AutoFill with a Formula -- Absolute and Relative References:

AutoFill works with formulas in the same manner as described above. Just click and drag the AutoFill handle to repeat the same formula on different data. However, you may need to make sure that one or more parts of the formula stay the same while others adapt to the rest of your spreadsheet.

For example, you may have a gradebook spreadsheet containing the total points for all of your students. If you wish to easily find the students' grade percentages, you might type your formula (student's total points / total points available) for the first student and AutoFill it down to calculate the rest of the class.

You may notice as you do this, however, that the AutoFilled percentages don't come out as they should.

This is because you have probably used a relative reference. When you create a formula, references to cells or ranges are usually based on their position relative to the cell that contains the formula. This means that the formula is not looking for the value in cell K32 after you AutoFill -- instead, it is looking for a cell that is one column to the left and 27 rows down from your original formula entry, which was entered into cell L4. Check the Formula Bar to see how the formula has changed in your new entries.

To avoid this problem, Excel allows you to input absolute references instead of relative ones. If you don't want Excel to adjust references after AutoFilling or copying a formula to different part of your spreadsheet, simply type a dollar sign ($) before the references that do not change.

Now, when you perform your AutoFill, the reference to K32, which contains the total points available for the class, will remain the same for every calculation.