Article ID: 231002 - View products that this article applies to.
This article was previously published under Q231002
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.
Selecting the Auto Extend List FeatureTo turn the Auto Extend List feature on and off, follow these steps:
How Auto Extended Formatting WorksFor 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
An Example of Auto Extending a Formula
Rules for Auto Extended FormattingWhen 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:
When Auto Extended Formatting May Not WorkBecause 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:
Auto Extending and BordersIf 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 WorkFormulas 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.