How to Lock Filter in Excel

Microsoft Excel is a powerful tool that has been designed to help users effectively manage vast amounts of data. One of the most frequently used features in Excel is filters, which is used to sort and categorize data based on specific criteria. However, it is common for filters to be accidentally cleared, which can lead to unwanted changes in the data. Fortunately, Excel offers a feature known as “Lock Filter” that allows users to protect filters from being altered by anyone other than the person with the correct password. In this blog post, we will explore how to lock filters in Excel to ensure the security and accuracy of your data.

Quick answer: To lock a filter in Excel, first apply the filter (or sort) you want to keep, then go to Review → Protect Sheet and tick the “Use AutoFilter” box in the allow-list before clicking OK. This protects the sheet while still letting users filter, but the filter and sort must already exist on the sheet because protection prevents adding new ones.

Understand the importance of locking filter in Excel

Before we dive into the steps for locking filters in Excel, it’s important to understand why this feature is necessary. Filters are a great way to sort and organize data on your sheet, but accidental changes can lead to errors, affecting your decision-making process. To prevent this, locking filters helps ensure that the data remains untouched by unauthorized personnel.

Steps for locking filter in Excel

Step 1: Apply a filter to your data range

The first step is to apply a filter to your data range. If you already have a filter applied, then skip to step two.

1. Select the range of data to filter, then click on the “Filter” option in the “Sort & Filter” group of the “Data” tab in the Excel Ribbon.
2. Choose the column(s) you want to apply the filter to. Excel will automatically add the drop-down arrow to each column header.

If you need a refresher on setting filters up first, see our guides on how to create a filter in Excel and how to use filters in Excel. The key thing to remember is that the filter (and any sort you want to keep on your data) must already be in place before you protect the sheet, because protection blocks the creation of new filters.

Step 2: Lock the filter

1. Highlight the entire sheet or the range of cells that contain the filtered information.
2. Right-click and select “Format Cells”.
3. Click on the “Protection” tab and select the “Locked” option.
4. Click “OK”.

Step 3: Protect the sheet

Now that the filter has been locked, protect your sheet from unwanted modifications by following the next steps:

1. Click on the “Review” tab in the Excel Ribbon.
2. Select “Protect Sheet”.
3. Choose the options you want to enable while protecting the sheet from any unwanted changes. To let people keep using the existing filter on the protected sheet, tick the “Use AutoFilter” box in the allow-list. If you want them to sort within the locked area too, also tick “Sort”.
4. Enter a password if required.
5. Click “OK”.

Ticking “Use AutoFilter” is what lets users apply the filter dropdowns you already set up while everything else stays locked. Leave that box unchecked and the dropdowns will be greyed out once the sheet is protected. For more on this dialog, see how to protect an Excel sheet and how to lock an Excel sheet.

By following these simple steps, you can lock the filter in Excel, ensuring the protection and accuracy of your data. By using the protection feature, you can also ensure that no other user can make any modifications without your permission. Happy filtering!

Why protect the entire worksheet?

While locking the filter provides some level of protection, you may want to consider locking the entire worksheet or workbook instead. This is especially useful if your sheet contains sensitive or confidential data. Locking the worksheet prevents unauthorised users from making any changes to the format, data, or structure of the sheet.

To lock the worksheet, follow these steps:

1. Right-click on the worksheet tab you want to protect and select “Protect Sheet”.
2. Choose the options you want to enable while protecting the sheet from any unwanted changes. You can select options such as “Select locked cells”, “Format cells”, and “Insert rows or columns” to enable specific features while protecting the data and format from unauthorised access.
3. Enter a password if required and click “OK”.

How to remove the lock filter

Removing the lock filter is essential if you want to make changes to your data set. To remove the lock filter, you need to unprotect the sheet by following these steps:

1. Click on the “Review” tab in the Excel Ribbon.
2. Select “Unprotect Sheet”. Enter the password if required.
3. Right-click on the worksheet and select “Format Cells”.
4. Click on the “Protection” tab and uncheck the “Locked” option.
5. Click “OK”.

