Excel Protected Sheet Sort: Settings, Limits, and Fixes

Formulas & functions · Updated November 21, 2026

To sort a protected Excel worksheet, choose Review > Protect Sheet, allow users to sort, and protect the sheet. There is an important limitation: Excel generally cannot sort a protected range that contains locked cells, so enabling the Sort option alone may not be enough.

What sheet protection changes

Worksheet protection controls which actions people can take; it does not make a sheet read-only in every respect or encrypt the workbook. When you protect a sheet, Excel presents a list of actions that you can permit, such as selecting cells, formatting cells, inserting rows, sorting, and using AutoFilter. Actions not permitted are blocked while protection is on.

Sorting rearranges records, not just the values in one column. If a table has names, dates, and amounts in adjacent columns, sorting by date should move each whole record together. Excel therefore has to rearrange the cells in the sort range. If those cells are locked, sheet protection can prevent the operation, even when Sort is checked. This explains why the setting can appear correct but a user still sees an error.

By default, cells on a worksheet are marked as locked, but that setting does not take effect until the sheet is protected. You can change the Locked setting for selected cells in Format Cells > Protection. A common design is to leave formula cells locked while unlocking cells intended for data entry. Sorting a range that includes the locked formula cells can then conflict with protection.

Allow sorting when you protect the sheet

In Excel for Microsoft 365 on Windows, configure the permission as follows:

  1. If users will sort a range that must be editable, select the intended range and press Ctrl+1 to open Format Cells. On the Protection tab, clear Locked, then select OK. This changes the cell setting; it does not itself protect or unprotect the sheet.
  2. Set up any table, sort range, or filter controls users will need before protection. If you plan to use AutoFilter, add it now through Data > Filter.
  3. Go to Review > Protect Sheet. Enter a password if you need one, and keep it somewhere safe; Excel may require it to remove protection later.
  4. In the permissions list, select Sort. If users need to change filter criteria, also select Use AutoFilter. Choose any other permitted actions deliberately, then confirm the password if prompted.
  5. Test the worksheet as a user would: try sorting the full intended range, not just a single column, and check that each record stays together.

For an existing protected sheet, choose Review > Unprotect Sheet first. Enter its password if requested, adjust the cell settings or protection permissions, then protect it again. Selecting Sort after the sheet is already protected is not a separate shortcut around the locked-cell restriction.

On Excel for Mac, the Review tab also contains Protect Sheet and Unprotect Sheet; the protection dialog lets you choose permitted actions. The exact dialog layout can differ slightly from Windows. Excel for the web has its own protection controls and a more limited interface for some sheet-management tasks. If you cannot find a needed permission or need to change cell locking in detail, open the workbook in the desktop Excel app.

Why the Sort permission may not be enough

The Sort checkbox grants permission to attempt sorting; it does not unlock cells or override every restriction. In particular, a protected worksheet cannot reliably sort a range whose cells are locked. Consider a sheet with a table in A1:D50: column A contains IDs, B contains names, C contains dates, and D contains formulas. If the data cells are locked, allowing Sort does not necessarily let a user sort all 50 records by date, because the sort would move locked cells.

Also check that the selected sort range includes complete records. If someone selects only the dates in column C and sorts them independently, names and IDs may no longer match their original dates. Excel often warns when a selection appears to be part of a larger data range, but the safest method is to click inside the dataset and use Data > Sort, confirming that the full range and header setting are correct.

Other constraints can matter too. Merged cells within a sort range, inconsistent data layouts, or a sort selection that excludes some records can prevent a correct sort or produce an unexpected result. These are separate from the protection permission, so unprotecting the sheet is not always the only troubleshooting step.

Choose what to unlock

Unlocking cells makes them editable while the sheet is protected. That may be appropriate for a shared data-entry sheet, but it means users who have permission to edit those cells can change their contents as well as sort them. Protection is not a way to let people rearrange locked records while guaranteeing the records cannot be altered.

Before changing protection, decide what users need to do and what must remain fixed:

  • Users can edit and sort the records: Unlock the entire data range they need to manage, then protect the sheet with Sort allowed. Keep headings or formulas outside that editable range where practical.
  • Users can enter information but not change formulas: Unlock only the input cells and keep formula cells locked. If a sort would move both input and formula cells, this layout may block sorting; consider separating the editable data from calculated results.
  • Users should view but not edit source records: Keep the source protected and provide a separate sorted view, such as a report sheet or a separate workbook. This avoids granting edit access just to support ordering.

Test the whole workflow after protection is applied. A setup that permits selecting unlocked cells but not locked cells can also affect what users can select, so review those permissions alongside Sort.

Sorting and AutoFilter are different permissions

Sorting changes the order of rows. AutoFilter hides rows that do not meet selected criteria; it does not delete those records. On a protected sheet, the Sort and Use AutoFilter permissions are separate. If users need to sort and filter, enable both. Set up the filter before protecting the sheet: protection generally lets users use existing filter controls, but does not let them add or remove AutoFilter while the sheet remains protected.

For example, a protected task list might let a user filter the Status column to show Open items and then sort those visible items by Due Date. That requires the filter controls to exist already and the appropriate permissions to be enabled. It does not mean users can change locked values in the list. Test the combination with your actual range, because protection and the locked state of cells still affect what Excel permits.

Alternatives when locked data must stay protected

If users need different views of records but must not edit the underlying data, changing the protection settings may not be the right solution. A separate report sheet can display the records in a chosen order while the source sheet remains protected. In Microsoft 365, the SORT function can return a sorted array on another sheet. For example, if the source records are in A2:D50 and the date to sort by is in column C, a formula can be placed in an empty area such as F2: =SORT(A2:D50,3,1). The result spills into adjacent cells in ascending order by the third column of the source range. The source range should include only the records, not the header row.

SORT is available in Microsoft 365 and Excel 2021 or later. It creates a sorted result rather than moving the original rows, so it suits a view or report. It does not itself add security: someone with access to the workbook may still be able to inspect other sheets or copy data. Protect or hide sheets only as part of a broader workbook design, not as a guarantee of confidentiality.

Another option is to keep data entry on one sheet and create a separate, unprotected analysis sheet. A macro can temporarily unprotect a sheet, sort a range, and protect it again, but this adds maintenance and security considerations. Macros cannot be undone with Ctrl+Z, and users should save a copy before running one. For many shared workbooks, a separate view is simpler and less error-prone than a macro.

Common problems and practical checks

  • Sort is unavailable or gives a protection warning: Confirm that Sort was selected in Review > Protect Sheet. Then check whether the intended sort range contains locked cells. If it does, decide whether those cells can safely be unlocked or whether users should sort a separate view.
  • Only one column moves: Select the complete dataset or click inside a consistent table before opening Data > Sort. Make sure the range includes all columns that belong to each record and that My data has headers is set correctly when applicable.
  • Filter arrows are missing or cannot be used: Create the filter before protecting the sheet and enable Use AutoFilter. Sort permission by itself does not grant filter permission.
  • The sheet cannot be unprotected: If a password was set, Excel asks for that password. There is no supported setting that lets a user change the protection permissions without unprotecting the sheet first. Keep passwords available to the people responsible for maintaining the workbook.
  • Sorting works for one user but not another: Check that they are opening the same workbook and worksheet, and that the sheet has not been protected again with different options. Excel desktop and web interfaces can expose different controls.

Sheet protection is intended mainly to prevent accidental edits, not to provide strong data security. A password on a protected worksheet is not a substitute for file encryption, access controls, or careful sharing permissions. Avoid granting broad edit permissions simply to make sorting convenient if the contents must remain unchanged.

Frequently Asked Questions

Can I sort a protected Excel sheet without a password?
Yes, if the sheet is not password-protected or if its owner permits sorting and the sort range meets the cell-locking restrictions. If protection requires a password to change settings, you need the password to alter those settings.

Why does Excel say I cannot sort a protected sheet?
The Sort permission may not be enabled, or the range may contain locked cells. Check Review > Protect Sheet settings and the Locked status of the cells in the intended sort range. Also confirm that you are sorting complete records.

Can I allow sorting but prevent users from editing cells?
Not generally for a range of locked cells that Excel must rearrange. Sorting changes row positions, and protection can block that change. Use a separate sorted view, or redesign the sheet so the sort range is editable if that is acceptable.

Does allowing AutoFilter also allow sorting?
No. Use AutoFilter and Sort are distinct protection permissions. Enable both if users need both actions, and create the filter before protecting the worksheet.

Can users sort a protected Excel table?
They may be able to if Sort is allowed and the table range satisfies the protection restrictions, including the locked-cell limitation. Test the actual table after protection; table behavior does not remove the worksheet’s cell-protection rules.

Does protecting a sheet secure its data?
No. Worksheet protection mainly limits editing and other actions in Excel. It should not be treated as encryption or as a guarantee that someone with workbook access cannot view or copy the data.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.