ExcelRibbon.Tips.Net ExcelTips (Ribbon Interface)

Sorting Data on Protected Worksheets

Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Sorting Data on Protected Worksheets.

When you protect a worksheet, Excel stops users from performing a wide variety of tasks on the data in the worksheet. One of the things that the user can no longer do is to sort data. What if you want the user to be able to sort data, but still have the sheet protected?

The answer is quite easy: Excel allows you to specify what users can and cannot do with a protected worksheet. When you display the Review tab of the ribbon and click Protect Sheet in the Changes group, Excel displays the Protect Sheet dialog box. At the bottom of the dialog box is a long list of check boxes. All you need to do is select what the user should be able to do with the worksheet. One of the options (you need to scroll down a bit) is Sort. If you select this option, then users can sort protected data.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (137) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Sorting Data on Protected Worksheets.

Related Tips:

A Picture is Worth Thousands! Your worksheets are not limited to holding numbers and text. You can also add graphics or easily create charts based on your data. Excel Graphics and Charts, available in two versions, helps you make your graphics and charts their absolute best. Check out Excel Graphics and Charts today!


Comments for this tip:

Aviva    23 Oct 2013, 10:51
Here is an article that explains the solution Jules mentioned in more detail: http://blog.softartisans.com/2013/10/01/kb-sorting-locked-cells-in-protected-worksheets/
awyatt    20 Sep 2013, 12:43

The process you describe is what you must do if you are using an older version of Excel. That is the process described if you click the link that leads to the version of this tip for older versions of Excel. (The link is available at either the beginning or end of the above tip.)

Willie    20 Sep 2013, 12:36
Thhe tip Jules mentioned doesn't work in Excel 2003.

I have a sheet with prior year data. I'd like to allow access to the sort dialog box but prevent changing ANY data!
The only solution I've found is to create Avery complex macro that presents an emulation of the sort dialog, unlocks the sheet, sorts, and then relocks the sheet! There sure ought to be a better way!
Jules    25 Aug 2013, 10:26
Been struggling with this in at least two versions of Excel, then today came across a solution that was posted four years ago! It avoids macros, key combinations, etc. It works for me (Excel 2010). See if it's what you're looking for:

John    17 Jul 2013, 10:31
Then, how can I sort a table with one column locked. Macro? or any event handler?

Note:It is the key column and the values are generated by the database. I don't user to mess these values or I won't be able to find matching record in DB when I import the data to database.
awyatt    13 Jun 2013, 09:11
It is not "entirely incorrect," and I did "research before posting."

You CAN sort data in protected worksheets, but ALL the cells that will be involved (or potentially involved) in the sorting operation must be unlocked. This includes any column headings for the data that may be sorted.

BEFORE locking the worksheet, select all the cells that you want people to be able to sort. (Good idea: select one of the cells then press Shift+Ctrl+8. The selected region is what Excel thinks should be sorted when a sort is done.) Display the Protection tab of the Format Cells dialog box and clear the Locked check box. Now protect your worksheet and make sure you allow for sorting in the protection specifications.

That's it. Works on all systems on which I tested it.

Pogla    12 Jun 2013, 21:20
This is entirely incorrect.

Sort on locked cells in not allowed in Excel... any version I can find, but DEFINITELY for 2007/10.

Please research before posting such articles
Jayesh    18 Dec 2012, 00:44
You need to enable the auto-filter first, before protecting the sheet and granting access to users via "allow users to ..." option. the auto-filter drop-downs then would enable the users to sort the data - ascending or descending (works in excel 2007)
Drew    20 Aug 2012, 21:48
Tech tip above is incorrect. Excel 2010 and earlier does NOT allow you to sort a protected worksheet, no matter which options are selected to "allow all users of this worksheet to". This is a know bug and is a rampant problem with users, documented widely across the internet.
Eric    09 May 2012, 16:23
Same here, in fact Msft Techweb says you have to unprotect to allow sort feature. It appears the selection allows the sort ribbon icons to appear ..... but not work.

Msft article for earlier versions:


Has anyone actually seen this work?

Thank you
Kevin    12 Jan 2012, 09:29
I am using Excel 2007 and followed the method described above, but was unable to sort a protected worksheet even though I had checked the "Sort" option. Am I missing a step?

Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 3+4? (To prevent automated submissions and spam.)
          Commenting Terms

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.