Locking filters in Excel ensures the integrity and security of your data. Applying a lock filter reduces the risks of accidental changes, while protecting the worksheet prevents unauthorised access. Knowing how to lock and unlock filters is essential for anyone who works with Excel frequently. Practice and apply this method to ensure the confidentiality, accuracy, and reliability of your data.

FAQs about Locking Filters in Excel

Here are some frequently asked questions that cover additional information about locking filters in Excel:

What is the difference between locking filter and protecting sheet?

Locking filters prevents the columns from being modified or the removal of filters. Protecting a sheet, however, restricts entire sheet modifications. It ensures that the sheet structure, format, and data remain protected and can only be modified by authorized persons who have the password. Consider locking the filter for column protection and protecting the sheet if your sheet includes confidential data.

What happens if I forget the password to unlock the sheet?

If you forget the password, you can’t unlock the sheet. However, you can use tools like “Excel Password Recovery,” available online, to retrieve/reset the sheet password. Alternatively, you can copy and paste the entire worksheet into a new workbook, which will remove the password protection.

Can I apply multiple filters to a single range of data?

Yes. You can apply multiple filters to a single range of data. Excel will add a new filter drop-down for every column you apply the filter to. Each filter can be customized to filtering unique criteria’s.

How do I password protect an entire workbook?

Password protecting an entire workbook can help to prevent unauthorised users from accessing, viewing, and modifying sensitive data. To password protect, the entire workbook, follow these steps:

1. Open the Excel workbook you want to protect.
2. Click on “File” and select “Info”.
3. Select “Protect Workbook” from the list of options.
4. Choose “Encrypt with Password”.
5. Enter and re-enter your password.
6. Click “OK”.

For a dedicated walkthrough, see how to password protect an Excel file.

Can I unlock filter in one column and keep it locked for others?

Yes. You can unlock the filter for one column and keep it locked for others. To do this, right-click on the filter and select “Filter Setting.” Next, click on the “Protect filter drop-down list” option under the “Locked” column and uncheck the box for the columns that you want to unlock the filter. Click OK to save. This will unlock the filter for specific columns while others remain locked.

Frequently Asked Questions

How do I let users filter on a protected sheet?

When you protect the sheet (Review → Protect Sheet), tick the “Use AutoFilter” box in the list of actions you want to allow, then click OK. This keeps the sheet locked but lets anyone use the existing filter dropdowns. The filter must already be applied before you protect the sheet, because Excel will not let users turn AutoFilter on or off once protection is active.

Why are my filter dropdowns greyed out after protecting the sheet?

The most common reason is that “Use AutoFilter” was not ticked in the Protect Sheet dialog. Unprotect the sheet (Review → Unprotect Sheet), confirm a filter is applied to your range, then protect it again and check the “Use AutoFilter” box. The dropdowns will then work normally on the locked sheet.

Can users sort data on a protected sheet too?

Yes, but only if you tick the “Sort” option in the Protect Sheet dialog, and the cells being sorted are unlocked. Like AutoFilter, sorting is disabled by default on a protected sheet. See how to sort data in Excel for the sorting basics.

Does protecting the sheet stop people from changing the filter criteria?

No. Allowing “Use AutoFilter” lets users change which values are shown through the dropdowns; it simply stops them from removing the filter or restructuring the sheet. If you need the displayed data to stay fixed, do not allow AutoFilter and instead rely on locked cells and full sheet protection.

How do I unlock the filter again?

Go to the Review tab and click Unprotect Sheet, entering the password if one was set. Once the sheet is unprotected, the filter behaves normally and you can add, remove, or change filters freely, or remove the filter altogether.

What is the difference between locking a filter and hiding the filter buttons?

Locking a filter (via sheet protection) controls who can change the filter, while hiding the buttons simply removes the dropdown arrows from view. If you only want to clean up the look of your headers, see how to hide filter buttons in Excel instead of protecting the sheet.