Sabrina Ayon
While working in Microsoft Excel, sometimes, we need to protect our data. If we locked a group of our data cells, the contents of those cells can not be modified, erased, or formatted. This is a great feature for preventing the unintentional deletion of complex formulas and functions. In Microsoft Excel, we can easily lock the data. In this article, we will demonstrate how we can lock a group of cells in excel.
Download Practice Workbook
You can download the workbook and practice with them.
7 Different Methods to Lock Group of Cells in Excel
Locking a group of cells helps to protect the data and formulas from being unmodified. You can protect the worksheets to prevent unwanted data modifications in excel. None of the data of a protected worksheet can be modified. Suppose, you have put a lot of effort into a workbook. Now, to make sure no one changes the data by accident, therefore you will need to know how to lock the cells in your excel sheets.
To lock a group of cells in excel, we are going to use the following dataset. The dataset contains some product names, their brands, and sales in January and February, also the total sales for each month.
1. Excel Format Cells Feature to Lock a Group of Cells
One of the most powerful tools in Excel is Format Cells. By the name of this tool, we may think that it will change the formatting of any data, but with this tool, we can also lock a group of cells. So, now let’s see the steps below to lock a group of cells using the Format Cells feature in excel.
STEPS:
- Firstly, select the data cells that you want to lock. So, we select the cell range D5:E12.
- Secondly, go to the Home tab from the ribbon.
- Thirdly, click on the Format drop-down menubar under the Cells category.
- Further, click on the Format Cells.
- Another way to open the Format Cells dialog box is to right-click on the mouse after selecting the cells and from there click on the Format Cells.
- This will open the Format Cells dialog box.
- Now, go to the Protection menu and checkmark the Locked check box.
- Then, click on the OK button.
- By following the above procedures you will be able to lock the cells of data in your spreadsheet.
Read More: How to Lock Multiple Cells in Excel (6 Suitable Methods)
2. ‘Go To Special’ Feature to Lock a Group of Cells in Excel
In Excel, we can use the Go To Special feature to quickly pick all cells that fulfill a set of criteria. We can use the Go To Special feature to lock a group of cells. For this, we need to follow some steps down.
STEPS:
- In the beginning, select the cells with formulas. So, we select D13 and E13.
- After that, go to the Home tab on the ribbon.
- Next, from the Editing group click on the Find & Select drop-down menubar.
- Further, select the Go To Special feature.
- This will appear in the Go To Special dialog box.
- Now, select the Formulas from the Select list.
- Click on the OK button to lock them.
- Finally, just press the F4 key from your keyboard. It will lock the group of cells.
Read More: How to Lock Certain Cells in Excel (4 Methods)
3. Use the Review Tab in Excel to Freeze a Group of Cells
The Review tab includes features such as adding and deleting comments, protecting and unprotecting excel sheets/workbooks and allowing users to follow changes in a multi-user excel workbook. To protect or lock a group of cells in Excel, we need to follow some steps down.
STEPS:
- In the first place, select the whole data.
- Next, go to the Review tab on the ribbon.
- After that, under Protect group, select the Protected Sheet command.
- This will open the Protect Sheet dialog box.
- Now, save a password and click OK.
- To confirm the password type the password again in the Reenter password to proceed type the box on the Confirm Password dialog.
- Further, click on the OK button.
- By confirming the password, you will now see that a pop-up window will appear, just simply click the OK to continue.
- If you want to Unprotect the sheet, just click on the Unprotect Sheet shown in the picture down.
- Finally, type the password that you previously saved and click OK to see the locked cells.
Read More: How to Select a Range of Cells in Excel Formula (4 Methods)
4. Manually Lock a Group of Cells from Ribbon
We can manually lock a group of cells by using the ribbon or toolbar. To use the toolbar let’s see the quick steps down.
STEPS:
- As similar as before, select the cells that you want to lock.
- Then, go to the Home tab.
- After that, select Lock Cell from the Format drop-down menu, under the Cells category.
- An error will show up on the cells, just click ok the error and select Lock Cells.
- By following the easy steps, your selected cells are now locked.
Read More: How to Lock Cells in Excel Formula (2 Easy Ways)
Similar Readings
- What is cell in Excel definition
- What is an Active Cell in Excel?
- Data clean-up techniques in Excel: Filling blank cells
- How to Shift Cells Down in Excel (5 Easy Ways)
- How to shift cells in Excel
5. Lock a Group of Cells with Absolute References in Excel Formula
We can use absolute referencing to lock a group of cells. If we paste the formula someplace else in our worksheet or use the Fill Handle to copy the formula, the cells existing inside the formula will not change to other relative locations if we utilize absolute reference techniques to lock the cells. The ‘$‘ symbol in Excel stands for Absolute Reference. Let’s go through the procedure to lock a group of cells using absolute referencing.
STEPS:
- First, select the cell where you want to use the formula with absolute references.
- Second, type the formula there and press Enter.
=D6+D6*$E$4
- The formula will calculate the price with 15% vat.
- Drag the Fill Handle to copy the formula. And this will lock the cells with absolute references.
- Now, suppose we want to sum the normal price and the price with vat. To do this, we are using the SUM function.
- So, to calculate the total normal price, type the formula in cell D14.
- Hit Enter.
=SUM($D$6:$D$13)
- Furthermore, to calculate the total price with vat, type the formula in cell E14. Press Enter.
=SUM($E$6:$E$13)
- And, that’s it! The cells are now locked.
Read More: How to Group Cells in Excel (6 Different Ways)
6. Freeze Group of Cells by Protecting Workbook
One of the advantages of protecting a worksheet is that we may restrict which cells are utilized for entering data. It will be difficult for others to make changes in your spreadsheet if your sheet is protected. We can prevent worksheets from being mistakenly deleted by other users. Let’s see how to protect or lock a group of cells in a worksheet.
STEPS:
- First, select the sheet from which you want to lock a group of cells.
- Second, go to the Review tab from the ribbon.
- Third, select Protect Workbook under Protect category.
- And, that’s it! Just by some click, your workbook will be protected and your data/a group of cells will be locked.
Read More: How to Protect Selected Cells in Excel (4 Methods)
7. Excel VBA to Lock a Group of Cells in Excel
VBA is the most user-friendly and effective technique to automate Excel. Excel VBA enables far more advanced navigation, complicated execution. We can lock or protect our spreadsheets using VBA code. To do this, we need to follow the steps down.
STEPS:
- Firstly, go to the Developer tab on the ribbon.
- Second, click on Visual Basic or press Alt + F11 to open the Visual Basic Editor.
- Instead of this, to open the Visual Basic Editor is, simply right-click on the sheet and select View Code.
- Now, write down the VBA Code below.
VBA Code:
Sub LockCells()
Dim lckCell As Range
Dim lckRng As Range
ActiveSheet.Unprotect
Range("B4:E13").Locked = False
Set lckRng = ActiveSheet.Range("B4:E13")
For Each lckCell In lckRng.Cells
If lckCell.Value <> "" Then Cells.Locked = True
Next lckCell
ActiveSheet.Protect
End Sub- Next, run the code by pressing the F5 key or clicking the Run Sub button.
- Finally, running this code will lock the cell’s range B4:E13.
Read More: How to Divide a Group of Cells by a Number in Excel (3 Methods)
Conclusion
The above methods will assist you to lock a group of cells in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
Related Articles
- How to Display the Cell Formulas in Excel (6 Methods)
- Check If Multiple Cells Are Equal in Excel (4 Methods)
- How to Select a Range of Cells in Excel (9 Methods)
- Select a Range of Cells in Excel Formula (4 Methods)
- How to Make a Group of Cells Negative in Excel (5 Methods)
- How to Move Selected Cells in Excel with Keyboard (4 Methods)
