XL5: Filter Takes Long Time With Border Applied to List

• Microsoft Excel for Windows, versions 5.0, 5.0c

SYMPTOMS

In Microsoft Excel, when you filter a large list of data using either the AutoFilter or the Advanced Filter, it may take a longer time than expected (or a longer time than you previously noticed) to filter the list.

CAUSE

This behavior occurs if your worksheet that contains the list also contains a cell formatted with a solid border. If a cell on the worksheet that contains the list is formatted with a solid border (not dotted), when you filter the list, it may take up to 100 times longer to filter the list than if the worksheet does not contain any solid border cell formatting.

Additionally, even after you remove the border formatting from the cell(s) on the worksheet, Microsoft Excel continues to take a long time to filter the list--it takes longer to filter this list than it takes to filter a list on a worksheet that has never contained solid borders.

RESOLUTION

To work around this problem, copy the list that you want to be filtered to a new worksheet. If the list itself contains a cell with border cell formatting, then do the following:

1. To display the entire list, choose Filter from the Data menu, and then choose Show All.

2. Select the entire list, and choose Copy from the Edit menu.

3. From the File menu, choose New to create a new workbook.

4. Select the cell that you want to contain your list, and choose Paste Special from the Edit menu. Under Paste, select either the Values or the Format option, and choose OK.

If the list does not contain border cell formatting (that is, the border cell formatting is elsewhere on the worksheet), you can copy the list to a new worksheet, and paste the list using the Paste command (Edit menu).

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel version 7.0 for Microsoft Windows 95. Microsoft Excel version 7.0 was optimized to correct this problem. Note that it still takes longer to filter a list on a worksheet that contains border cell formatting than it does on a worksheet that does not contain this formatting. However, this process takes much less time than it took in version 5.0.