Protect Excel sheet from being edited

Question

I am trying to figure out how to protect an excel sheet from being edited by others. However, i want people to be able to sort/filter and select cells in order to copy and paste. I've watched tutorials but haven't been able to get it to do all 3 things that I want.

Answer 1

When you protect a sheet (using the Review Menu and then selecting Protect Sheet on the ribbon) the following box appears....

If you scroll down you will see the option that I've ticked.

Users will then be able to select, copy and filter but not alter contents.

Does this do what you want?

Answer 2

I'm still able to edit though. And I'm trying to make sure no one can edit it.

Answer 3

Unfortunately even though the Protection has an option to permit sort, it does not work with the protected worksheet.

Following edited after initial posting with the first step added

An easy work around is as follows:

• When protecting the worksheet ensure that both "Select  locked cells" and "Select unlocked cells are checked".
• User to Select all the data on the original protected worksheet and Copy.
• Select (or add) another blank worksheet and select the first cell.
• Paste Special -> Values  (Will result in values without formulas)
• Past Special -> Formats (Will paste the formats. No need to copy again before this step)
• The User can then perform all functions because the worksheet is not protected.

The above method protects the original data but permits the user to perform all of the functions on the temporary worksheet.

Answer 4

OssieMac is correct - you will be unable to sort a protected sheet but you will be able to filter.

However - you say that you can still edit cells.

Check the protection properties of the cells.

Highlight the clles and then withing the highlighted area, right-click, then select Format Cells.

Click the Protect tab

Make sure that Locked is ticked as this will cause the selected cells to be protected when protection is activated

Answer 5

Selecting and copying cells in protected worksheets is the default behavior for protected worksheets, so presumably nothing to do there. That said, if users can copy anything out of your protected worksheet, then paste values and formats into an unprotected worksheet, they could them change anything & everything they want. Protecting a worksheet out of which anything could be copied isn't as protected as you seem to believe.

The belief my users aren't that clever is one of the most frequently burst assumptions in spreadsheet development.

Anyway, you could keep underlying data in a hidden but otherwise unprotected worksheet and use command buttons in the protected worksheet to sort the data in the other unprotected worksheet so it appears sorted in the protected worksheet. That requires using formulas like =INDEX(Other!$A$1:$AZ$20000,ROWS($A$1:A5),COLUMNS($A$5:A5)) rather than =Other!A5 in cell A5 in the protected worksheet.