XL2000: Advanced Filter May Function on Protected Worksheet

If you have a protected Microsoft Excel worksheet and you try to filter a list by using the Advanced Filter command, the list may be filtered in place. Also, after your list is filtered, you cannot click Show All on the Filter menu.

Because the Show All command is unavailable on a protected worksheet, you need to display all of the records in a filtered list in order to work around this problem. To do this, follow these steps:

1. On the Data menu, point to Filter, and then click Advanced Filter.
2. Clear the Criteria range box, and then click OK.

All of the records in the list are now displayed.

NOTE: Currently, there is no way to prevent the Advanced Filter command from filtering a list in place on a protected worksheet.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

When a worksheet is protected (with the default settings), you are unable to resize or hide rows and columns in the worksheet. However, when you use the Advanced Filter command to filter a list in place, certain rows in your list may be hidden, based on the criteria you specify.

For more information about using Advanced Filter, click Microsoft Excel Help on the Help menu, type Filter a list by using advanced criteria in the Office Assistant or the Answer Wizard, and then click Search to view the topic.