While working with Microsoft Excel, sometimes we need to protect our dataset for the convenience of our work. Basically, we may need to protect selected cells for two reasons. One is to lock cells for protecting them from further edits or changes whereas the other reason is to use the locked cells as absolute cell references. Today, in this article, we’ll learn four quick and suitable ways to protect selected cells in Excel effectively with appropriate illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Suitable Ways to Protect Selected Cells in Excel
Let’s say, we have a dataset that contains information about several sales representatives of the Armani group. The delivery and ordered product and the revenue earned by the sales representatives are given in columns D, C, and E respectively. We will protect our dataset by applying the Format Cells command, Quick Access Toolbar, Go To special command, and VBA macros also. Here’s an overview of the dataset for today’s task.
1. Use the Format Cells Option to Protect Selected Cells in Excel
Undoubtedly, using the Format Cells command to protect selected cells in Excel is an easy task. This is also a time-saving way. Let’s follow the steps below to learn!
- First of all, select your dataset to protect, then press Ctrl + 1 simultaneously from your keyboard.
- Hence, a window named Format Cells will appear in front of you. From that window, firstly select the Locked option from the Protection tab and then press OK.
- After that, place your cursor on the active sheet name and press right-click on your Mouse. By right-clicking on your Mouse a window pops up. From that window select the Protect Sheet option.
- Further, immediately, you’ll see the following dialog box namely Protect Sheet. Here, you need to input the password and press OK.
- After pressing the OK option, a dialog box named Confirm Password will instantly appear in front of you. Again, you have to reenter your password and press OK as shown in the below screenshot.
- Subsequently, your selected cells are locked and protected. For example, if you click on the B5 cell to change or edit, you’ll get an error message which depicts your sheet is “a protected sheet” and you need to enter a password to edit.
2. Add a Button from Quick Access Toolbar to Protect Selected Cells in Excel
After using the Format Cells command to protect selected cells, we’ll learn here how to protect selected cells by applying the Lock Cell command in the Quick Access Toolbar (QAT). Let’s follow the instructions below.
- For adding the command, click on the icon of Customize Quick Access Toolbar and choose More Commands.
- Then, choose All Commands from the drop-down list of the Choose commands from. Besides, select the Lock Cell option as shown in the following picture and click on the Add option. Finally, press OK.
- Within a short time, you’ll see an icon of the Lock Cell as shown in the following screenshot. After that, select cells from our dataset. Let’s say, we select cells C4 to D14 for our work. Now, just click on the Lock Cell command from the Quick Access Toolbar.
- After that, from your Preview ribbon, go to,
Preview → Protect Sheet
- Hence, a Protect Sheet window pops up. From that window, press OK to protect your dataset.
- How to Group Cells in Excel (6 Different Ways)
- How to Select Multiple Cells in Excel without Mouse (9 Easy Methods)
- Multiple Excel Cells Are Selected with One Click (4 Causes+Solutions)
- How to Shift Cells Down in Excel without Changing Formula (4 Methods)
- How to Move All Cells Down in Excel (5 Methods)
3. Apply the Go To Special Command to Protect Selected Cells in Excel
More importantly, if you may require to lock certain multiple cells that have formulas, you might do that using this method. Let’s follow the steps below.
- First, from your Home tab, go to,
Home → Editing → Find & Select→ Go To Special
- Then, check the circle before the Formulas option and press OK.
- Shortly, you’ll get the cells having formulas (F5:F14 cell range).
- After that, using the Format Cells discussed in the first method or Lock Cell command discussed in the second method, you may lock the specific cells. Hence, you need to activate the protection of the sheet (step 2 of the first method).
4. Run a VBA Code to Protect Selected Cells in Excel
Last but not the least, we can protect our selected cells by using the VBA code. For example, we want to lock the cell range B5:E14 in the dataset using the Macro. To do that, follow the instructions below.
- Firstly, from your Developer tab, go to,
Developer → Visual Basic
- Secondly, go to,
Insert → Module
- Now, just copy the following code into the newly created module.
Sub Protect_selected_Cells() Dim LPassword As String Range("B5:E14").Select Selection.Locked = True LPassword = InputBox("Enter the Password to Protect") ActiveSheet.Protect Password:=LPassword End Sub
- After that, run the VBA To do that, go to,
Run → Run Sub/UserForm
- After running the code, you’ll get a dialog box where you have to enter the password.
- When you enter the password, immediately the selected cells are locked. If you want to check whether the cell is locked or not, just select a cell (e.g. C5 cell) and try to edit. Shortly, you’ll see an error message as depicted in the following screenshot.
Things to Remember
👉 If the Preview tab is not visible in your ribbon, then place your cursor on any ribbon and press right-click on your mouse then firstly, select Customize Ribbon, and then select Preview.
I hope all of the suitable methods mentioned above to protect selected cells will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
- 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)
- Make a Group of Cells Negative in Excel (5 Methods)
- How to Move Selected Cells in Excel with Keyboard (4 Methods)
- How to Move Cells Up in Excel (3 Easy Ways)