Unprotect Excel Spreadsheets and Workbooks Easily

Udemy Editor

As you may know, Microsoft Excel has built-in features that allow you to control who can edit data, format cells, run reports, and even select cells and ranges. These protection features have applications where they can be very useful. For instance, if you have several people working on a complex workbook, they help to ensure that no formulas get misplaced or removed and, generally, that nobody makes unwanted changes that undo hard work you’ve completed to keep things in order.

That said, Excel’s protection features can be an inconvenience if you aren’t used to them or if they are used imprecisely in workbooks you need to edit. So you will need to understand how these features work and how you can go about unprotecting data to make edits in your work.

For a comprehensive understanding of this tool, you can take a course covering all the basic features and functions of Excel. This guide, though, will focus on how you can unprotect your data, so you can keep moving with the spreadsheets you’re working on.

Basic method for unprotecting data

As of Office 2007, and in later versions, you will handle protection settings by accessing the Review tab from the main ribbon.

The labeling is intuitive. The Protect Workbook feature handles protection for the entire workbook and protect sheet handles protection for the sheet that is currently active. If you are unable to change the size or position of the window or if you are can’t change the order of tabs in the workbook, that is because the workbook is protected. If, on the other hand, you can’t select or edit cells or their content, that is due to sheet protection.

Toggling protection for the workbook

When you click on Protect Workbook, you will either be prompted for a password or, if a password is not enabled, you will see the workbook’s protection menu. In Excel 2007, this menu will give you the option to protect the workbook for structure (the arrangement of sheets) and windows (the size and placement of the workbook’s window). If you were able to access the menu, you can unprotect the workbook simply by making sure the options are unchecked and clicking OK. If, however, the workbook is password protected, you will need to enter the password before you can make adjustments (more on that in a moment).

Toggling protection for the sheet

The Protect Sheet feature has the same basic structure as the Protect Workbook feature. If a password is set, you will need to enter it before you can proceed, and if not, you will go straight to the pop-up window where you can select and de-select protection options. This menu looks similar to the one for the workbook, but you use it a bit differently.

If you want to unprotect the sheet outright, you simply need to uncheck the box at the top, which says “Protect worksheet and contents of locked cells.” Alternatively, you can keep some protections in place for the sheet by enabling individual editing rights in the scrolling options menu underneath.

Ultimately, when dealing with options such as these, it can be helpful to have a thorough understanding of the Microsoft Office Suite in general. For that reason, you might gain an advantage by taking a course covering the fundamentals of the latest Office software.

Managing locked cells

In some cases, you may want to preserve protection for your sheet or workbook, but you are struggling with how to manage locked cells. There may, for instance, be cells that you want to be able to select and edit but are currently inaccessible. For that reason, you want to be familiar with how locking and unlocking cells operates in the program.

If you want certain cells to be unlocked within a protected sheet, here are the steps you can take:

1. Select the cells or range that you want to have unlocked.

2. Right click and select Format cells from the menu.

3. Click on the Protection tab at the far right.

4. The Locked option should be checked by default. Uncheck it.

5. Click OK

Locking or unlocking cells, as you have done here, will have no effect until you enable protection for the sheet.

If you are interested to learn more tricks for managing data in Excel, you should find what you are looking for in an advanced course on Excel.

Regarding Passwords

Clearly, if you are working with password protection in Excel and you want to override the protection features, you are going to have to input the password. Simple enough if you were the one to create it and you know what it is.

But what if you forgot your password, or perhaps one of your associates lost their password on a workbook you are working on together? Are you simply out of luck?

Well, the short answer is no. But dealing with this scenario can be tricky. You have a few options:

1. You can recreate the workbook, which is obviously an intensive and unrealistic option in many cases.

2. You can look for a third-party password recovery tool. There are a number of products, usually offered by small software development companies.

3. You can use a macro.

The third option is possible. Keep in mind that you should not use this on workbooks you do not own or where you do not have permission. Essentially, Excel converts passwords into a simplified string of characters that is fairly simple to recover using a macro.

You will need to use the VBA functions and developer tab, so you might want to get familiar with those tools if you are not already. You can started on that with an online course on VBA for Excel.

If you are comfortable with the development tools, what you will need to do is create a macro, open the VBA editor for that macro, and enter this code:

Sub PasswordBreaker()

Dim i As Integer, j As Integer, k As Integer

Dim l As Integer, m As Integer, n As Integer

Dim i1 As Integer, i2 As Integer, i3 As Integer

Dim i4 As Integer, i5 As Integer, i6 As Integer

On Error Resume Next

For i = 65 To 66: For j = 65 To 66: For k = 65 To 66

For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66

For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66

For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126

ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _

Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _