Sometimes the data in your worksheets contain important information that you may not want others to edit or delete. Fortunately, you can protect sensitive information in elements down to the cell level in Excel. The following articles explain how to do that and more with Excel's protection features.
Tips, Tricks, and Answers
The following articles are available for the 'Worksheet Protection' topic. Click the article''s title (shown in bold) to see the associated article.
Adding Comments to Protected Worksheets
When you protect a worksheet, Excel stops users from editing or otherwise making changes to the data in the worksheet. If you want to allow users to add or make changes to comments, you'll need to apply the workarounds in this tip.
Automatically Protecting After Input
Do you want user-entered data to be immediately protected so that it cannot be changed? This can be done relatively easily using a simple macro.
Checking Lock Status of Cells
When you first create a worksheet, all the cells in that worksheet are formatted as locked. As you unlock various cells on the worksheet, you can easily lose track of which cells are locked and unlocked. Here's some ideas on how you can easily check the lock status of individual cells.
Controlling Entry Order on Unprotected Cells
When you protect a worksheet, one of the benefits is that you can limit which cells can be used for data entry. How a user moves from cell to cell is controlled by Excel. If you want to control the order of cell selection rather than having Excel control it, read on.
Enabling Editing Erases Worksheet
If you receive a protected worksheet that you want to edit, how do you proceed if you try to unprotect the worksheet and everything it contains disappears? This tip looks at a few options you might try in order to make the edits.
Forcing a Worksheet to be Protected Again
Excel allows you to protect your worksheets so they can only be changed as you want to have happen. If you unprotect a worksheet and then save the workbook, that status (unprotected) is saved as well. This tip explains how you can make sure that a worksheet remains protected when saving.
Functioning Check Boxes in a Protected Worksheet
Want to protect a worksheet but maintain the ability to make changes to the check boxes you add to the worksheet? Here is some information on how check boxes work, and how to update them within a protected worksheet.
Hiding and Protecting Columns
Want to hide certain columns within a worksheet so the contents are not visible to others? The answer lies in formatting the protection features in Excel.
Inserting and Deleting Rows in a Protected Worksheet
You've protected and saved your worksheet with explicit instructions that you be allowed to insert and delete rows. But when you try it, you find that you can't get rid of rows you want to remove. This is due to the way that Excel interprets your protection instructions, as explained in this tip.
Limiting Input by Time of Day
Excel provides some great tools that allow you to limit what is entered into a worksheet. This tip looks at how those tools can be utilized to limit when data is actually entered.
Locking a Worksheet Automatically
The protection features built into Excel can help you to make sure that only things are changeable that you want changed. Even so, there may be times when you want to modify the protection and Excel doesn't cooperate. Here's one such instance.
Locking All Non-Empty Cells
Need to make sure that your worksheet is locked, with only the blank cells accessible to editing? You can do this easily by using the Go To Special dialog box, or you can use a macro to do your configuration.
Locking Worksheet Names
Want to stop other people from changing the names of your worksheets? You can provide the desired safeguard by using the workbook protection features built into Excel.
Preventing Someone from Recreating a Protected Worksheet
When you share a protected workbook with other people, you may not want them to get around the protection by creating a new workbook from scratch. Here are some ideas on how to avoid this end-run on your work.
Protecting a Graphic
Need to make sure that someone cannot delete a graphic in a worksheet? The ability to protect the graphic depends on where you place it and how you protect the worksheet.
Protecting a Worksheet's Format
You can protect various parts of your worksheets by using the tools built into Excel. One thing you can protect is the format of the worksheet, as described in this tip.
Protecting Many Worksheets
Need to protect a lot of worksheets? Rather than protect the sheets individually, you'll appreciate the macros discussed in this tip. They allow you to protect all the worksheets with a single command.
Protecting Worksheets from Deletion
If you share a workbook with others in your office, you will probably want to make sure that some of the worksheets don't get deleted. Here are some ideas on how to get the protection you need.
Sorting Data on Protected Worksheets
Protect a worksheet and you limit exactly what can be done with the data in the worksheet. One of the things that could be limited is the ability to sort the data. This tip explains how you can allow that data to be sorted.
Spell-Checking in a Protected Worksheet
When you protect a worksheet, you can't use some tools, including the spell-checker. If you want to use it, you must unprotect the worksheet, run the check, and then protect it again. All of this can be done quite quickly by using the macros discussed in this tip.
Unlocking a Worksheet with an Unknown Password
It is not unusual, in a corporate world, to be handed a worksheet whose source you don't know. If that worksheet is locked and you need to change it, then you become very interested in figuring out how to unlock the worksheet.
Using a Protected Worksheet
If you have a worksheet protected, it may not be immediately evident that it really is protected. This tip explains some of the tell-tale signs you can use to determine if protection is in play.
Visually Showing a Protection Status
Need to know if a worksheet or workbook is currently protected? Excel provides some tell-tale signs, but here are some ways you can know for sure.