XL2000: How Auto Extend List Behavior Works

ID: Q231002

The information in this article applies to:

Microsoft Excel 2000

SUMMARY

This article describes the Auto Extend List feature, which was introduced in Microsoft Excel 2000 to address one of the most common tasks in Excel: extending an existing list. When you turn on extended formats and formulas, Excel automatically formats new data that you type at the end of a list to match the preceding rows, and automatically copies formulas that are repeated in every row. This new feature is turned on by default.

MORE INFORMATION

Selecting the Auto Extend List Feature

To turn the Auto Extend List feature on and off, follow these steps:

On the Tools menu, click Options.

On the Edit tab, click to select or clear the Extend list formats and formulas check box.

How Auto Extended Formatting Works

For formats or formulas to be extended in a list, they must consistently appear several times in the preceding rows. For a format to be extended, the format must appear in at least three of the five list rows preceding the new row. For formulas, the formula must appear in at least four of the five list rows preceding the new row. For extended formatting to work, you must exit edit mode. Because of this, auto extend formatting is not triggered by a Paste action.

An Example of Auto Extending a Format

Open a new workbook in Microsoft Excel.

Enter the following data:
```
A1: apples
A2: oranges
A3: pears 
```

Select cells A1:A3 and click Cells on the Format menu.

In the Format Cells dialog box, click the Patterns tab. Select a color, and note which color you select. Click OK.

Enter the following data:
```
A4: bananas 
```

When you press ENTER, the format applied to cells A1:A3 is now automatically applied to cell A4.

An Example of Auto Extending a Formula

Open a new workbook in Microsoft Excel.

Enter the following data:


A1: 1   B1: 6   C1: =SUM(A1, B1)
A2: 2   B2: 7   C2: =SUM(A2, B2)
A3: 3   B3: 8   C3: =SUM(A3, B3)
A4: 4   B4: 9   C4: =SUM(A4, B4)

Enter the following values:
```
A5: 5   B5: 10 
```

When you press ENTER, the formula in cells C1:C4 is automatically copied into cell C5.

Rules for Auto Extended Formatting

When you enter a blank cell, Excel checks whether there is an actual list, and where in the list the active cell is located. Excel scans five cells up from the active cell, checking for consistent cells. If the cells seem consistent, Excel assumes that the active cell below is in a column of a list. Excel then scans five cells to the left for consistent cells. If these five cells are consistently formatted then Excel assumes that the active cell is in a row list. If Excel does not find cells consistently formatted in either direction, it assumes that the active cell is not in a list.

After determining that the active cell is in a list, Excel applies the following additional rules:

It ignores any filtered items and does not extend the formatting.

If more than one cell is selected, Excel only looks at the active cell in the selection, not the selected range.

If you filled a range of cells using CTRL+ENTER, Excel ignores the cells, and the auto extend feature does not activate at all.

If Excel encounters a blank cell when looking at the list, Excel still considers the cell when checking for formatting consistency.

Excel looks at each cell in the list independently. This means that if there is a merged cell in the list and that cell is different than the cell you are editing, Excel treats the cell as if there was no match and does not extend the formatting.

When Auto Extended Formatting May Not Work

Because Excel automatically applies Auto Extended Formatting, there are situations that may cause this feature to not work exactly as prescribed. The following list explains when the Auto Extend formatting may not work as expected:

Formatting does not auto extend in columns (copy down) if data entered in a new cell is not similar to data in cells above. For example, if you have formatted cells as bold, align center, and shaded, in a column containing column or row headings (or any text) and then enter a numeric value at the end of that column in the list, the formats do not copy down.

Formatting does not copy down if the new cell already has custom formatting applied, which can include shading or cell formatting such as currency or special date formatting.

Excel does not update formatting in cells that have already been formatted with the Auto Extend feature when you change the cells above.

Excel never copies down Data Validation settings.

Hyperlink formatting is a special case. If, during the scan process, Excel detects that a subset of the cells being scanned contain hyperlinks, Excel ignores those cells. If all cells in the set of cells being scanned contain hyperlinks, the feature does not activate at all.

Auto Extending and Borders

If all the cells in the scan region have consistent vertical borders, Excel extends the same vertical border formatting. All cells in the list, including the cells that contain the headings, must have borders applied, before Excel can extend them.

Excel can extend both the right vertical and left vertical borders; however, it does not extend the bottom and top borders. This behavior is by design because a horizontal border may denote the end of a list.

If there is any variation in border attributes, such as style or color, then the vertical border won't be extended. For example, if Excel scans upward and sees that cells have a right border, but one of the right borders is a different color, it will not extend the right border.

When Auto Extended Formulas May Not Work

Formulas copy down if four of the last five cells contain a similar formula and the cell above the active cell contains the formula that you want copied down. There are times when formulas may not extend. The following list contains examples of formulas that Excel will not copy down.

Formulas that reference cells in other sheets or workbooks do not copy down, even if they also reference a cell in the list containing the active cell.

Formulas in which all of the formula's references point to areas outside the list.

Formulas that reference a dimensional horizontal range in which not all row references are relative, for example:
=SUM($A$1, A2, B1, B2)
Column references have no effect on the copy down trigger.

A formula that uses a series reference, where none of the row references are relative, for example:
=SUM($A$1, $B$1)
Column references have no effect on the copy down trigger.

Formulas containing a range name or natural language formula.

Data that you paste into the new row.

Most Microsoft Excel functions also copy down, as long as they follow the basic guidelines outlined in this article.

Additional query words: XL2000

Keywords :
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto