How to Protect Selected Cells in Excel

It’s sometimes essential to protect the data on your Excel sheets against unintentional modifications or unauthorized access. 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. In this article, we will demonstrate how to protect selected cells in Excel.

You are aware of how important it is to maintain the integrity of your data if you have ever shared an Excel sheet with another user. However, it can take a lot of time and effort to remember who has access to which cells. You may feel secure knowing that your data is safe and secure because we’ll show you how to protect specific cells and ranges of cells in Excel in this article.

We’ll go over a number of strategies, such as formula locking, password protection, and limiting editing rights. We’ll also go through how, if required, to take the protection off of cells or sheets. Readers will be able to secure their critical data by following the instructions provided in this article, ensuring that only authorized people may make modifications.

Below is an overview image of how to protect selected cells in Excel.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.

How to Protect Selected Cells in Excel: 4 Suitable Ways

In the following section, we will use 4 effective methods to protect selected cells in Excel. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

To protect selected cells in Excel, we are going to use the following dataset. The dataset contains some product names, their brands, and sales in 2021 and 2022, also the total sales for each month. In this case, we want to protect two columns: Sales in 2021 and Sales in 2022 because both columns contain formulas, and our critical parameter, Total Sales, is dependent on their values.

1. Protect Selected Cells in Excel Using Home Tab

By default, the worksheet is locked. So, first, we need to unlock the entire worksheet then we can protect certain cells as we want. Let’s walk through the following steps to complete the task.

📌 Steps:

• First, select the entire worksheet by clicking on the sign as shown in the image.

• Now, right-click on the mouse button and select Format Cells.

• You can see that all of the cells are locked by default, as we previously informed you.
• So, just uncheck the locked options, and click OK.

• Now, choose the cells you wish to keep protected (in this case, we want to protect the Sales in 2021 and Sales in 2022 columns, since they include formulas and the values in these columns, affect our key parameter, Total Sales).
• Next, go to Home > Format > Lock Cells.

• After that, go to the Review tab and select Protect Sheet.

• Therefore, the Protect Sheet window will appear. Now, give the password as you like.

• Clicking OK will ask you to Re-enter the password.

• Finally, click OK and done. All the cells from the selected column will be protected.
• At this point, click on any of the cells in the selected column and a warning message box will pop up.
• However, you can easily modify the other cells except for protected cells as shown below.
• If you want to unprotect selected cells you need to go to the Review tab and select Unprotect Sheet.

Read More: How to Lock Cells in Excel Formula (2 Easy Ways)

2. Using Format Cells to Protect Selected Cells in Excel

Another method to protect selected cells in Excel is the Format Cells option using a shortcut. Here we want to protect all the cells in the Sales in 2022 column. Let’s follow the following process.

📌 Steps:

• First, select the entire worksheet by clicking on the sign as shown in the image.

• Now, right-click on the mouse button and select Format Cells.

• You can see that all of the cells are locked by default, as we previously informed you.
• So, just uncheck the Locked options, and click OK.

• Now, choose the cells you wish to keep protected (in this case, we want to protect the Sales in 2021 and Sales in 2022 columns) and press the CTRL+1 key.
• This time, check the Locked box, as shown in the image below.

• After that, we have to do all the work from the Review tab. So, from here, we have to follow the steps from Method 1.

Similar Readings

• 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 Containing Formulas in Excel

Protecting particular cells has been demonstrated so far. What if we have a sizable dataset with formulas? In Excel, you may quickly protect particular cells that contain formulas. Let’s follow the following process.

📌 Steps:

• First, select the entire worksheet by clicking on the sign as shown in the image.

• Now, right-click on the mouse button and select Format Cells.

• You can see that all of the cells are locked by default, as we previously informed you.
• So, just uncheck the Locked options, and click OK.

• Now, press CTRL+G and a dialogue box will pop up.

• From the dialogue box, select Special and another dialogue box will pop up this time. We select the Formulas option from there. Like the image below.

• After clicking OK, all our formula cells will be selected automatically.

• After that, we have to do all the work from the Review tab. So, from here, we have to follow the steps from Method 1.

Read More: Select All Cells with Data in Excel (5 Easy Methods)

4. Run a VBA Code to Protect Selected Cells in Excel

In this method, we will demonstrate how to protect selected cells using Excel VBA. Let’s walk through the following steps to complete the task.

📌 Steps:

• First, right-click on the sheet and go to View Code.

• After that, copy and paste the VBA code below.

Sub CertainCellLocker()
Cells.Select
Selection.Locked = False
Range("B5:B13,E5:E13").Select
Selection.Locked = True
ActiveSheet.Unprotect Password:="exceldemy"
ActiveSheet.Range("D5:E13").Cells.Locked = True
ActiveSheet.Protect Password:="exceldemy"
End Sub

To unlock the by default locked cells, we commanded Selection.Locked = False.

Then, we specified cells by Range(“D5:E13”).Select and protect them through the code Selection.Locked = True.

• After that, press the F5 or play button to run the code.
• As you can see, from the below image, protect our selected cells.

Read More: How to Lock Certain Cells in Excel (4 Methods)

How to Protect Entire Sheet in Excel

One of the simplest ways to protect an Excel sheet is to use the Protect Sheet command under the Review Tab.

📌 Steps:

• First, go to the Review tab and select Protect Sheet.

• Therefore, the Protect Sheet window will appear. Now, give the password as you like.

• Clicking OK will ask you to Re-enter the password.

• Thus, the sheet will be protected. If you want to change any cell value then it will show you a warning message.

How to Protect Workbook in Excel

In the Review Tab, there is another option named Protect Workbook that will protect the sheet as well as the whole workbook.

📌 Steps:

• Select the Review tab, and then choose Protect Workbook.

• A box named Protect Structure and Windows will appear.
• Write down the password and click OK.

• Rewrite the password in the Re-enter password to proceed in the Confirm Password.
• Press OK to finish the protection process.

So, you won’t be able to do any further editing without a password.

Frequently Asked Questions

1. Can I protect specific cells in Excel?

Uncheck the Locked option under the Protection tab, then click OK. Now only choose the cells you want to keep protected. Once more, right-click and select Format Cells. Select the Locked option under the protection tab, then click OK.

2. How do I lock individual cells in Excel without protecting the sheet?

Selecting the cells you want to lock is the first step.

Select Format on the far right side of the Home tab on the Excel Sheet.

Select Lock Cell from the drop-down option.

3. Where is the protection tab in Excel?

Select Protect Sheet under the Review tab. Choose the components you want the user to be able to modify from the Allow all users of this worksheet to list.

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.

Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to protect selected cells in Excel. You can effectively protect selected cells using the Format Cells feature or Go To Special Command. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!

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)
• 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)