If Excel will not sort a protected sheet, the usual fix is to unprotect the sheet, unlock the cells in the range users need to sort, then protect it again with Sort allowed. Excel generally cannot sort a protected range that contains locked cells, even when the Sort permission is checked.
Why sorting stops when a sheet is protected
Worksheet protection is designed to prevent changes to locked cells. Sorting is not just a display change: Excel moves cell contents among rows. If the range being sorted includes locked cells, Excel may block the operation to avoid changing protected content. This is why checking the Sort option alone may not solve the problem.
There are two separate controls to keep in mind. First, the worksheet must allow sorting in its protection settings. Second, the cells in the range being sorted must be available for that operation. A sheet can have Sort enabled and still refuse to sort because the data cells are locked.
Protection is also different from hiding or filtering data. A filter can hide rows without rearranging them, and sorting changes row order. A protected sheet may allow users to use an existing filter if the relevant permission is enabled, while still restricting sorting.
Check the protection settings first
In Excel for Microsoft 365 on Windows, open the Review tab and choose Unprotect Sheet if the sheet is currently protected. If Excel requests a password, the person who set the protection must provide it. There is no supported setting that lets a user bypass a forgotten protection password.
After unprotecting, protect the sheet again to inspect its allowed actions. Choose Review > Protect Sheet. In the dialog, make sure Sort is checked. Consider checking Use AutoFilter as well if people need to operate filter arrows that are already in place. These are distinct permissions: allowing filters does not automatically allow sorting.
In the same dialog, select the actions users genuinely need, such as selecting unlocked cells. Avoid enabling unrelated permissions just to make a sort work. Protection options and dialog presentation can vary somewhat in Excel for Mac and Excel for the web, but the principle is the same: permit sorting and make the relevant range eligible to move.
Unlock the cells users need to sort
By default, Excel cells are marked as locked, but that setting takes effect only after the worksheet is protected. To make a data range available for sorting, unprotect the sheet, select the range, and open Format Cells. On Windows, press Ctrl+1; on Mac, press Command+1. Open the Protection tab, clear Locked, and select OK.
Then protect the sheet again with Review > Protect Sheet, enable Sort, and enable Select unlocked cells if users need to click or select the data. Test the sort with a copy of the workbook or a small sample before distributing the protected file.
For example, imagine a sales list with columns for salesperson, region, and revenue. If users need to sort whole records by revenue, unlocking only the revenue cells is not enough: sorting must keep each salesperson and region attached to the correct revenue. The entire data range that must move together needs to be eligible for sorting. If some columns remain locked, protection can prevent sorting the full record range.
This creates a practical trade-off: cells that users need to move as part of a sort may also be editable when the sheet is protected. Excel worksheet protection does not provide a separate permission that allows a cell’s value to be rearranged but forbids all direct edits to that same unlocked cell.
Set up the data before protecting it
Prepare the range while the sheet is unprotected. Use one clear header row, keep each record on a single row, and avoid merged cells inside the data. A rectangular range with consistent columns is easier for Excel to sort correctly than a layout with blank separator rows or unrelated notes mixed into the records.
For a reliable setup, use this sequence:
- Unprotect the worksheet, using the password if prompted.
- Identify the complete block of records, including every column that must stay together when rows move.
- Unlock the cells in that block through Format Cells > Protection.
- Set up any filter arrows or table structure before protecting the sheet.
- Choose Review > Protect Sheet, allow Sort, and select any other permissions users need.
- Protect the sheet and test a sort on a representative column.
When sorting a selected range, check that Excel identifies the header correctly. If prompted, choose to expand the selection so entire rows move together. Sorting just one column can separate values from the records they describe. The safest approach is to sort the full data block or use a properly structured Excel table.
Why Sort may still be unavailable
If the Sort command is disabled, or Excel displays a message that the operation is not allowed on a protected sheet, check these common causes before changing the workbook design:
- The range contains locked cells. This is the most common protection-related cause. Unlock the complete range that must be reordered, not just the column used as the sort key.
- Sort was not permitted when protection was applied. Unprotect the sheet and protect it again with Sort selected. Changing which actions are allowed requires changing the protection settings.
- The selection does not include the full records. Confirm that every related column is included, and choose to expand the selection if Excel offers that choice.
- The data has merged cells or inconsistent layout. Unmerge cells in the data area and remove blank separator rows that split one list into multiple blocks.
- The workbook is in a restricted state. Check whether the file is read-only, opened with limited editing rights, or protected at the workbook-structure level. Sheet protection and workbook protection are separate controls.
- You are trying to create or change a filter while protected. Usually, add the filter before protection and allow Use AutoFilter so users can operate its existing controls.
Sorting an Excel table does not automatically avoid the worksheet-protection limitation. Tables make data easier to organize, but the cells that need to move still have to be usable under the sheet’s protection rules. If a particular table has a mixture of locked and unlocked cells, test its behavior in a copy rather than assuming table formatting overrides protection.
Protect formulas without blocking the workflow
A common layout has editable input cells and locked formula cells. For instance, a worksheet might contain unlocked entries for quantity and price, with a locked formula column calculating totals. If users need to sort complete records, the locked formula cells in the sort range may prevent the operation. Simply enabling Sort does not make those formula cells sortable on a protected sheet.
One option is to let users sort only a range in which all cells that need to move are unlocked, while keeping formulas outside that range. Another is to provide a separate, unprotected working copy or a dedicated report sheet for sorting. If formulas must remain locked and users must reorder entire records, the workbook may need a different design or an approved automation workflow. A macro is not a universal fix: it needs appropriate permissions and must be designed and reviewed for the workbook.
Do not unlock formula cells casually. Once the sheet is protected, unlocking a cell permits users to edit it as well as making it available for operations that move the data. If protecting formulas is important, weigh that requirement against the need for interactive sorting and choose a layout that does not put both requirements in conflict.
Protection is not a security boundary
Excel sheet protection is useful for preventing accidental edits and limiting ordinary worksheet actions. It should not be treated as strong security for confidential data. A password-protected sheet does not replace proper file access controls, encryption, or storing sensitive information somewhere users are not authorized to access.
Use sheet protection to guide normal editing: unlock intended input cells, lock formulas or headings, and allow only the operations needed for the task. Share the workbook through an access-controlled location and give users the appropriate file permissions. If someone forgets the sheet password, contact the workbook owner or administrator rather than relying on unofficial bypass methods.
Excel for Mac and Excel for the web
The Windows instructions above apply to Microsoft 365 desktop Excel. In Excel for Mac, the relevant controls are also on the Review tab, although ribbon layout can differ by build. The shortcut to open Format Cells is Command+1 rather than Windows Ctrl+1. The locked-cell and Sort-permission requirements remain the important checks.
Excel for the web has sheet-protection and sorting features, but its controls and available options can differ from desktop Excel and may change as the service is updated. If you cannot find the permission you need in the web interface, open the workbook in the desktop app, adjust protection there, and then save it. When a workbook is stored online or shared with others, make sure you have editing permission; read-only access is a separate reason a sort may not work.
Frequently Asked Questions
Why does Excel say I cannot sort a protected sheet?
Most often, the Sort permission is off, or the range to be sorted contains locked cells. Unprotect the sheet, unlock the complete range that must move together, then protect it again with Sort enabled.
Does checking Sort in Protect Sheet always fix it?
No. It grants permission to use sorting, but it does not necessarily make locked cells sortable. Both the permission and an eligible data range are needed.
Can I sort protected cells without unlocking them?
Excel generally blocks sorting a protected range that contains locked cells. There is no separate sheet-protection checkbox that overrides this limitation while keeping those cells locked.
Can users filter a protected sheet but not sort it?
Yes. Filtering and sorting are separate actions. An existing filter can be usable when Use AutoFilter is allowed, while sorting remains unavailable if Sort is not permitted or the range includes locked cells.
Will converting the data to a table fix sorting on a protected sheet?
Not by itself. A table can improve headers and filtering, but worksheet protection still affects whether Excel can reorder the cells in the table. Check the protection settings and whether the cells to be sorted are locked.
What if I do not know the sheet password?
Ask the workbook owner or your organization’s administrator to unprotect the sheet or provide an authorized editable copy. Excel does not provide a supported way to recover or bypass a forgotten sheet-protection password.
Related
- Excel Power Query: How to Group Data
- Vertex42 Debt Snowball Spreadsheet: A Practical Guide
- Vertex42 Budget Spreadsheet: How to Get and Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Scatter Plot Not Plotting X Values? Here’s How to FixIf an Excel scatter plot is not plotting your x values, first confirm that the chart type is…
- Excel Invalid Name Error: Causes and FixesIn Excel, an invalid name error usually means a formula contains a name Excel cannot recognize, such as…
- Excel Not Copying and Pasting All Data? Fix ItIf Excel is not copying and pasting all your data, first check whether a filter, hidden rows, or…
- Excel Cell List Drop-Down: Create and Fix OneTo create an Excel cell list drop-down in Microsoft 365 for Windows, select the cell, choose Data >…
- Excel Repair Now Error: Causes and Safe Fixes“Excel repair now error” is not one specific Microsoft Excel error code; it usually describes a prompt or…
- Excel COUNTIFS Not Working? Fix Common Formula ErrorsWhen Excel COUNTIFS is not working, first check that every criteria range has the same shape, then confirm…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.