Microsoft KB Archive/230998

= XL2000: Excel Considers Cell Outside Data Area as Last Cell in the Worksheet =

PSS ID Number: 230998

Article Last Modified on 9/25/2003

-

The information in this article applies to:


 * Microsoft Excel 2000

-



This article was previously published under Q230998



SYMPTOMS
Excel may calculate the last cell in a worksheet to be in a row below any data or in a column to the right of any data. Depending on the size of the unused area, this may cause the worksheet to have a larger file size or perform slowly.



CAUSE
There are a number of things that Excel looks at to determine the used range of a worksheet. The "last cell" is usually defined as the intersection of the furthest column to the right that contains data, and the lowest row that contains data. The "used range" is the rectangle of cells with the upper-left corner in cell A1, and the lower-right corner in the "last cell."

However, some other conditions may cause Excel to consider a cell as being used, thereby increasing the size of the used range beyond those cells that contain actual data or formulas. For example, any change to a cell that makes it different from the Normal style marks the cell as used, including changes to:
 * Number formats
 * Text alignment
 * Fonts
 * Font formats such as color, font style (bold), size
 * Borders
 * Patterns
 * Cell protection such as locked or hidden

Other changes that mark a cell as used:
 * Changed row heights (but not column widths)



RESOLUTION
It is often useful to make a worksheet smaller, in order to save file space, increase calculation speed, and reduce the amount of time Excel needs to open the file. However, you can only clear or remove rows from the bottom of, or columns from the right of the area that Excel considers to be used. The used range of an Excel worksheet always begins at cell A1, even if the first row or column is totally empty of data and contains no format changes.

To change the used range in Excel, follow these steps:
 * 1) Clear or delete the cells that define the range boundaries, or change the formatting back to that of the default Normal style. To do this, use any of the following methods:
 * 2) * Select the area on the right or bottom that you want to remove from the used range, and press DELETE or point to Clear on the Edit menu, and click All.


 * 1) * If you want to remove entire rows or columns, click the row numbers or column letters, and then click Delete on the Edit menu.


 * 1) * If you need to change row height back to the default, select the appropriate rows, point to Row on the Format menu, and click AutoFit.


 * 1) Save the workbook.

After you clear an area, Excel does not recalculate a smaller used range, until you save the workbook.



MORE INFORMATION
To find the "last cell" or end of the used range of a worksheet, use either of the following methods:


 * Press CTRL+END.

-or-


 * On the Edit menu, click Go To. Then, click Special. In the Go To Special dialog box, click Last cell, and then click OK.

To modify the Normal style, follow these steps:


 * 1) On the Format menu, click Style.
 * 2) In the Style dialog box, click Normal in the Style name list, and click Modify.
 * 3) Click each of the tabs and make changes as appropriate. Then, click OK.

