Skip to content

AutoFill in Excel 2003 - Part 2

We delve even further into the AutoFill options in Excel and find out how to create a custom fill list.

By Michael Barden

Last week, we had a look at the basics of AutoFill in Excel – a function that is used to “automatically fill in” a series of data based on a small amount of input.

In this edition of Office for Mere Mortals, we will delve even further into the AutoFill options and find out how to create a custom fill list. We’ll also mention a few tips and tricks so you can get even better mileage out of your AutoFill usage.


THE AUTOFILL RIGHT-CLICK DRAG MENU

Last week, we looked at the AutoFill button options that appear just below and to the right of your filled selection. What we didn’t tell you was that you can see a complete menu of AutoFill options if you hold down the right mouse button while dragging the AutoFill handle. When you have selected the area to AutoFill, a menu will appear detailing all of the available AutoFill options. Some of these options will appear “grayed out” if they are not applicable in the current situation.

The Right-Click AutoFill Options Menu image from AutoFill in Excel 2003 - Part 2 at Office-Watch.com

Say you wanted to enter a series of dates, but only wanted the series to include weekdays. The best way to do this is to enter the first date in a cell and drag the AutoFill handle using the right mouse button. Simply select the “Fill Weekdays” option from the menu and all weekend dates will be skipped. Similarly, the “Fill Months” and “Fill Years” options will provide different date sequences.

Since we are already familiar with “Copy Cells” and “Fill Series” from last week’s newsletter, we’ll now focus on the last three options on the right-click drag menu: “Linear Trend”, “Growth Trend” and “Series”.

In a “Linear Trend”, Excel calculates the average difference between each value in the series you selected. It then adds it to each succeeding value in the AutoFill range. For example, the initial values “1” and “3” would create a linear series as follows: 1, 3, 5, 7, 9, etc – where each value is 2 more than the last.

In a “Growth Trend”, Excel calculates the percentage of difference between each value in the series you selected. It then multiplies that amount to each succeeding value in the AutoFill range. For example, the same initial values “1” and “3” would create a geometric series as follows: 1, 3, 9, 27, 81, etc – where each value is 3 times bigger than the last. As the name suggests, a growth trend can be useful in trying to project future growth patterns based on existing data.

Choosing the “Series” option will display the “Series” dialog box that gives you even more options all in the one place including the step value (increment or decrement) and stop value for a series.

The Series Options Dialog image from AutoFill in Excel 2003 - Part 2 at Office-Watch.com

CREATING A BASIC CUSTOM FILL LIST

What do you when you regularly have to input a sequence into your Excel spreadsheets that Excel isn’t able to predict? After all, Excel can only recognize a small number of sequences by itself – like linear sequences, days of the week, dates, etc.

The answer is to program Excel’s AutoFill function to recognize this sequence by creating your very own “Custom List”. To do this, start by navigating to “Tools | Options” and click on the “Custom Lists” tab.

Creating a Custom Fill List image from AutoFill in Excel 2003 - Part 2 at Office-Watch.com

Click on the “NEW LIST” option in the left-hand pane and enter your list entries in order in the right-hand pane. For example, enter the colors of the rainbow: red, yellow, pink, green, purple, orange and blue – making sure to press the Enter key in order to separate list entries. When you have populated your list, click the “Add” button and your rainbow color custom list will appear as the last entry on the left-hand “Custom lists” pane.

If you are following our example, go back to your Excel spreadsheet and type “red” into a cell. Now grab the AutoFill handle and populate the rest of your custom list. Conceptually easy, but potentially powerful.


IMPORTING A CUSTOM FILL LIST

For even more convenience, the “Custom Lists” tab on the “Tools | Options” dialog box has an “Import list from cells” feature. That means you can open an Excel spreadsheet where a list of data that you know you will need to use on a regularly basis exists and import the list directly into your “custom lists”. Simply click the icon on the right of the “Import list from cells” text box and select the range of cells that represents the list. Return to the dialog box by clicking the icon again and you will notice that the cell range has been input into the text box. To complete the process, click the “Import” button and the list corresponding to the range of cells will now be saved as a custom list.

ADDITIONAL TIPS AND TRICKS

AutoFill is good way to copy a formula from one cell along a row or column. This is especially useful for copying formulas that total or average columns or rows. This is because AutoFill will not only copy the formula, but it will also adjust the relative references as needed. This makes a formula that totals all the numbers of a single row able to be copied down the totaling column using AutoFill so that each new formula calculates the total of its row automatically.

Following on from this, if you double-click the AutoFill handle it will extend the selection until it matches the length of the content of the adjacent column on the left. This would be perfect for our “totaling formula” example. If the left column is blank, then it will match the adjacent column to the right. If both columns contain data, it gives precedence to the length of the left column. If neither left nor right columns contain data, then this function will not do anything.

 

About this author