Enable AutoFilter functionality for a protected worksheet

Applies to
Microsoft Office Excel 2003
Microsoft Excel 2000 and 2002

By default, the AutoFilter functionality in Excel becomes unavailable when you protect part or all of a spreadsheet. If you use Microsoft Office Excel 2003 or Excel 2002, you can restore that functionality manually. If you use Excel 2000, you need to use a few lines of Microsoft Visual Basic® for Applications (VBA) code.

If you're new to the AutoFilter functionality

You use AutoFilter to quickly find and work with subsets of the information in a worksheet. For example, you can sort the data in a worksheet in ascending or descending order, find the top 10 items in a worksheet, or filter for a single value. You can also create custom filter criteria using the Custom AutoFilter dialog box. The following sections provide more information about using the AutoFilter command.

ShowStart AutoFilter

  • On the Data menu, point to Filter, and then click AutoFilter.

A set of down-arrow buttons appears in each of the column headers in your worksheet, like so:

List filtered for Davolio

Callout 1 Worksheet with AutoFilter disabled.
Callout 2 Worksheet with AutoFilter enabled.

For more background information about AutoFilter and the types of filters you can create, see About filtering. The next sections explain the basics of how to use AutoFilter.

ShowThe basics of using AutoFilter

  1. Click the AutoFilter button on the column that contains the data you want to filter or sort. A menu appears:

The AutoFilter pop up menu

  1. Click one of the available menu items. The item that you select affects the column and any information adjacent to the column. For example, if you select Sort Descending, Excel sorts the data on both sides of the filtered column in descending order. If you select Top 10, Excel displays the top 10 items in the filtered column and any corresponding data in adjacent cells. If you select an individual item, Excel displays just that item and any adjacent data. To remove a filter, click All.

 Note   You start the Custom AutoFilter dialog box by clicking Custom on the AutoFilter menu. You use that dialog box to filter on a variety of criteria, including unequal values ("show rows where Column B does not equal 200") and discrete subsets of text or data ("show rows where Column B contains 'Smith'"). For more information about using the Custom AutoFilter dialog box, see the next section.

For more information about using AutoFilter, see the following topics:

ShowUse the Custom AutoFilter dialog box

You start the Custom AutoFilter dialog box by clicking Custom on the AutoFilter pop-up menu. When you start the dialog box, it displays the name of the column that you want to filter. In this illustration, the filter is applied to Column B.

The Custom AutoFilter dialog box.

The dialog box provides two rows of lists. In each row, the left-hand list provides a set of filtering options, such as equals, begins with and contains. The right-hand lists display the values from the column that you want to filter.

You use the And and Or buttons to filter on more than one pair of criteria. For example, say you want to filter for a set of addresses that begin with 3400 and end with 3999. To do that, you would select begins with and "3400" from the first two lists. You would then click And, and select ends with and "3999" from the second row of lists.

You can also filter using wildcard characters such as asterisks (*). The dialog box provides a couple of examples. For example, the filter "Column B contains Smith*" will return all instances of names that contain "Smith," including "Smith," "Smithberg," "Smithson," and so on.

One of the best ways to familiarize yourself with the dialog box is to explore the options is provides and experiment with different types of filters. You can't really break anything. If you get a result that you don't like, just undo the filter. To undo the filter, click the AutoFilter button atop the filtered column, and then click All.

For more information about using the Custom AutoFilter dialog box, see Filter a range.

Restore the AutoFilter functionality in Excel 2003 and 2002

Follow these steps to enable AutoFilter functionality on protected worksheets. When you reach the second step, don't be afraid to browse through the list of functions for other tasks that you may want to re-enable.

  1. On the Tools menu in Excel 2003 or 2002, point to Protection and then click Protect Sheet. The Protect Sheet dialog box appears.
  2. From the Allow all users of this worksheet to list, select Use AutoFilter.

Use VBA code to protect a worksheet and enable the AutoFilter functionality in Excel 2000

The sample code shown here protects a worksheet (not a workbook) and enables the AutoFilter functionality for that worksheet. The code works only with Excel 2000. This particular sample runs automatically when you open the workbook that contains the protected worksheet. The code also contains a password that you use to unprotect the worksheet.

ShowUse the code sample

  1. If you haven't already, start Excel 2000, open the desired workbook, and note the name of the worksheet that you want to protect.
  2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

The Project Explorer pane in the editor displays a list of the worksheets in the open workbook, like so:

The Visual Basic for Applications Project pane

  1. In the Project Explorer, double-click ThisWorkbook.

A new, blank code module opens in the code window.

  1. Copy the following sample code and paste it into the code window:
                     Private Sub Workbook_Open()
    Sheet1.Protect password:="test", DrawingObjects:=True, _
        contents:=True, Scenarios:=True, _
    Sheet1.EnableAutoFilter = True
End Sub
  1. In the second and fifth lines of code, change Sheet1 to match the name of the worksheet that you want to protect.

For example, if you named the worksheet "Q2 Sales," change both instances of Sheet1 to Q2 Sales.

  1. Go back to the second line of code, locate the password="test" declaration, and change test to a strong password. You'll use that password when you remove protection from the worksheet.

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords.

It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

 Note   The password will not cause Excel to encrypt the contents of the worksheet.

  1. Save the module, close the Visual Basic editor, and then close and reopen your workbook. The code protects the worksheet and enables AutoFilter each time you open the workbook.

ShowRemove protection from the worksheet

  1. On the Tools menu, point to Protection and click Unprotect Sheet.

The Unprotect Sheet dialog box appears.

  1. In the Password box, enter the password that you embedded in the code sample.

Keep these facts in mind about the password:

  • If you or someone else protected a worksheet manually before you added the sample code described above, chances are that you (or the person who applied protection to the worksheet) entered a password. If so, that password supersedes the password that you embedded in the code sample. Here's the catch: Your original password only supersedes the password in the code the first time you remove protection. When you close and reopen the workbook after using the original password to remove protection, then the password embedded in the code takes over.
  • If you remove protection, and then close and reopen the workbook, the code automatically reapplies protection. In other words, the code will continue to run and protect the workbook until you remove the code from the workbook.
Applies to:
Excel 2003