If you’re a Microsoft Excel user, you know that it’s easy to make changes to your workbooks, worksheets, and cells. But what if someone wants to edit your data? There are a few ways to protect your workbooks, worksheets, and cells from editing. Here are three tips:
- Use the Protect Workbook button on the ribbon in Microsoft Excel. This button will keep all of your data safe from editing.
- Use the Protect Cells button on the ribbon in Microsoft Excel. This will protect all of your cells from being edited.
- Use the Edit Data command on the ribbon in Microsoft Excel. This command will keep all of your data safe from editing, but it won’t show any errors or warnings until you finish editing.
You’ve worked hard on your spreadsheet. You don’t want anyone to mess it up. Fortunately, Microsoft Excel provides some pretty good tools for preventing people from editing various parts of a workbook.
Protection in Microsoft Excel is password-based and happens at three different levels.
Workbook: You have a few options for protecting a workbook. You can encrypt it with a password to limit who can even open it. You can make the file open as read-only by default so that people have to opt into editing it. And you protect the structure of a workbook so that anyone can open it, but they need a password to rearrange, rename, delete, or create new worksheets. Worksheet: You can protect the data on individual worksheets from being changed. Cell: You can also protect just specific cells on a worksheet from being changed. Technically this method involves protecting a worksheet and then allowing certain cells to be exempt from that protection.
You can even combine the protection of those different levels for different effects.
Protect an Entire Workbook from Editing
You have three choices when it comes to protecting an entire Excel workbook: encrypt the workbook with a password, make the workbook read-only, or protect just the structure of a workbook.
Encrypt a Workbook with a Password
For the best protection, you can encrypt the file with a password. Whenever someone tries to open the document, Excel prompts them for a password first.
To set it up, open your Excel file and head to the File menu. You’ll see the “Info” category by default. Click the “Protect Workbook” button and then choose “Encrypt with Password” from the dropdown menu.
In the Encrypt Document window that opens, type your password and then click “OK.”
Note: Pay attention to the warning in this window. Excel does not provide any way to recover a forgotten password, so make sure you use one you’ll remember.
Type your password again to confirm and then click “OK.”
You’ll be returned to your Excel sheet. But, after you close it, the next time you open it, Excel will prompt you to enter the password.
If you ever want to remove the password protection from the file, open it up (which of course requires you to provide the current password), and then follow the same steps you used for assigning the password. Only this time, make the password field blank and then click “OK.”
Make a Workbook Read-Only
Making a workbook open as read-only is super simple. It doesn’t offer any real protection because anyone who opens the file can enable editing, but it can serve as a suggestion to be careful about editing the file.
To set it up, open your Excel file and head to the File menu. You’ll see the “Info” category by default. Click the “Protect Workbook” button and then choose “Encrypt with Password” from the dropdown menu.
Now, whenever anyone (including you) opens the file, they get a warning stating that the file’s author would prefer they open it as read-only unless they need to make changes.
To remove the read-only setting, head back to the File menu, click the “Protect Workbook” button again, and toggle the “Always Open Read-Only” setting off.
Protect a Workbook’s Structure
The final way you can add protection at the workbook level is by protecting the workbook’s structure. This type of protection prevents people who don’t have the password from making changes at the workbook level, which means they won’t be able to add, remove, rename, or move worksheets.
To set it up, open your Excel file and head to the File menu. You’ll see the “Info” category by default. Click the “Protect Workbook” button and then choose “Encrypt with Password” from the dropdown menu.
Type your password and click “OK.”
Confirm your password and click “OK.”
Anyone can still open the document (assuming you didn’t also encrypt the workbook with a password), but they won’t have access to the structural commands.
If someone knows the password, they can get access to those commands by switching over to the “Review” tab and clicking the “Protect Workbook” button.
They can then enter the password.
And the structural commands become available.
It’s important to understand, however, that this action removes the workbook structure protection from the document. To reinstate it, you must go back to the file menu and protect the workbook again.
Protect a Worksheet from Editing
You can also protect individual worksheets from editing. When you protect a worksheet, Excel locks all of the cells from editing. Protecting your worksheet means that no one can edit, reformat, or delete the content.
Click on the “Review” tab on the main Excel ribbon.
Click “Protect Sheet.”
Enter the password you would like to use to unlock the sheet in the future.
Select the permissions you would like users to have for the worksheet after it is locked. For example, you might want to allow people to format, but not delete, rows and columns.
Click “OK” when you’re done selecting permissions.
Re-enter the password you made to confirm that you remember it and then click “OK.”
If you need to remove that protection, head to the “Review” tab and click the “Unprotect Sheet” button.
Type your password and then click “OK.”
Your sheet is now unprotected. Note that the protection is entirely removed and that you’ll need to protect the sheet again if you want.
Protect Specific Cells From Editing
Sometimes, you may only want to protect specific cells from editing in Microsoft Excel. For example, you might have an important formula or instructions that you want to keep safe. Whatever the reason, you can easily lock only certain cells in Microsoft Excel.
Start by selecting the cells you do not want to be locked. It might seem counterintuitive, but hey, that’s Office for you.
Now, right-click on the selected cells and choose the “Format Cells” command.
In the Format Cells window, switch to the “Protection” tab.
Untick the “Locked” checkbox.
And then click “OK.”
Now that you’ve selected the cells for which you want to allow editing, you can lock the rest of the worksheet by following the instructions in the previous section.
Note that you can lock a worksheet first and then select the cells you want to unlock, but Excel can be a little flaky about that. This method of selecting the cells you want to stay unlocked and then locking the sheet works better.
