How to open a password protected excel file. Removing protection from an Excel file. Data Protection Basics in Excel

Change the archive extension to XLSX. You can now freely edit this file in MicroSoft Office Excel. If there are many sheets with editing protection in an Excel workbook, the described procedure must be done with each of them.

Conclusions about protecting Excel workbook sheets from editing

The defense is weak. It's easy. I checked all three methods, they all work. In my opinion, the most convenient one is to use OpenOffice. True, it has a peculiarity: Open Office cannot save files in the XLSX format, only in the older XLS format.

Acknowledgments

The following sources were used when writing this article.

Information is perhaps the most valuable thing there is. Especially in the era of computer technology. This is why software developers provide data encryption capabilities to their users. The craftsmen from Microsoft were no exception, who used all programs office suite added this very feature. However, after encryption, it is important not to forget the password in order to get the information back. This article will tell you how to remove protection in Excel, regardless of whether you have locked a workbook or a sheet.

Unlock methods

You might be interested in:

As was said earlier, you can lock a workbook and a sheet in Excel, so their unlocking method is different. Below they will be discussed in more detail. In addition, I would like to highlight methods that involve removing protection from a sheet or workbook in Excel when you have forgotten the password. This will also be discussed at the end of the article.

Methods 1: Unlock Book

First of all, we’ll look at the method of unlocking the workbook itself, and then we’ll tell you how to unprotect a sheet in Excel 2007.

Afterwards you need to save the changes, in simple words, just save the book. To do this, being in the “File” tab, click on the “Save” or “Save As” line. In the second case, you will then need to specify the save location.

Method 2: Unprotecting the sheet

There is another way to block the contents of a document - protecting the sheet. In this case, the user will be able to open the book without any problems and even view the contents of all sheets; he will only be prohibited from making adjustments to the table. If he tries to do this, a notification of the corresponding content will appear, notifying him of the need to remove protection from the sheet in Excel. Now we will look in detail at how to do this:

As soon as you do this, you will immediately have access to the full range of possibilities for adjusting table data.

Method 3: unlocking by changing the file code

We figured out how to unlock a sheet if the special code is known, but how to remove protection from a sheet in Excel 2010 if you forgot the password? Fortunately, there is a way that doesn’t even involve using third-party software. However, if your table has an XLS extension, it must be recoded to XLSX. This is done as follows:

After this, the file will have the required extension and you can proceed directly to unprotecting the sheet in Excel:

You can run the file in Excel; now the previously locked sheets are available for editing.

Method 4: Using third party programs

In Excel, you can remove protection from a sheet or workbook if you have forgotten the password - using special software. In this example, the Accent OFFICE Password Recovery program will be used.

Once decoding is complete, all passwords will be removed from the document. The success of this operation directly depends on the complexity of the cipher itself.

Microsoft Office Excel is designed to work with information in tables. It is presented in the form of a grid, which is formed by columns and rows. In some cases, the author of a “book” - an Excel document - needs to protect data from changes. You can protect an arbitrary number of cells from entering incorrect data or editing. This is necessary in order to:

• limit the rights of users or groups by setting a password for a specific list (or the entire book);
• protect the elements from changing the cell on their own and losing information.

The article discusses the options available in Excel.

This is how you can prevent interference in an Excel workbook from accidental or intentional interference by one or another user. This will avoid data loss when saving and closing a document, as well as attempts to restore values, which takes time and is not always possible.

Unfortunately, Excel does not have a button that will instantly restrict access to a particular area. Nevertheless, everything can be done using the program’s capabilities without resorting to others. In order to install such protection against entering incorrect data or changing it, use one of the following methods:

1. restriction on entering incorrect information;
2. protecting a selected quantity or a specific group of values ​​from editing;
3. setting different rights for different users or groups;
4. restricting rights to one or more pages of an Excel workbook.

You can also password-protect security features, making changes unavailable to people who don't know the access code. For example, this allows you to split a book into areas with one person working in each area. By combining different functions, you can achieve the type of editing rights restriction you want. The change restriction can also be canceled (if a password has been set, you will need to enter it).

Settings can be made for various types of protected data.

This method is the easiest to use. With it, you can control what you (or another user) puts into the cell. You can filter out data that does not meet certain criteria, which can also be specified. This way, you can prevent entering a negative price, a fractional number of people, or the wrong date for a certain event. In order to install protection, you need:

1. Select the elements to which the function will apply.
2. Go to the “Data” tab in Excel, then in the “Working with Data” area, click “Data Validation”.
3. A window will open - in “Parameters”, specify the type of data you want to protect cells from entering.
4. You can also set up notifications that Excel will display before editing and if you enter incorrect data. This is done in the “Input Message” and “Error Message” tabs, respectively.
5. Then you need to save the settings by clicking “OK” and they will take effect.

Adding automatic notifications for working with cells.

Restriction from editing

Using this function, you can clearly indicate specific cells or groups that need to be protected from modification. This can be done for:

• All elements of the work area - press Ctrl + “A”;
• Specific - select them manually, using Ctrl to add a cell to the list and Shift to include several horizontally, vertically or crosswise;
• Certain groups of elements, for example, only cells with formulas, notes or any others.

Limit the entire work area:

1. Select all elements - press Ctrl + “A” or click on the area where the numbering of rows and columns on the table frame intersects (an empty cell without a number or letter).
2. Select “Format Cells”, in the window go to “Protection” and activate the items “Protected cell” (set by default, optionally or instead), “Hide formulas”.
3. After this, you need to activate the saved page function.

By selecting the entire table or part of it, we restrict access.

Restriction of rights to manually selected cells:

1. Select elements that do not need to be protected by clicking on them, holding Shift (to include in the group list) and Ctrl (to include a specific, including non-adjacent cell).
2. Right-click on the highlighted area to open the context menu.
3. Select “Format Cells”, in the window go to “Protection” and uncheck “Protected Cell” - all other selected elements will be limited in editing when sheet protection is activated.
4. Click "OK" to apply the settings.
5. After this, you need to activate the option.

Restricting access to certain groups of cells is necessary if you need to restrict editing of one or more groups with specific content properties:

1. Specify items belonging to one or more groups that need to be protected. To do this, follow the path “Home” - “Editing” - “Find and Select” - “Selecting a group of cells”.
2. Specify the required options and click OK for the changes to take effect.
3. After this, you need to activate the protection function.

An example of selecting content with specific properties.

How to set worksheet protection in Excel

It is not enough to simply uncheck or check the boxes in the paragraphs on restricting the rights to change data. The cells will become protected (or vice versa, if you unchecked them) after the function is activated for the entire page. Excel's default settings are Protected Cell enabled and Hide Formulas disabled, but you can check this or make adjustments if necessary.

1. Go to the “Review” tab, select “Protect sheet” - this way you will limit the rights to all cells that are checked (the rest can still be changed).
2. You can also make additional settings before applying the option to specify what exactly will be done with the selected items.
3. Confirm activation of access restriction.

If you want to leave the option to format all elements of an Excel page, check only the first three items.

Excel allows you to add a password when protecting a worksheet.

Limiting different ranges for different users

If several people are working on a document, and each of them should have access to only a certain area, it is necessary:

1. Go to the “Review” tab, click “Allow changing ranges”.
2. In a new window, create an area - enter a list of values ​​and a password to access them.
3. Create the required number of custom areas and allow access to any number of users.
4. Install the function for the changes to take effect.

When using such a system of functions, each user will be forced to enter a password before editing a certain section of the table. If he doesn't know the secret code, he won't be able to make changes.

We restrict or provide access to cell ranges.

Restrict one or more sheets from changing and formatting

In most cases, it is not enough to limit the rights to cells and their groups by functionality. It is advisable to prohibit editing an entire Excel page or several. To do this you need:

1. Go to “Review”, select “Protect Sheet” in the “Changes” area.
2. Enter a password if required.
3. Save changes.

Using this function, you can avoid not only changing certain values, but also editing the title, “header” (fixed rows and columns), formatting and resizing in the Excel window.

How to set a password to protect against editing

Restrictions set in an Excel document can be overridden by any user with rights (by default). In order to prevent other users from disabling the function of protecting against editing or entering incorrect data, you can set a password. To do this:

1. Specify the cells or sheets to which you want to restrict access.
2. Go to Review and click Allow Ranges to Change.
3. In a new window, create a range or specify an existing one.
4. Enter a password and save the settings.

After activating the function, the document cannot be changed by a person who does not know the file access code. Be careful, as it is impossible to recover your Excel password - it will be lost along with all your data.

How to remove protection

This algorithm is suitable for returning rights to the entire sheet, and to some of its elements, if they are the only ones blocked. To do this:

1. Specify the list of cells to which you want to restore access (press Ctrl + “A” to select all).
2. Open the “Format Cells” window by right-clicking on the elements area.
3. Go to “Protection” and configure the “Hide formulas” and “Protected cell” items.
4. Save your changes.

After using these functions, the Excel document will again become available to all users. In order to cancel protection for only part of the book, first do this for the entire book, and then select the elements whose rights should be restricted again.

Bottom line

