Yes. In Excel for Microsoft 365 on Windows, you can allow sorting on a protected sheet by checking Sort when you protect it and unlocking the cells in the range users need to sort. If the sort range includes locked cells, Excel may block the operation even when the Sort permission is enabled.
What sheet protection does to sorting
Worksheet protection controls which actions people can take on a sheet; it does not make the sheet read-only in every respect. When you protect a sheet, Excel offers permissions such as selecting cells, formatting cells, inserting rows, and sorting. The permissions you choose determine which actions remain available.
Sorting is a special case because it rearranges records across a range. Excel must be able to move the cells involved, and a protected range may contain cells that users are not allowed to change. As a result, checking Sort alone is not always enough. The cells in the sort range generally need to be unlocked before the sheet is protected.
This is different from protecting the workbook structure, which can restrict actions such as adding, deleting, or moving worksheets. It is also different from applying a filter: a filter narrows which rows are shown, while sorting changes their order.
How the Sort permission works
The Sort option is part of the Protect Sheet settings. It grants permission to use Excel’s sorting commands on the protected sheet, but it does not automatically unlock cells or override other protection restrictions. Think of it as permission to attempt a sort, subject to the cells and range involved being eligible.
For example, imagine a table with columns for employee, department, and start date. If all three columns in the records are unlocked, users can usually sort the records by department or start date while the sheet remains protected. If the start-date cells are locked, a sort that would move those cells can fail. Locked cells outside the range being sorted do not necessarily prevent sorting a separate, unlocked range.
Sorting also needs to keep each record together. When you sort, include the full set of columns that make up each row, not just one field. Sorting names alone while leaving departments and dates in place would scramble the data’s meaning. Excel may offer to expand a selection, but on a protected sheet it is safer to select the complete data range before sorting.
Set up a protected sheet that allows sorting
Plan which cells people may change or move before protecting the sheet. The following steps are for Excel for Microsoft 365 on Windows:
- Select the cells in the data range that users must be able to sort. If the sort will rearrange whole records, include every column in those records.
- Press Ctrl+1 to open Format Cells. On the Protection tab, clear Locked, then select OK. Cells are marked locked by default, but that setting only takes effect after sheet protection is turned on.
- If people will use filter dropdowns, turn on a filter before protecting the sheet. Select a cell in the data and choose Data > Filter. For a formatted Excel table, its header filter buttons are usually already available.
- Choose Review > Protect Sheet. Add a password if appropriate, then select the actions users should be allowed to perform. Check Sort; if they need to use existing filter dropdowns, also check Use AutoFilter.
- Confirm the protection settings and test a sort using a copy of the workbook or a suitable test range. Check that entire records move together and that protected formulas or headings behave as intended.
On a Mac, the same basic protection choices are available from the Review tab, though the ribbon layout can vary by Excel version. The Format Cells shortcut is Command+1 rather than Ctrl+1. Excel for the web has its own ribbon layout and protection controls, so check the available sheet-protection options there if people will edit the file in a browser.
Choose what to unlock before protecting
Unlocking a range allows users to make changes to those cells as well as move them through sorting. This is the main trade-off: if a column contains formulas that must not be edited, but those formulas are part of every row being sorted, keeping those cells locked can prevent the whole range from sorting.
Common layouts include:
- Editable records: Unlock the full data area, including all columns that must travel together when records are sorted. Keep titles, instructions, and unrelated calculations locked.
- Input cells plus formulas: Unlock user-entry fields and protect formula cells. If the locked formula column must move with the records, test the sort; a protected formula column may make that sort unavailable.
- Separate calculation area: Keep formulas or summaries outside the sortable records, or put calculations on another sheet. This can let users sort an unlocked data range while keeping calculations protected.
For headers, decide whether users need to edit them. Excel’s sort tools can treat the first row as headings, but protection and the exact selection can affect what is included. A practical setup is to keep labels protected, select the records below them when sorting, and confirm that Excel recognizes the header row. If the header must be included in the selected range, test that arrangement before sharing the workbook.
Sorting a protected table or filtered list
To sort a protected list, select a cell in the data and use a header dropdown to choose a sort order, or select Data > Sort for more control. The Sort dialog is useful for sorting by multiple fields, such as department first and then start date. Make sure the dialog identifies whether the data has headers and that the selected range covers all columns in each record.
Filtering and sorting are separate permissions. Checking Use AutoFilter lets users work with an AutoFilter that was already applied before protection; it does not mean they can create or remove the filter while the sheet is protected. The Sort option controls sorting. If the dropdown appears but its sort commands are unavailable or produce a protection warning, verify both the relevant permission and the lock status of the range.
Excel tables can make lists easier to manage because they have named headers and built-in filter buttons. However, converting a range to a table does not bypass sheet protection. The protected-sheet settings and locked cells still matter. If a table contains locked cells that need to move as part of a sort, test the exact operation rather than assuming the table will sort regardless of protection.
Common sorting problems and fixes
- Sort is checked, but Excel still blocks the command: Inspect the lock status of the cells in the complete sort range. Unlock the cells that must move, then protect the sheet again with Sort enabled.
- Only part of a record moves: Select the full set of columns for the records, not one column by itself. Confirm that the first row is treated as headers when appropriate.
- Filter arrows work, but sorting does not: Filter use and sorting have separate settings. Check both Use AutoFilter and Sort, and confirm that the range is eligible to move.
- Users cannot select the data: In the Protect Sheet dialog, allow selection of unlocked cells. If users need to select locked headings or other protected cells too, allow selection of locked cells as well; that does not unlock them.
- Changes to Locked seem ineffective: The Locked property matters only while the sheet is protected. Unprotect the sheet, change the cell setting, and protect it again with the intended permissions.
- Excel warns that the selection contains protected cells: The selected range may include locked cells, even if the user intended to sort only one field. Select the full eligible data range or revise the layout so the sort range can be unlocked.
If a password was set and the person configuring the workbook does not know it, they may be unable to change the protection settings through the normal interface. Avoid relying on an undocumented password as a recovery plan; keep an authorized copy and record access details securely.
Protection is not the same as data security
Worksheet protection is useful for preventing accidental edits and guiding routine use. It should not be treated as strong security for confidential information. Microsoft describes worksheet protection as a way to prevent changes to locked cells, not as a security feature for protecting sensitive data. A password on sheet protection does not replace appropriate file access controls or encryption.
Use protection when the goal is to preserve formulas, labels, or layout while allowing limited actions such as sorting. If some people must not see the data at all, do not rely on hiding a sheet or locking cells as a confidentiality boundary. Control access to the workbook and its storage location instead.
Alternatives when locked formulas must stay fixed
If users need to sort records but the range includes formulas that must remain locked, consider changing the workbook design rather than repeatedly disabling protection. You could separate input data from calculations, place summaries on another sheet, or provide an unprotected working copy for sorting and then retain a protected master. The right option depends on whether formulas need to travel with each record and whether users need to edit the underlying data.
Another option is to use a controlled workflow in which an authorized person unprotects the sheet, makes or applies the sort, and protects it again. This is less convenient for frequent sorting and can expose formulas to accidental edits during the unprotected period, so clear instructions and a backup are sensible. If sorting is a routine user task, designing the sortable range to be unlocked is usually simpler.
Frequently Asked Questions
Can you sort a protected sheet in Excel?
Yes, if the sheet’s protection settings allow sorting and the cells in the sort range are unlocked. Check Sort in the Protect Sheet dialog, then test the complete range users need to sort.
Why can’t I sort even though Sort is checked?
The range may contain locked cells, or the selection may include protected cells beyond the field you meant to sort. Unlock the cells that need to move and select the full set of columns for each record.
Can users sort without being able to edit formulas?
Sometimes, depending on the range. If locked formula cells are included in the records that must move, protection can block the sort. Consider moving calculations outside the sortable range or testing a layout with the necessary cells unlocked.
Does Use AutoFilter let users sort?
It permits use of an existing AutoFilter, including its dropdown controls, but it is distinct from the Sort permission. Enable both options when users need both filtering and sorting, and make sure the range can be sorted.
Can users add a filter after protecting the sheet?
Usually, set up the filter before protection. The Use AutoFilter permission is for using an existing AutoFilter; it does not grant permission to add or remove one while the sheet is protected.
Does sheet protection protect confidential data?
No. It helps prevent unwanted changes to worksheet cells, but it is not a substitute for file security. Use suitable access controls when information must be restricted from particular people.
Related
- Uni Budget Spreadsheet: Build a Student Budget in Excel
- Excel Power Query Editor: What It Does and How to Use It
- Excel Power Query: How to Extract and Refresh Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Highlight Track Changes in Word for Clear ReviewsTo highlight edits in Microsoft Word, turn on Track Changes, then select Review > Display for Review >…
- Google Sheets Filter Just for Me? Use Filter ViewsYes. In Google Sheets, use a filter view to sort or narrow rows for your own view without…
- How to Share a Google Sheets Document SafelyTo share a Google Sheets document, open it in a web browser, select Share in the top-right corner,…
- Excel Quick Access Toolbar: Add Paste Values EasilyTo add Paste Values to the Quick Access Toolbar in Excel for Microsoft 365 on Windows, open the…
- Can’t Create Rules in Outlook? Causes and FixesYes, you can create rules in most Outlook versions, but the steps and available options depend on whether…
- Excel Day Tracker: Build a Daily Log for Work and HabitsAn Excel day tracker is a spreadsheet for recording what happens or what needs doing each day. The…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.