Program for removing protection from excel cells. Excel unprotect a sheet without knowing the password? How to remove protection from an Excel file if you know the password

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 follow this 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 will 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.

Good day! If you have protected your MS Excel 2007-2010 sheets from changes and accidentally forgot your password, then the problem can be solved quite simply, even without the use of third-party programs. I will describe here two ways to do this. They use the same method, but use two different programs for this purpose: WinRAR and Total Commander.

Method for removing protection from an Excel file (using WinRAR):

change the file extension XLSX to ZIP
open the winrar zip file
look for the XML file along the path “File.xlsx/xl/worksheets/sheet1.xml” or “sheet2″. Below these names are the pages of the book.
We extract the xml file from the XLSX container and open it with a text editor.
We look for the substring “sheetProtection” in the text.
If one is found. This means that the sheet contains protection; if there is no such block, then there is no protection. We delete the entire block in tags, for example
sheetProtection password=»JHVB76JHGV6″ sheet=»1″ objects=»1″ scenarios=»1″ selectLockedCells=»1″ selectUnlockedCells=»1″. (from "" including the parentheses themselves)
Save the file to its original location.
Change the file extension and open it using Excel 2007 - 2010 in the usual way.

Method for removing protection from an Excel file (using Total Commander):

Open a file through TotalCommander - place the cursor on it and press Ctrl+PageDown. An archive will open (which is the *.xlsx format), in which you will see several folders and files
Along the path “.../xl/worksheets/sheetN.xml”, where N is the sheet number, i.e. 1,2,3... select a Sheet protected by a password (or carry out the following procedure one by one for all password-protected sheets).
Extract the file “sheetN.xml” from the XLSX container to any temporary folder and open it with a text editor (F4 in Total Commander).
Using a text search (Ctrl+F4) we look for “password”.
The editor finds the XML container. (Your values may be different)
Remove the entire “sheetProtection” container from “<» до «>».
Save the file (Ctrl + S).
We copy the file back to the XLSX container and replace the old one (just in case, you can first save the original one somewhere else in case you need to roll back.

That's it. The protection from the sheet has been removed. You can use your file!

You worked long and hard on the file, created complex formulas and entered a lot of data. Of course, it is better to protect such a file with a password! And now six months pass, you need to make changes to the file, but the password has been safely forgotten? How can I now remove the protection of an Excel sheet or, as they say, reset the password?

How to quickly unprotect an Excel sheet if you forgot your password? Essentially cracking the book.

There are several ways, but except for one they are all quite cumbersome. Create () double-click on “This book” as in the picture

Copy the macro into the window on the right

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

Situations when users create documents in the form of spreadsheets and protect them with a password occur quite often. This is sometimes necessary so that changes are not made to the formulas or they are simply not visible. Many people are starting to think about how to remove the password from an Excel file or remove the protection. Below are a few simple methods that allow you to perform such operations in just a couple of minutes.

How to remove protection from an Excel file if you know the password?

First, let's consider the common situation when a sheet or group of sheets is protected, but the user knows the password to be able to change the contents.

The algorithm for removing protection is as follows. First, you should use the main panel, in which you select the review section and then go to the unprotect line. To confirm the action, you will be prompted to enter a valid password, after which the sheet or file will become available for editing.

This one is simple. But how then can a user who needs to make changes to the file remove the password from a file but does not know or does not remember the required combination? There are several methods you can use here.

How to remove a password from an Excel file using the program?

The password removal method looks quite simple. To do this, in an open document you need to use the file menu, where you select the line of information about it.

After this, the button to set protection for the book is pressed, and then the password encryption option is used. The window that appears will show the previously installed combination. How to remove password from Excel file? Yes, just remove all the characters from the password line and save the document again. When you reopen the workbook, you will no longer be prompted to enter a password.

Using File Format Change

There is another equally effective way to resolve the issue of how to remove a password from an Excel file. The point is to change the format of the source document.

First, the open file must be saved directly in the program, selecting XML as the format. The new document is then opened in regular Notepad using the right-click menu and the "Open with..." command, and there you should look for the password tag (usually it looks like Password), then delete the contents and simply click on save document (for To quickly save, you can use the combination Ctrl+S).

But if you have a large number of sheets, this method may not work, so you should initially resave the document in XLSX format, then open it using any 7-Zip) and extract the contents to any convenient location.

