AutoFill in Excel 2003 - Part 1
We look at AutoFill – a powerful feature in Excel used to “automatically fill in” a series of data based on a small amount of input.
By Michael Barden
In this edition of Office for Mere Mortals, we will have a look at AutoFill – a powerful feature in Excel used to “automatically fill in” a series of data based on a small amount of input.
WHAT IS AUTOFILL?
Creating a useful Excel spreadsheet will often involve some level of tedious data-entry. What you may not know is that Excel has an AutoFill feature that can be used to automatically fill in a sequence of labels or values along a column or row.
By entering data in one or more cells you can use AutoFill to copy or extend the selection of recognizable patterns such as:
- Numbers: 1, 2, 3…
- Days: Monday, Tuesday, Wednesday…
- Months: January, February, March…
- Dates: 1/25/2007, 1/26/2007, 1/27/2007…
- Calendar Quarters: Q1, Q2, Q3, Q4…
- Numbered Items: Chapter 1, Chapter 2, Chapter 3…
- Formulas: SUM, AVERAGE…
Using AutoFill can vastly reduce the time you spend on manual data-entry and help ensures more reliable and consistent lists. It can save your sanity as well!
AUTOFILL A BASIC SERIES OF DATA
To find out what AutoFill can do, start by opening up a new Excel spreadsheet and enter the initial value of a series into the first cell. In our example, we’ll use “January”. Since “January” begins a unique sequence (months of the year), we only need to enter a value in one cell.
If you wish to AutoFill a sequence of numbers or dates with an increment value other than 1, then you must input at least the first two values in the series. The difference between the two initial cells determines how the series should be linearly increased or decreased.
For example, if you entered “1” in the first cell and “3” in the cell beneath it, the increment would be 2 (as in 1, 3, 5, 7, etc). Similarly, if you entered “Mon” and “Wed” in consecutive cells, then the series would include every second day (as in Mon, Wed, Fri, etc). This would be a good place to point out that AutoFill will also recognize the short versions of days and months.
To begin the AutoFill process, select the cell (or range of cells) you have entered and locate the “AutoFill Handle”. This is the small black square in the bottom right-hand corner of the selected cell or range of cells. When you hover over this handle, the mouse pointer will turn into a thin black plus sign.
Click and drag the handle down the column or along the row until the drag area covers all the cells you want to appear in the series. Excel will display the value that will appear in each new cell as you extend the series using a ToolTipText balloon next to the cell. When you reach the last cell, release the mouse button and Excel will fill in the cells with the series up to that point.
AutoFill will only work on a single column or a single row at any one time. This means you cannot extend a selection down and to the right – this can only be performed in a number of separate steps.
When you reach the end of a finite AutoFill sequence (such as months of the year), the sequence will repeat. For example, if you start with “January” in the first cell, then the sequence will finish with “December” in the twelfth cell, and start again with “January” in the thirteenth cell.
THE AUTOFILL OPTIONS BUTTON
After dragging and releasing the AutoFill handle, an “AutoFill Options” button is displayed just below and to the right of your filled selection. Clicking this button will bring up a list of AutoFill options. The list of options will vary according to the type of data: text and formulas, numbers, months, days of the week, and dates. The main options are as follows:
- Copy Cells
- Fill Series
- Fill Formatting Only
- Fill Without Formatting
The “Copy Cells” option will occur by default if you haven’t provided a sequence that Excel recognizes. For example, if you select a cell that contains the text “Microsoft”, AutoFill will copy the selection into each cell in the drag area by default. If you wanted to copy the text “1st of January” down a column however, the AutoFill would automatically increment the series (2nd of January, 3rd of January, etc). In this case, simply click the “Copy Cells” option and all cells will revert to “1st of January”.
The “Fill Series” option will occur by default if you HAVE provided a sequence that Excel recognizes like our months of the year series (January, February, March, etc).
AutoFill will copy both formats and values from the starting cells to all other cells in the sequence. If you only want to copy the formatting of the original cells to other cells in the row or column, use the AutoFill and then select the “Fill Formatting Only” option. If you want to keep the original formatting of the cells populated by an AutoFill command, use the “Fill Without Formatting” option.
BASIC AUTOFILL BEHAVIOR
If you enter one or more numbered items (any text plus a number such as “Chapter 1”, “Section 1”, etc) AutoFill will extend the number within the selection by 1 each time (as in “Chapter 2”, “Section 2”, etc). If you were to enter “Section 1” followed by “Section 3”, then the number in the AutoFill sequence would increase by 2 each time.
Since AutoFill automatically increments numbered items, then you’d think it would increment simple numbers in the same way. This is not the case. If you start with a single cell containing the number “1” and extend it with the AutoFill handle, Excel will copy the number “1” to the rest of the cells (1, 1, 1, etc) rather than incrementing the series by 1 in each cell (1, 2, 3, etc).
The obvious solution is to simply enter the first two or three values in the series before extending it with AutoFill. This is necessary when you need the series to increase or decrease by more than one each time. For example, if you enter “10” and “9” in the first two cells will give a decreasing pattern of: 10, 9, 8, 7, etc.
An easy way to instruct Excel to AutoFill a series of numbers with only one starting value however is to hold down the CTRL key when you drag. Similarly, if you hold down the CTRL key when you drag to AutoFill a list of numbered items, it will copy the values rather than increment the numbers. You can think of the CTRL key as the “alternate to default function” key.
If you enter a date in any recognizable format, such as 01-31-07 or 01/31, AutoFill will extend the series one day at a time. For a list of acceptable date formats right-click the cell and choose “Format Cells” from the menu. On the “Number” tab of the “Format Cells” dialog box choose “Date” and a list of example date formats will be available. You can select which date format becomes your default here.
In next week’s edition of Office for Mere Mortals we’ll have a look at some more AutoFill options, and find out how to create a custom fill list.