Knowing what storage capabilities Excel offers, you can fine-tune access restrictions. This way you can protect yourself from accidentally entering unwanted data, as well as editing the book by a third-party user. You can password-protect cells, as well as remove protection from unwanted access or amendments in whole or in part. These are the simplest ways to restrict rights that can be configured in Excel itself.

19.02.2017

The Excel spreadsheet processor was developed by Microsoft back in 1985, since then its functionality has expanded significantly and its interface has become more convenient. The program is actively used by financiers, accountants, analysts, and ordinary users. The functionality allows you to create complex tables and make calculations using formulas available in the program or entered by the user yourself, and build graphs and diagrams. The work takes place on so-called sheets, which are included in the book (one Excel document).

Removing password protection from Excel

Microsoft understands that the file may fall into the wrong hands, so it allows the user to set a password to access a specific sheet or the entire document. It is even possible to block individual cells from editing and/or viewing. But what to do if you forgot your password and the file contains important data? You can remove the password yourself using standard Windows programs.

Method 1: Remove the password using an archiver

This method works best with older versions of the program (up to version 2010). The latest versions have a risk of data loss due to new security settings. The essence of the method is to access each sheet separately. To do this, you will need any archiver that can work with .zip files and a tool for editing code (even a standard Windows notepad will do).

Work can only be done with .xlsx files, so pay attention to the format in which the document is saved, because Older versions of Excel save documents in .xls format. If the file has the extension .xls, then resave. To do this, open it in Excel and select the file in the top menu, and from the drop-down menu “Save as”, further in the section “File type” choose “Excel workbook (.xlsx)”.

Next you need to change the extension .xlsx to .zip. To do this, right-click on the file and select rename. At the end of the name, change the extension to .zip. Example: “Table1.xlsx” rename to “Table1.zip”.

If, when you try to rename a file, the extension is not shown, then do the following procedure:

After this procedure, you can easily mark the extension of any file manually.

Next, open the archive and go to the folder "xl", after in "worksheets", where the sheets in .xml format are located. The sheets in this case are called “sheet” and are numbered by creation date (sheet1, sheet2, etc.). You need to open the file using a standard notepad or a special code reactor (for example, NotePad++).

In the file that opens, find the fragment “sheetProtection”. Because there will be a lot of code in the file, it is recommended to use a quick search for the content using a keyboard shortcut Ctrl+F. Find the desired block that has this structure ““sheetProtection variable1=”” variable2=””…”, you can see in more detail in the screenshot below. To reset your password, delete this entire block.

If you perform this procedure with a sheet that was saved in the program version 2010 or higher, you will lose all data. To solve this problem, do not delete the entire block, but only the value of the variable "password". As for the version starting from 2016, there is no variable "password", so it is impossible to remove the password using an archiver in this case.

When finished, save your changes and change the file extension back to .xlsx in the same way as you changed to .zip.

This method allows you to remove the password only from individual sheets, so if you have a large Excel file with several sheets that is completely password-protected, you will have to remove the protection from each one manually.

Method 2: Removing the password via Macro

In this case, you only need the Excel table itself and the ability to work with the developer console. By default, the developer panel is not displayed in the file; to enable it, use the following instructions:

In the developer window that opens, find the button "Macro" or use the keyboard shortcut Alt+F8.

Fill in the field with the name of the macro (you can come up with any name, but it should not contain Russian letters or spaces). Click "Create". The system opens a window for entering the code.

Enter the following code:

Sub Password_Cracker()
Dim t!
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Long
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
Dim kennwort As String
t = Timer
On Error GoTo err_
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
kennwort = Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4) & Chr(i5 )&Chr(i6)
For n=32 To 126
ActiveSheet.Unprotect kennwort & Chr(n)
MsgBox "Done in " & Format(Timer - t, "0.0 sec")
Exit Sub
nxt_: Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Exit Sub
err_: Resume nxt_
End Sub

Close the window, saving the changes. In developer mode, use the Alt+F8 key combination again to see the created macro. Select it and click “Run” in the right corner.

The macro will take approximately 10-60 seconds to complete, depending on the power of your computer and the size of the document.

Conclusion

To independently remove a password from an Excel spreadsheet, you do not need to have the skills of a professional hacker or download additional software. The latter is strictly not recommended, because... you may catch a virus that can cause serious damage to your system.

Tell your friends on social networks

14 comments

Valery Rvachev


Thank you very much for the article. It is written clearly, concisely, without the idiotic distorted words that many people on the Internet are guilty of.
But I was not able to use the excellent methods described in the article.
I tried the first option. I have Microsoft Office 2010. But after replacing the .xlsx extension with .zip I received the program response:
"The folder cannot be opened.
The path to the compressed ZIP folder “C:\Users\Comp\Desktop\Login 02/25/2017- copy.zip” contains invalid characters that make extraction impossible: -, "
I tried the second option. But when creating a macro, it can only be used for open Excel workbooks, and it is impossible to open a workbook without a password. If there is a password, then why look for it? So it didn't work out for me. and in an Excel file I stored passwords for many sites and services. And now suddenly it stopped opening with the password I used to open it hundreds of times.