Next, you need to find the Worksheets folder in the unpacked directories, which will contain all the sheet files in XML format (Sheet1, Sheet2, etc.). After this, for each such object, you should repeat the editing operation in the text application. Only the sheetProtection tag is searched first, and then everything that is between the combinations “ " This is followed by a standard save, then you will need to drag the saved files into the archiver window and agree to replace the contents. After completing all these steps in the spreadsheet editor itself, the files can be saved in their native format.

Third party programs

Finally, the question of how to remove a password from an Excel file can be solved using special script programs like Password Remover.

After installation, the application integrates its own commands into office applications (not only Excel, but also all others). To remove the password, when you first start with the built-in VBA script, you first need to agree to the execution of the macro, then open the file, and use the Straxx command in the main editor panel. Here you need to understand that the application is not a hacker and does not work on the principle of randomly selecting the desired combination. It disables file protection from the inside, so it is easy and safe to use. Reviews from users who have used this script in practice indicate that the entire process of removing protection takes about two minutes.

Excel from the Microsoft Office suite is perhaps the most popular office application. It is used everywhere for storing data, working with tables, importing and exporting information for other purposes. Microsoft Excel has a lot of functions for storing and protecting information, which are useful to activate if several people are working on a file at the same time. Among the security features in Excel, it is possible to set password protection on a separate sheet, limiting various actions with the data on it. In this article, we will look at how to protect a sheet in Excel, as well as how to remove it, including without knowing the password.

Table of contents:

How to protect a sheet and password in Excel

To protect information on a sheet in Microsoft Excel from unwanted changes, just set a password on the sheet. This can be done using functions built into the program:

How to remove password protection from a worksheet in Excel

If you receive an Excel file with a password set on a sheet, and you know the password, removing it is not difficult. To do this:

After this, the protection from the sheet in Excel will be removed, and it will be possible to perform all actions available to the program with the sheet of this file.

How to remove password protection from a worksheet in Excel without knowing the password

There are several ways to remove protection from an Excel sheet without knowing the password. Below we consider two options:

Using an archiver. This method is more complicated, but it allows you to remove the password from a sheet on a computer that is not connected to the Internet.
Using the Google Sheets service. A simpler option to remove protection from a sheet, but requires an Internet connection.

Important: You can find all kinds of programs on the Internet that promise to remove protection from a sheet in Excel without a password. We do not recommend installing such applications on your computer. There is a high chance that they may be infected with viruses.

Using an archiver

Let's look at a way to remove protection from a sheet in Excel without knowing the password. To do this, you will need any archiver installed on your computer - WinRar, WinZip or another. Perform the following steps in sequence:

Set Windows Explorer to display file name extensions in the folder where the worksheet-protected Excel file is located. To do this, in the explorer at the top, switch to the “View” tab and check the option;
Next, you need to go to the Excel file renaming mode (right-click on it and select the “Rename” option). As you can see, it is possible to change not only the file name, but also the extension. Change the extension “.xlsx” to “.zip”;
When changing the name, a warning window will appear that this may have a negative effect on the file. Click “Yes” to confirm that you want to change the file;
Next, you need to open the resulting “.zip” file using any archiver;
Inside the archive you can see several files and folders. Go to the “xl” folder;
Next, go to the “worksheets” folder;
Here, all sheets of this Excel document are displayed in “.xml” format. The sheets are assigned a serial number, depending on their sequence in the document itself. Select the “sheepN” file, depending on the sheet from which you want to remove protection. Click on it and move the file to any folder in Windows Explorer;
After that, in Explorer, right-click on the file and select the “Open with” - “Notepad” option to switch to its editing mode;
The text of the file, as well as its technical information, will open in Notepad. Here you need to edit the code to remove references to the password. Press the “Ctrl+F” combination on your keyboard to open a document search and enter the phrase “sheetProtection” into it;
One mention of this phrase in the document will be found. This expression is enclosed together with other variables in a single block, which is opened with the symbol “<” и закрывается символом “>" Select this block and remove it from the file;
Next you need to save the changes in a notepad file;
After this, copy the modified “sheepN.xml” file back to the archive in the folder from which you took it. If an information message appears confirming the replacement of the file, confirm;
Close the archive and change the file extension back from “.zip” to “.xlsx”.

After this, you can simply open the file using Excel, and the password protection will be removed from the sheet, that is, it will be possible to perform all actions with the information from the document.