Loading
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:

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

 

Comments for this tip:

Wink    22 Jul 2014, 15:53


This subroutine unprotects the active sheet, invokes the sort menu and then reapplies protection to the sheet.

This might help some users who want to use the sort menu on protected worksheet..

Sub sampleSort()
    
    ActiveSheet.Unprotect
    Application.Dialogs(xlDialogSort).Show
    If Err.Number = 1004 Then
        MsgBox "Cursor not in sort range"
    End If
    ActiveSheet.Protect

End Sub
Eve    21 Jul 2014, 15:59
Allen- my apologies - your steps work, as long as I keep range of cells selected for ALL steps performed. In my situation, I wanted to only allow Sort & Filter, but no edits of any cells or formulas. Here's a recap of Allen's steps: I highlighted all columns with data. Then de-selected "locked" via protection tab, after right-clicking to get "format cells". I used the Review tab and set a new "allow users to edit range", for all data columns in my worksheet. When going to Protect the worksheet, I de-selected the first 2 options (have blanks for select locked and select unlocked), then scroll down and select "sort" and "auto-filter". These steps work in excel 2010.
Eve    21 Jul 2014, 15:59
Allen- my apologies - your steps work, as long as I keep range of cells selected for ALL steps performed. In my situation, I wanted to only allow Sort & Filter, but no edits of any cells or formulas. Here's a recap of Allen's steps: I highlighted all columns with data. Then de-selected "locked" via protection tab, after right-clicking to get "format cells". I used the Review tab and set a new "allow users to edit range", for all data columns in my worksheet. When going to Protect the worksheet, I de-selected the first 2 options (have blanks for select locked and select unlocked), then scroll down and select "sort" and "auto-filter". These steps work in excel 2010.
Eve    21 Jul 2014, 15:27
Allen, your solution posted "13 Jun 2013, 09:11" works to allow the sort in my excel 2010. But it doesn't prevent users from being able to edit / change the data. I am looking for no editing of ANY cells or formulas, with unlimited ability to filter and/or sort.
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
Willie,

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.)

-Allen
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:
http://answers.yahoo.com/question/index?qid=20090419000032AAs5VRR

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.

-Allen
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:

http://support.microsoft.com/kb/826923

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:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*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

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

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.