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.
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?
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: