Protect and unprotect worksheets

Hello students, welcome to Kwickprep. You made a fee sheet with a total formula. A clerk types over the formula by mistake, and every total goes wrong. Can you lock the formulas but still let the clerk type the fees? Yes. Today we will learn to protect and unprotect a worksheet.

Let us start with the meaning. Sheet protection stops people from changing locked cells on one worksheet. Each worksheet in a file is protected on its own, so protecting January does not protect February. It is different from workbook protection, which stops sheets from being added or deleted. We use sheet protection to guard formulas, headings and data that should never change.

Two settings on the Protection tab of Format Cells control this. Locked is ticked for every cell by default, and a locked cell cannot be edited once the sheet is protected. Hidden is not ticked by default, and it hides a formula from the formula bar, while the result still shows. Here is the key point. Neither setting does anything until you protect the sheet.

This leads to the big trap in exams and in offices. Every cell starts as Locked. So if you protect the sheet straight away, every single cell is locked. The clerk cannot type fees anywhere at all. The correct order is to unlock the entry cells first, and then protect the sheet.

Step one is to unlock the cells where data will be typed. Select B2 to B40, the fee amount cells. Press Ctrl+1 to open Format Cells. Click the Protection tab. Remove the tick from Locked. Click OK. Nothing seems different yet, and that is normal.

Step two is to protect the sheet. Go to the Review tab. Click Protect Sheet. A box opens with a list of actions that users are still allowed, such as selecting locked cells and selecting unlocked cells. Type a password and click OK. The password is optional, but without it anyone can unprotect the sheet. Retype the same password to confirm, and click OK. Now the sheet is protected.

The Protect Sheet box lets you choose what users may still do. Selecting locked cells is usually allowed, so people can click and read. Selecting unlocked cells must be allowed, or nobody can enter data. Formatting cells is usually blocked, so the look stays neat. Inserting rows is usually blocked, so the layout does not break. Deleting rows is blocked, so data is not lost. Sorting is also usually blocked, and it does not work on locked cells anyway.

Pause and predict. Cells B2 to B40 are unlocked, and C41 holds the total formula. The sheet is now protected. The clerk types fifteen hundred in B7. What happens? It works, because B7 is unlocked, and the total updates. Then the clerk types zero in C41. What happens? Excel shows a message that the cell is on a protected sheet, and the formula stays safe.

When you need to change the formula yourself, unprotect the sheet. Go to the Review tab. Protect Sheet has now become Unprotect Sheet, so click it. Type the password and click OK. Now every cell can be edited again. When you finish, remember to protect the sheet once more.

Sometimes you want to hide how a total is worked out. Select the formula cell and press Ctrl+1. On the Protection tab, tick Hidden. Then protect the sheet as before. The cell still shows the result, but the formula bar stays empty when someone clicks it.

Know the limits of sheet protection. Passwords are case sensitive, so Fees and fees are different. Sheet protection is meant to stop mistakes, not to guard secrets, because the data can still be seen and copied. For secret data, such as salaries, use a password to open the whole file. And keep your password safe, because a forgotten password is very hard to recover.

In LibreOffice Calc, the idea is exactly the same. To unlock cells, open Format Cells and use the Cell Protection tab, then untick Protected. To protect, use the Tools menu, then Protect Sheet. To unprotect, open the Tools menu again, click Protect Sheet to remove the tick, and enter the password.

Let us revise. Every cell starts Locked, but locking works only after you protect the sheet. So first unlock the entry cells, using Ctrl+1 and the Protection tab. Then go to Review, Protect Sheet, choose the allowed actions and add a password. To edit again, use Review, Unprotect Sheet, and type the password. And the Hidden setting hides formulas once the sheet is protected.