How To Lock Cells In Excel

We need to know how to lock our momentous cells in Excel to prohibit anybody from reformatting or deleting them. It may be required, particularly when you share (internal link: How to Share Excel blog) your data.

Here are different cases you may want to lock the cells to prevent users from changing or erasing the values accidentally in Excel:

1. Cells locked by default
2. Lock all the cells in a worksheet
3. Lock the worksheet except for a few cells
4. Lock some specific cells
5. Hide the formulas when cells are locked

In this tutorial, we are going to learn all these cases.

Cells Locked by default

When you open a worksheet, all of your cells are locked by default. Select any cell you want, Right-click on it, and choose Format Cell.

Note: Other ways to open the Format Cells dialogue box are:

1. Go to the Home tab from the ribbon.
2. Click on the small arrow in the Alignment group, the Format Cells dialogue box opens up.

• Use the keyboard shortcut Ctrl+1 to open this window.

Then choose the Protection tab from the format cell dialogue box. Now you can see the Locked checkbox is enabled. But how can we edit the cells although they’re locked?

Actually, cells are ready to protect.

Lock All the Cells in a Worksheet

To protect all cells in a worksheet:

1. Go to the Review tab from the toolbar.
2. In the Changes group, choose the Protect Sheet option, the Protect Sheet dialogue box opens up.
3. Enter a password that is asked to unprotect the sheet (optional) and re-enter it.
4. Press OK.

This method will protect your worksheet, whether cells are locked or unlocked.

After protecting the sheet whenever anyone tries to edit the cells, this massage will open up:

Lock the Entire Sheet Except a Few Cells

Sometimes you need some transformative cells in a protected worksheet. Follow these steps below (according to video 1):

1. Select the cell you want to be unlocked (cells you want to be editable after protecting the sheet.)
2. Press Ctrl+1 to open the Format Cells dialogue box.
3. Go to the Protection tab.
4. Uncheck the Locked checkbox (which is checked by default.)
5. Press OK.
6. Go to the Review tab from the toolbar.
7. In the Changes group, select the Protect Sheet to open the Protect Sheet dialogue box.
8. Enter a password (optional).
9. Press OK.

Now you can change the unlocked cells.

Lock some specific cells

Occasionally some data or formulas are essential, and you don’t want them to be changed independently. So just lock them and protect your data as shown below:

1. Select all cells by right-clicking on the triangle in the left upper corner of the table.
2. Choose the Format Cells.
3. Go to the Protection tab from the Format Cells dialogue box.
4. Uncheck the Locked checkbox and press OK.
5. Select the cells you want to keep locked.
6. Repeat steps two and three but now check the Locked checkbox and press OK.
7. Go to the Review tab and select the Protect Sheet.
8. In the Protect Sheet dialogue box, enter a password and press OK, then re-enter the password and press Enter.

Hide the Formulas When Cells Are Locked

All of the examples which we mentioned before lock the cells and don’t allow others to edit its content, however, those methods leave the formulas visible in the formula bar.

To hide your sensitive formulas, here are the steps (according to video 3):

1. Select the cell which contains the formula.
2. Press Ctrl+1 or right click on the cell and choose the Format Cells.
3. Go to the Protection tab from the Format Cells dialog box.
4. Check the Hidden checkbox (make sure the Locked checkbox is enabled, too.)
5. Press OK.
6. Go to the Review tab from the ribbon.
7. Choose the Protect Sheet option.
8. Enter a password and re-enter it, then press OK.

You can connect with us, ask our experts if you have any inquiries and get more support via Excel Support Services.

Also, reduce costs, accelerate tasks, and improve quality with Excel Automation Services.