Yanochka


Valery, I agree with you too. As for storing passwords in a file on a PC, that’s a very dubious idea. I personally keep them in written form, by hand, in a separate notebook, which is in a safe place, and only after my death can it be read by someone.

Installing protection on Excel files is a great way to protect yourself both from intruders and from your own erroneous actions. The trouble is that not all users know how to remove the lock so that, if necessary, they can edit the book or even just view its contents. The question is even more relevant if the password was not set by the user himself, but by another person who passed on the code word, but the inexperienced user does not know how to use it. In addition, there are cases of password loss. Let's find out how you can remove protection from an Excel document if necessary.

There are two types of Excel file locking: workbook protection and sheet protection. Accordingly, the unlocking algorithm depends on which protection method was chosen.

Method 1: Unlocking the Book

First of all, let's find out how to remove protection from a book.

Now, when opening a book, you will no longer need to enter a password and it will no longer be protected.

Method 2: Unlocking the sheet

In addition, you can set a password on a separate sheet. In this case, you will be able to open the workbook and even view information on a locked sheet, but you will no longer be able to change cells in it. When you try to edit, a message appears in the dialog box informing you that the cell is protected from changes.

In order to be able to edit and completely remove protection from the sheet, you will have to perform a number of actions.

After this, the protection will be removed and the user will be able to edit the file. To protect the sheet again, you will have to install its protection again.

Method 3: removing protection by changing the file code

But sometimes there are cases when the user has encrypted the sheet with a password so as not to accidentally make changes to it, but cannot remember the code. What is doubly upsetting is that, as a rule, files with valuable information are encrypted and losing the password to them can be costly for the user. But there is a way out even from this situation. True, you will have to tinker with the document code.

1. If your file has the extension xlsx(Excel workbook), then immediately proceed to the third step of the instructions. If its extension xls(Excel 97-2003 book), then it should be recoded. Fortunately, if only the sheet is encrypted and not the entire workbook, you can open the document and save it in any available format. To do this, go to the tab "File" and click on the item "Save as...".



2. The save window opens. Required in the parameter "File type" set the value "Excel workbook" instead of "Excel 97-2003 book". Click on the button "OK".



3. An xlsx book is essentially a zip archive. We will need to edit one of the files in this archive. But to do this, you will immediately need to change the extension from xlsx to zip. Using Explorer, go to the hard drive directory in which the document is located. If the file extensions are not visible, then click on the button "Arrange" at the top of the window, in the menu that opens, select the item "Folder and Search Options".



4. The folder options window opens. Go to the tab "View". We are looking for an item "Hide extensions for registered file types". Uncheck it and press the button "OK".



5. As you can see, after these steps, if the extension was not displayed, then it appeared. Right-click on the file and select the item in the context menu that appears "Rename".



6. Changing the extension from xlsx on zip.



7. After the renaming is done, Windows treats this document as an archive and you can simply open it using the same Explorer. Double-click on this file.



8. Let's go to the address:

   file_name/xl/worksheets/

   Files with the extension xml This directory contains information about sheets. Open the first of them using any text editor. You can use the built-in Windows Notepad for these purposes, or you can use a more advanced program, for example, Notepad++.



9. After the program has opened, type the key combination on the keyboard Ctrl+F, which causes the internal search of the application. Enter the following expression into the search window:

   We look for it in the text. If we don’t find it, then open the second file, etc. We do this until the element is found. If multiple Excel sheets are protected, the item will be located in multiple files.



10. After this element is detected, we delete it along with all the information from the opening tag to the closing one. Save the file and close the program.



11. We return to the directory where the archive is located and again change its extension from zip to xlsx.

Now, to edit an Excel sheet, you won't need to know the user's forgotten password.

Method 4: Using Third Party Applications

In addition, if you have forgotten the code word, the lock can be removed using specialized third-party applications. In this case, you can remove the password both from the protected sheet and from the entire file. One of the most popular applications in this area is Accent OFFICE Password Recovery. Let's look at the procedure for resetting protection using this utility as an example.

As you can see, there are several ways to remove protection from an Excel document. The user must choose which one to use depending on the type of blocking, as well as on the level of his abilities and how quickly he wants to get a satisfactory result. The method of removing protection using a text editor is faster, but requires some knowledge and effort. Using specialized programs may require a significant amount of time, but the application does almost everything itself.