There are times when you just need to protect a particular cell or cells with a password so that viewer can’t edit those cells. Or you simply don’t need to change the supposed constant values or formulas in those cells that may affect the other values. And if any change is necessary, only the owner with a password can change it. In this tutorial, you can learn how to protect Excel cells with a password.
Download Practice Workbook
Download the workbook below with the dataset to try the variations yourself while you go through the steps in each different example. Different results are included in different spreadsheets. Use the password “exceldemy” if you want to unlock any cells to edit.
4 Suitable Examples to Protect Excel Cells with Password
The general idea to protect Excel cells with a password is to lock the cells first. Then proceed to protect the sheet using a password. To lock the cells you first need to format the cells. So this is a combination of formatting cells and protecting sheets.
For every variation of protecting Excel cells with a password, I have used the following dataset.
1. Protect All Excel Cells with Password
First, we will see how you can protect all Excel cells in a spreadsheet with a password. To do this select all cells before formatting and protecting them. By default, all cells are locked in Excel. So you may not need to use the first couple of steps. In case you have made adjustments to the protection format of cells, follow the whole process.
Steps:
- At first, select all cells by clicking the triangular sign where the column headers and row headers meet.
- Then in the Home tab, click on the Format Cells popup launcher as shown in the figure to open up the formatting box. Or you can do that by simply pressing Ctrl+1 on your keyboard.
- Now, in the Format Cells box, go to the Protection tab.
- At this point check the Locked option if it is not checked.
- Now click on OK.
- After that, navigate to the Review tab from your ribbon.
- Then select Protect Sheet from the Protect tab.
- Now type the password in Password to unprotect sheet
- Then check the Protect worksheet and contents of locked cells option.
- After that, click on OK.
- Then re-enter the password in the Confirm Password box to confirm.
- Now click on OK.
As a result, all cells in the spreadsheet will be protected with a password from now on. Henceforth if you want to edit any cells, a warning box will appear. For further editing. unprotect the sheet with the password you have just entered.
2. Protect Specific Cells with Password
There are certain conditions where you have to protect just some specific cells so that only you have the authority to edit them. For example, we may need to protect the first, third, and fourth entries of the Name column and the tax amount from the dataset to prevent editing from third parties.
For this reason, we first need to lock the specific cells only. And then protect the sheet to prevent the cells from editing without a password. Follow these steps for a more detailed process.
Steps:
- At first, select all cells by clicking the triangular sign where the column headers and row headers meet.
- Then open up the Format Box by pressing ‘Ctrl+1′ on your keyboard.
- After that, go to the Protection tab.
- Now, uncheck the Locked option to unlock all cells.
- Then click on OK.
- After that, manually select the cells you want to protect with a password by holding Ctrl on your keyboard and then selecting the cells.
- Then open up the Format Cells box by pressing ‘Ctrl+1′ on your keyboard.
- There, go to the Protection tab.
- Now, check the Locked option and click on OK.
- Then select the Review tab from your ribbon.
- Now click on Protect Sheet from the Protect group.
- Consequently, Protect Sheet box will pop up. Now enter the password in the Password to unprotect sheet box.
- Then check the Protect worksheet and contents of locked cells option below it.
- After that, click on OK.
- Finally, re-enter the password in the succeeding box to confirm and click on OK.
Henceforth if you want to edit these particular cells, you will see a warning box. If you want to edit these cells again, then unprotect the sheets with the password to do so.
Similar Readings
- Lock a Cell after Data Entry Using Excel VBA with Message Box Notification Before Locking
- How to Lock Cell Value Once Calculated in Excel (3 Simple Ways)
- How to Protect Excel Cells from Deletion (3 Handy Methods)
3. Protect Specific Rows with Password
Sometimes, while making a worksheet you may need to protect the whole column or row from viewers to edit further. For example, we may need to protect the table header in the dataset with a password. To protect all excel cells with a password in a row/column, follow these steps.
Steps:
- At first, select all cells by clicking the triangular sign where the column headers and row headers meet.
- Then open up the Format Box by pressing ‘Ctrl+1′ on your keyboard.
- After that, go to the Protection tab.
- Now, uncheck the Locked option to unlock all cells.
- Then click on OK.
- Now, select the entire row or column by clicking on the row or column header. I have selected the fourth column here by clicking on 4 on the row headers on the left.
- Now press ‘Ctrl+1′ on your keyboard to open up the Format Cells box.
- Then go to the Properties tab in this box and check the Locked option.
- Now click on OK.
- After that select the Ribbon tab and select Protect Sheet from the Protect group.
- As a result, a Protect Sheet box will pop up. Now enter the password in the Password to unprotect sheet field and check the Protect worksheet and contents of locked cells.
- Then click on OK.
- Next, re-enter the password in the following box and click on OK.
As a result, this will protect Excel cells in the entire row with a password. If you want to edit the cells from the row from now on, you will see a warning box appear.
And if you want to edit the cells, just unprotect them again using the password.
Read More: Protect Excel Cells But Allow Data Entry (2 Quick Methods)
4. Protect Cells Containing Formulas with Password
There are situations where you have to protect the cells that contain formulas. For example, the Tax to be Paid column in the mentioned dataset is determined by a formula. We may want to secure these cells in such a way that the values will change depending on the values from other cells only using the formula but we cannot change it manually to be a random value.
For a large dataset where cells containing formulas are randomly distributed, follow these steps to select and protect excel cells with a password.
Steps:
- At first, select all cells by clicking the triangular sign where the column headers and row headers meet.
- After that, open up the Format Cells box by pressing ‘Ctrl+1′ on your keyboard.
- Now, in this box, go to the Protection tab and uncheck the Locked option.
- Then click on OK.
- Now go to the Home tab and select Find & Select option from the Editing group.
- Then, from the drop-down menu, select Go To Special.
- Consequently, the Go To Special box will appear. Now select the Formulas option from it and click on OK.
- As a result, this will automatically select all the cells with the formulas. If you have a large dataset, this method to select the cells with formulas will be particularly helpful.
- Now, open the Format Cells box again by pressing ‘Ctrl+1′ on your keyboard.
- After that, go to the Protection tab and check Locked. Then click on OK.
- Now, go back to the Reviews tab and select Protect Sheet from the Protect group.
- As a result, the Protect Sheet box will appear. Now write down the password in the Password to unprotect sheet field and check the Protect worksheet and contents of locked cells. Now click on OK.
- Next, in the succeeding box, re-enter the password again and click on OK.
Now you can’t enter random values in the cells that contain formulas. If you attempt to edit one, the warning box will pop up preventing you from doing so.
In case you want to edit the cells again, you should unprotect them by using a password.
Read More: How to Protect Cells without Protecting Sheet in Excel (3 Ways)
Conclusion
These were all different situations and the guides to protect Excel cells with a password. Hope you find this guide helpful and informative. If you have any questions or suggestions, let us know your thoughts below. For more guides like these, visit Exceldemy.com.