To protect cells from editing, you must lock them so that no one using your spreadsheet can edit the locked cells.
Cell locking in Excel is very important because it protects your worksheet from things like accidentally deleted formulas, incorrect formatting, and accidentally changed data values.
Accidents and similar errors can lead to serious problems. So, you need to learn how to lock cells in Excel to prevent users from accidentally changing the formulas and data in the worksheet.
To learn how to lock cells in Excel, let’s look at the following example where we lock cell G7 which contains the formula VLOOKUP, you will also learn how to lock all cells in Excel at once.
alt=block cells in an Excel formula width=640 height=407 data-recalc-dims=1 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/How-to-Protect-or-Lock-cells-in-Excel-for-Editing.png />
How to lock certain cells
When you secure a worksheet, the entire worksheet becomes read-only, meaning that none of the cells can be changed.
However, you can protect the worksheet so that only selected cells can be changed (by locking only certain cells in the worksheet).
To secure individual cells in Excel, you must first unlock all cells, since all cells are locked by default, then lock the cell(s) you want to lock, and finally secure the worksheet. The following example shows how to do this:
- Step one: Select all cells on the sheet.
To select all cells, click the small triangle in the upper left corner of the
grid rows, just after column A.
alt=Select all cells to protect for editing in Excel width=640 height=417 data-recalc-dims=1 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/How-to-Protect-or-Lock-cells-in-Excel-for-Editing.gif />.
- Step Two: Right-click and choose Format Cells from the menu.
alt=Right click on selected cells and choose Format Cells width=137 height=300 data-recalc-dims=1 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618796114_940_How-to-Protect-or-Lock-cells-in-Excel-for-Editing.png />
The Format Cells dialog box appears.
- Step Three: Click on the Protection tab. Then check the Locked box to disable it. Then click on OK.
alt=Go to the Security tab and check Locked width=529 height=465 data-recalc-dims=1 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618796115_217_How-to-Protect-or-Lock-cells-in-Excel-for-Editing.png />.
OPINION: This action unlocks all cells in the sheet. Now you can
lock your individual cells.
- Step four: Select the cell(s) you want to lock. Then right-click and choose Format Cells from the menu.
In our example, cell G7 is the cell we want to time. So we right-click on cell
G7 and click Format Cells from the menu.
alt=Shortcut for locking individual cells in Excel width=386 height=376 data-recalc-dims=1 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618796116_767_How-to-Protect-or-Lock-cells-in-Excel-for-Editing.png />
If you want to lock more than one cell, just select the range you want to lock. Or hold down the Ctrl key and successively click on each cell you want to lock, then right-click and choose Format Cells.
The Format Cells window appears.
- Step Five: Click on the Protection tab. Then click on the Locked box to activate it. Then click on OK.
alt=How to secure cells in Excel width=530 height=465 data-recalc-dims=1 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618796117_95_How-to-Protect-or-Lock-cells-in-Excel-for-Editing.png />
Message: If you also check the Hidden box, the contents of cell
will not be displayed in the formula row. The cell still appears in Worksheet
, but if the cell uses a formula, the formula is not visible.
- Step six: Protect the sheet
Now when you secure a worksheet, Excel only locks cell G7
, not all cells in the worksheet.
This is how you protect the worktop:
- Right-click on the worksheet tab and select Protect Sheet
alt=Right-click on the Spreadsheet tab and select Protect Sheet width=640 height=417 data-recalc-dims=1 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618796118_757_How-to-Protect-or-Lock-cells-in-Excel-for-Editing.gif />.
- The Sheet Protect dialog box appears. Enter a password in the Password field to override the sheet’s security. This field is optional. You can ignore it, so anyone using your spreadsheet can copy it without your password.
- Select the Protect worksheet and locked cell contents check box.
- In the Allow all users of this worksheet list, check what people should be able to do with locked cells.
- Then click on OK.
- Repeat the first step to remove the film protection again.
Cell G7, which contains the formula, is now locked. A value of
means that the contents of the cell can no longer be changed. When you try to edit a
locked cell, Excel warns you that the cell you are trying to edit is a
locked cell in the protected worksheet.
alt=Warning : The cell or chart you are trying to edit is a protected sheet. width=640 height=87 data-recalc-dims=1 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618796118_525_How-to-Protect-or-Lock-cells-in-Excel-for-Editing.png />
To change the contents of a locked and protected cell
, you must unprotect the worksheet.
What users can do
with a locked cell
When the Protect Cells
windows are displayed, Excel selects only the first two options by default, while
restricts all other options, so you can specify what users are allowed to do with locked
alt=Create a spreadsheet password width=274 height=308 data-recalc-dims=1 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618796119_722_How-to-Protect-or-Lock-cells-in-Excel-for-Editing.png />
For example, if you choose Review→Modifications→Protect Sheet, Excel will not execute the command until you have specified which parts of the sheet you want to protect.
Here is a detailed overview of the options:
- Select Locked Cells : This option is selected by default. Allows spreadsheet users to select locked cells using the keyboard or mouse. If you want to prevent people from walking around in locked cells, uncheck this box.
- Choose Unlocked Cells: This option is also selected by default. Allows spreadsheet users to select unlocked cells using the keyboard or mouse. If you want to prevent people from selecting cells that are not locked, you must uncheck this box.
- Format the cells: The user can format individual cells if this option is enabled.
- Speaker Format : The user can hide or change the width of the columns if this option is enabled.
- Format lines : The user can hide or change the height of the lines if this option is enabled.
- Place the speaker: The user can insert a new column if this option is enabled.
- Insert the line: The user can insert a new line if this option is enabled.
- Inserting hyperlinks: The user can insert hyperlinks (even in locked cells) if this option is enabled.
- Delete columns : The user can delete columns if this box is checked.
- Delete the rules : The user can delete rows if this option is enabled.
- More or less: If this option is enabled, the user can sort the data in the range, provided there are no cells or locked cells in the range.
- Use the automatic filter: If this option is set, the user can use the existing automatic filtering.
- Using PivotTable and PivotChart : If this option is enabled, the user can change the format of the pivot table or create new pivot tables.
- Editing objects : With this option enabled, you can make changes to objects (such as shapes) and images, and insert and delete comments.
- Modify the scripts : If this option is enabled, the user can use the functions of the script manager.
How to close all cells
Locking all cells is very easy because all
cells are already locked by default.
So, to lock all cells in Excel, just turn on worksheet lock
, without making any changes, and your entire worksheet will be locked
However, if some cells are locked and others are not,
you must follow the steps below to lock all cells:
- Select all cells on the sheet.
- Right-click and then click Format Cells from the
- Click on the Protection tab. Then check the Locked box if it is not selected in
- Then click on OK.
- Protect the sheet
If you now secure the worksheet, all cells in Worksheet
will be locked and cannot be edited.
how to lock specific cells in excelhow to lock cells in excel formulahow to lock cells in excel 2016how to lock cells in excel shortcuthow to unlock cells in excelhow to protect cells in excel 2010 without protecting sheet,People also search for,how to lock specific cells in excel,how to protect cells in excel 2010 without protecting sheet,how to protect cells in excel without protecting sheet,how to protect excel workbook from editing,how to lock cells in excel formula,how to lock cells in excel 2016,how to lock cells in excel shortcut,how to unlock cells in excel