Excel usually blocks sorting on a protected sheet because sorting is not allowed in the sheet’s protection settings, or because the cells in the records you are trying to sort are locked. Unprotect the sheet, unlock the cells that need to move, then protect it again with Sort enabled; if you use filter arrows, enable Use AutoFilter too.
Why a protected sheet blocks sorting
Worksheet protection is designed to prevent changes to cells and sheet structure; it is not the same as encrypting a workbook or controlling who can open it. In Excel for Microsoft 365 on Windows, a protected sheet can allow selected actions, but those permissions do not automatically make every operation available. Sorting is one of the actions that must be permitted explicitly.
There is a second restriction that often surprises people: allowing Sort in the protection settings does not let users rearrange locked cells. Excel requires the cells in the range being sorted to be unlocked. This matters because, by default, cells are marked as locked, although that setting has no effect until the sheet is protected. A sheet can therefore have Sort checked and still refuse the sort because the data range remains locked.
Protection settings also apply to a worksheet, not just to a particular table or range. If users need to sort one dataset but should not edit other cells, plan which cells are unlocked before protecting the sheet. That gives the intended users the necessary freedom without making every cell editable.
Check which restriction is causing the problem
Start by identifying what you are trying to sort and how you are starting the sort. A blocked command, a disabled Sort button, and a warning after you choose a sort field can point to different causes. Check these common possibilities:
- Sort is not permitted: The sheet is protected, but the Sort option was not selected when protection was applied.
- The sort range includes locked cells: Even with Sort allowed, Excel can reject a sort if the cells in the range to be rearranged are locked.
- You are using filter arrows: The sheet may need the separate Use AutoFilter permission, and the filter must already exist before protection is turned on.
- The selected range is unsuitable: A partial selection, merged cells, or an incomplete set of columns can stop Excel from sorting the records as intended.
- You do not have the protection password: You may need the workbook owner or administrator to change the settings.
To check whether protection is active, go to Review and look for Unprotect Sheet. If the command is available, the current sheet is protected. Selecting it may prompt for a password. Do not confuse sheet protection with Protect Workbook, which controls workbook structure, such as whether sheets can be added or deleted.
Allow sorting when you protect the sheet
In desktop Excel for Microsoft 365 on Windows, the reliable way to change the permissions is to unprotect the sheet, prepare the cells, and apply protection again. You need the existing password if one was set. If you do not know it, ask the person who manages the workbook rather than trying to change protected content without authorization.
- Go to Review > Unprotect Sheet. Enter the password if Excel asks for it.
- Select the cells users should be able to change or rearrange. To select a data block, include the columns that make up each record, not just the column that will determine the sort order.
- Open Home > Format > Format Cells, choose the Protection tab, clear Locked, and select OK. You can also use Ctrl+1 on Windows to open Format Cells.
- Go to Review > Protect Sheet. In the permissions list, select Sort. If users will sort or filter from existing filter arrows, select Use AutoFilter as well.
- Choose any other permissions the users need, optionally enter a password, and confirm the protection settings. Test a sort using a copy of the workbook or a sample range before distributing the file.
On a Mac, the corresponding protection controls are on the Review tab, although ribbon layout and dialog wording can vary by Excel version. The Format Cells shortcut is Command+1 rather than Ctrl+1. If a command is not visible on the ribbon, use Excel’s command search or check the Review tab’s protection controls.
Unlock only cells that genuinely need to be editable or sortable. Once unlocked, a cell can be changed by anyone who can use the protected sheet unless additional controls apply. A common setup is to unlock the input area and keep instructions, formulas, and headings locked. But if a sort must move whole records that include formula cells, the locked-cell restriction may mean the whole sortable record cannot be rearranged while protection is active. That is a design trade-off, not a missing checkbox.
Make sure the whole record can move together
Sorting only one column in a table of related information can separate values from their records. For example, if a customer’s name is in column A, their region in B, and their balance in C, sorting only column A would leave the other values in place and associate them with the wrong customer. In the Sort dialog, sort the full data range and choose the column that determines the order.
For a protected sheet, the range to be sorted must also meet the unlocked-cell requirement. If column C is locked while columns A and B are unlocked, selecting all three columns may still fail. Unlocking just the sort-key column is not necessarily enough: Excel needs to be able to rearrange the cells in the complete selected range. Make sure the header is identified as a header in the sort options so it is not treated as an ordinary data record.
Before changing protection, check for merged cells in the intended sort range. Merged cells can make a rectangular data block unsuitable for sorting, and they often create trouble even on an unprotected sheet. A clean table with one header row and consistent rows is easier to sort and less likely to produce a misleading result.
Sorting is different from filtering
Sorting changes the order of records, such as putting dates from oldest to newest. Filtering hides records that do not match a condition, such as showing only one region. Excel treats these as separate permissions in the Protect Sheet dialog: Sort and Use AutoFilter.
If the sheet already has filter arrows, selecting Use AutoFilter can let users change filter criteria while the sheet is protected. It does not grant permission to create or remove an AutoFilter, and it does not replace the Sort permission. Set up the filter before protecting the sheet, then test both filtering and sorting. If users only need to narrow the displayed rows and should not change their order, they may need filtering permission but not sorting permission.
Excel tables also provide header dropdowns for sorting and filtering, but a table does not bypass worksheet protection. The applicable protection settings and unlocked-cell constraints still matter. A table can help keep a dataset organized, yet it cannot make a protected sort succeed when the required cells are locked.
Common reasons the fix still does not work
- The sheet was not reprotected with the new option. Protection must be applied after selecting Sort. If you changed the cell locking but did not reapply protection, the sheet is not protected with the updated setup.
- Only part of the records was unlocked. Review the full range Excel is sorting, including every field that must move with each row. Also check whether the sort selection unexpectedly includes extra locked cells.
- You selected a single column from a multi-column dataset. Choose the entire record range and sort by the desired field so data stays together.
- The data contains merged cells or inconsistent boundaries. Unmerge cells in the sort area and make sure each record has the same set of columns. Avoid blank rows or stray values that cause Excel to detect the wrong range.
- You are using a filter arrow but only enabled Sort. If the action is through an existing filter dropdown, enable Use AutoFilter too. Conversely, enabling Use AutoFilter alone does not allow a protected-sheet sort.
- The wrong sheet is protected. Protection is set per worksheet. Confirm you changed the settings on the same tab where the data is located.
If the Sort command is unavailable even after you change the settings, try selecting one cell inside the data and opening the Sort dialog from the Data tab rather than selecting a fragment first. If Excel displays a specific warning, note its wording; it may reveal whether the problem is protection, a range selection, or the shape of the data.
Alternatives when the data must stay locked
If formulas or other cells in the sortable records must remain locked, there may be no protection setting that allows users to rearrange those locked records directly. One option is to sort the data before protecting the sheet, then protect the finished view. Another is to keep the source data locked on one sheet and provide a separate, deliberately editable output area for user-managed copies. Those choices preserve the source but require clear instructions so people know which range is authoritative.
For a workbook that needs a live, formula-based sorted view, Microsoft 365 and Excel 2021 or later include the SORT function. A formula-driven result can display data in a requested order without manually rearranging the source rows, but it is not a substitute for sorting the original records: users generally work with the resulting array as an output, not as an editable rearrangement of the source. If you use this approach, verify compatibility with everyone who will open the workbook and decide how the output sheet should be protected.
Another practical alternative is to ask the workbook owner to perform the sort or revise the protection design. If protection is password-controlled and you are not authorized to change it, request the password or an approved editable copy. Sheet protection is meant to control editing, and losing or not knowing a password is not a reason to assume the workbook can safely be modified.
Excel for the web and shared workbooks
Excel for the web has a different interface from desktop Excel, and available protection controls can depend on the workbook and current web version. Look under the Review tab for the sheet-protection or Manage Protection controls. If you cannot set the required permissions there, open the workbook in desktop Excel, make the approved change, save it, and test the result. Do not assume that a web sort working on an unprotected sheet proves that a protected desktop sheet has the same permissions.
When several people use the same workbook, explain which cells can be edited and whether users should sort or filter. Sorting changes the displayed order for everyone viewing that shared worksheet, so users can appear to see records move while they work. For collaborative data entry, consistent table headers and a clear rule about who may reorder records can prevent confusion. Keep an unchanged backup before making significant changes to protection or layout.
Frequently Asked Questions
Why is Sort greyed out in Excel when the sheet is protected?
Sort may not be allowed in the protection settings, or the range Excel would rearrange may contain locked cells. Unprotect the sheet, review the cell locking and Sort permission, then protect it again.
Does allowing AutoFilter also allow sorting?
No. Use AutoFilter and Sort are separate permissions. Enable both if users need to sort and use existing filter dropdowns on the protected sheet.
Do I need to unlock only the column I want to sort by?
Usually not. Excel must be able to rearrange the cells in the full range being sorted. If a record spans several columns, consider the locking of all those cells and sort the full record range together.
Can I sort a protected Excel table?
A table does not override sheet protection. The relevant Sort permission must be enabled, and the cells in the sort range must meet Excel’s unlocked-cell requirement. Existing filter dropdowns may also require Use AutoFilter.
Can I sort without knowing the sheet password?
You cannot change the protection settings through the normal Excel controls without unprotecting the sheet. Ask the workbook owner or administrator to make the change or provide an authorized copy.
Prevent the problem in future workbooks
Decide in advance whether users need to edit cells, filter records, sort records, or only view the sheet. Unlock the intended input and sortable ranges before protecting the sheet, then select only the permissions that match those tasks. Test the exact workflow—such as sorting the full data range by date and then filtering by region—on a copy before sharing the workbook. This catches the most common surprise: a permission can be enabled while locked cells still prevent the operation.
Related
- Excel Power Query: Combine Queries Explained
- Snowball Debt Payoff Spreadsheet: Free Excel Setup
- Excel Power Query: Change a Data Source Step by Step
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- BMI Calculator in Excel: Formula and SetupTo make a BMI calculator in Excel, enter weight in kilograms in B2 and height in metres in…
- Excel Filter Contains Partial Text? Find MatchesYes. In Excel for Microsoft 365 on Windows, open the column’s filter menu and choose Text Filters >…
- Excel Formula or Operator? Understand the DifferenceIn Excel, a formula is an expression that calculates a result, while an operator is a symbol that…
- Excel Formula List: Basic Functions, Uses, and ExamplesA basic Excel formula list includes SUM for totals, AVERAGE for means, COUNT and COUNTA for counting, MIN…
- How to Filter Rows by Color in Excel: A Practical GuideTo filter rows by color in Excel for Microsoft 365 on Windows, select a cell in your data,…
- How to Combine Names in Excel with Formulas and Flash FillTo combine first, middle, and last names in Excel, put them in A2:C2 in that order, then enter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.