Protect Your Excel 2010 Worksheet Data

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
Works On	2007,2010
Description	This macro will allow a user to simultaneously protect multiple worksheets in a workbook.

Code:

Sub ProtectWorkbook()
Dim wrksheet As Worksheet
For Each wrksheet In ActiveWorkbook.Worksheets
wrksheet.Protect Password:=”password123”
Next wrksheet
End Sub

Note: You can replace password123 with any password of your choice.

How to Use the Macro

1. Copy the code.
2. Open Excel 2010.
3. Press Alt + F11 to enter the Visual Basic Editor.
4. If necessary, press Ctrl + R to show the Project Explorer.
5. Right-click This Workbook.
6. Choose Insert -> Module.
7. Paste code into the right pane.
8. Press Alt + Q to close the VBE.
9. Save the workbook in the XLSM format.
10. 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.
11. On the Developer tab, in the Code group, click Macros.
12. 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.