When you have a lot of data to enter into your Excel 2013 spreadsheet, and that data consists of some type of repeatable pattern or sequence, you can save time by using AutoFill.
To use AutoFill, you select the cell or cells that already contain an example of what you want to fill and then drag the fill handle. The fill handle is the little black square in the lower-right corner of the selected cell or range.
Depending on how you use it, AutoFill can either fill the same value into every cell in the target area, or it can fill in a sequence (such as days of the month, days of the week, or a numeric sequence such as 2, 4, 6, 8). Here are the general rules for how it works:
When AutoFill recognizes the selected text as a member of one of its preset lists, such as days of the week or months of the year, it automatically increments those. For example, if the selected cell contains August, AutoFill places September in the next adjacent cell.
When AutoFill doesn’t recognize the selected text, it fills the chosen cell with a duplicate of the selected text.
When AutoFill is used on a single cell containing a number, it fills with a duplicate of the number.
When Auto Fill is used on a range of two or more cells containing numbers, AutoFill attempts to determine the interval between them and continues filling using that same pattern. For example, if the two selected cells contain 2 and 4, the next adjacent cell would be filled with 6.
In the following exercise, you AutoFill cell content using two methods.
In the Lesson 5 Mortgage file from the preceding exercise, select cell A8 and type Amortization Table.
Type the following:
In cell A10, type Date.
In cell B10, type Pmt#.
In cell A11, type January 2015. (Note that Excel automatically changes it to Jan-15.)
In cell B11, type 1.
Click cell A11 and move the mouse pointer over the fill handle.
The mouse pointer becomes a black crosshair, as shown in this figure.
Drag the fill handle down to cell A22.
The first year of dates fill in the cells, as shown in this figure.
Click cell B11 and drag the fill handle down to C22. The same number fills all the cells. That’s not what you want for this exercise, so press Ctrl+Z to undo the fill.
Click cell B12, and type 2. Select B11:B12 and then drag the fill handle down to cell B22.
The figure shows the completed series.
Select A22:B22 and drag the fill handle down to B190.
Both series are filled in, down to row 190, where the date is December 2029 and the payment number is 180.
Here you do Step 7 because the number of periods for this loan is 180 (see cell C5), so the number of payments should be 180 in the amortization table.
Press Ctrl+Home to return to the top of the worksheet.
Click the Save button on the Quick Access toolbar to save the changes to the workbook.
Choose File→Close to close the workbook.