Sometimes you may want to protect a worksheet to prevent others from modifying the contents of all the cells in the worksheet. And sometimes you may want to modify certain cells but prevent other cells from modification. For example, your worksheet may have certain cells that contain formulas and some cells that contain input data. You may want to modify the cells with input data but prevent modification of cells that contain formulas. In this post, we will have a look at how to protect your Excel 2010 worksheet data including how to protect only certain cells.
How to Protect Your Excel 2010 Worksheet Data
To prevent users from modifying the contents of all cells in a Worksheet:
1. On the Review tab, in the Changes group, click Protect Sheet.
2. The Protect Sheet dialog box is displayed. In the dialog box, notice that Select locked cells and Select unlocked cells is selected by default.
3. In the Password to unprotect sheet text box, enter a password and click OK.
4. In the Confirm Password dialog box, reenter the password and click OK.
5. Click inside any cell and change the contents of the cell. The Microsoft Excel message box displays a message indicating that the cell is protected and is read-only.
6. Click OK to close the message box.
Note: To modify the contents of the cell, you must remove the cell protection.
How to Prevent Users from Modifying the Contents of Certain Cells in a Worksheet
1. Select the range of cells you want to allow modification.
2. Right-click and choose Format Cells.
3. The Format Cells dialog box is displayed. In the dialog box, select the Protection tab.
4. Uncheck Locked and click OK.
5. On the Review tab, in the Changes group, click Protect Sheet.
6. In the Protect Sheet dialog box, in the Password to unprotect sheet text box, enter a password and click OK.
7. In the Confirm Password dialog box, reenter the password and click OK.
8. Click inside the cell you want to modification and make the change. If you try to modify the contents of the other cells, the Microsoft Excel message box will be displayed asking you to unprotect the worksheet.
However, this works only on a single worksheet. Excel 2010 does not provide direct options for protecting multiple worksheets. To do this, you need to set up a simple macro and execute it when required. In this Excel 2010 tutorial, you will learn how to protect multiple worksheets in a single click.
How to Protect Multiple Worksheets
To protect multiple worksheets in one go using an Excel Macro:
Basic Macro Info
|Level of Difficulty||Easy|
|Description||This macro will allow a user to simultaneously protect multiple worksheets in a workbook.|
Dim wrksheet As Worksheet
For Each wrksheet In ActiveWorkbook.Worksheets
Note: You can replace password123 with any password of your choice.
How to Use the Macro
- Copy the code.
- Open Excel 2010.
- Press Alt + F11 to enter the Visual Basic Editor.
- If necessary, press Ctrl + R to show the Project Explorer.
- Right-click This Workbook.
- Choose Insert -> Module.
- Paste code into the right pane.
- Press Alt + Q to close the VBE.
- Save the workbook in the XLSM format.
- Select the Developer tab. (If you have a problem displaying the Developer tab, please read our post How to Activate the Developer Tab in Excel 2010.
- On the Developer tab, in the Code group, click Macros.
- In the Macro dialog box, select the macro and click Run.
You should now be able to password-protect all your worksheets in one go.
Allan Ward says
Eddie, Thanks for doing this. It explains how to protect certain cells perfectly. I'll have to think of some more requests for you – you obviously know more about Office than I do!
You are welcome Allan. We are glad that we are able to provide useful content to our readers. We will be more than delighed to write articles as per user requrests.
Kim Eriksen says
I am trying to protect the format of a document. I have read your article and others and am still unsure of how to protect a document that requires input from users. I have an expense form where others will submit their request but the format of the document should not be changed. In this document there are check boxes available and some formulas. How can I protect the document that requires input.
Eddie Gear says
I’ve been having major problems with my Internet Service provider, so gimme a few days to get back to you.
Thanks in advance for your patience.
Eddie Gear says
Could you elaborate on how you are sharing this form with your users?
Vijai Kumar says
I have prepared a file which has around 50 worksheets and i would like to protect all the worksheets so people can view the sheets and cannot edit or delete anything from there. Is there any option which helps to protect all the worksheets instead of protect each worksheet one by one.
Eddie Gear says
Here’s what you need to do.
Hope it helps resolve your problem.