I forgot the password for the Excel file, what should I do? How to unprotect a sheet in Excel if you forgot your password. Tell your friends on social networks

Any user Microsoft Excel and other similar programs can easily set a password on an important spreadsheet document, which will prevent information from being viewed by persons for whom the contents of the document are not intended.

Unfortunately, quite often there are situations when a user sets a password, and then he himself cannot access the document for the simple reason that the password was completely forgotten. Fortunately, you have the opportunity to remove protection from the sheet.

Method 1: using standard Windows tools

Actually, this method suitable if you do not want to ask for help third party programs, allowing you to remove passwords from documents. It should be noted that in this case much more action will have to be taken.

Method 2: Using Accent OFFICE Password Recovery

And, of course, remove the password from Excel document it is possible with the help special utilities, which greatly simplify this task. In our example, we will turn to the help of the Accent OFFICE utility Password Recovery.

If you know other ways to remove a password from Microsoft document Excel, please tell us about them in the comments.

Excel program from Microsoft package Office 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.

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 Google service Tables. 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:

1. Install in Windows Explorer ability to display file name extensions in the folder where the Excel file with worksheet protection is located. To do this, in the explorer at the top, switch to the “View” tab and check the option;
   
2. 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”;
   
3. 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;
   
4. Next, you need to open the resulting “.zip” file using any archiver;
5. Inside the archive you can see several files and folders. Go to the “xl” folder;
   
6. Next, go to the “worksheets” folder;
   
7. 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;
   
8. After that, in Explorer, right-click on the file and select the “Open with” - “Notepad” option to switch to its editing mode;
   
9. The text of the file will open in Notepad, as well as its Technical information. 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;
   
10. 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 delete it from the file;
    
11. Next you need to save the changes in a notepad file;
    
12. 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;
    
13. Close the archive and change the file extension back from “.zip” to “.xlsx”.
    

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

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 both 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 programs Windows.

Method 1: Remove the password using an archiver

This method works best with older versions of the program (up to version 2010). IN latest versions There is 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 old Excel versions save the document 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 completely password-protected large file Excel with several sheets, you will have to remove protection from each sheet 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 the password from Excel tables no 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.

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 methods, 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

From time to time I receive Excel files whose pages are protected from changes. In this way, authors try to protect the document from changes. If you had a password, you could do as in the figure below.

But this article is for those who do not have a password, and it is very necessary to remove protection from the Excel sheet.

How to remove edit protection from an Excel worksheet using the OpenOffice office suite

This is the easiest way to remove protection. Just open the Excel file in the OpenOffice Calc spreadsheet editor and uncheck the “Tools -> Protect Document -> Sheet” menu. There is no need to enter any passwords, etc. Yes, yes - that's all!!! All that remains is to save the unlocked Excel file.

As I understand it, the OpenOffice developers did not sneeze at the protection that they came up with in MicroSoft. By the way, such manipulations with others free package LibreOffice will not bring the desired result. That is, when you try to unlock, you will be asked for a password, as in the case of using the original MicroSoft Office.

Unlocking using this method was carried out using OpenOffice version 4.1.2 dated October 28, 2015

How to remove editing protection from an Excel sheet using a MicroSoft Office macro

We will remove the editing protection from Excel sheets with the help of the same Excel programs. To do this, we will use the built-in macro support.

If you don't have a developer tab, enable it in the settings:

Then select the “Macros” item on the developer tab:

Enter the name of our macro and click the “Create” button:

In the window that appears

insert the following code:

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

You should end up with something like this:

Close the main window:

Click on the “Macros” button again on the developer tab

and in the window that appears, click the “Run” button

After a while you will receive a notification that everything is ready:

Unlocking using this method was carried out with using MicroSoft Office Excel 2010.

Unlocking using an archiver and text editor

This is a method for those who want to know how editing protection works from the inside.
Change the XLSX file extension to ZIP.

If you have a file in XLS format, then first save it as XLSX to use this method.

Open the file in an archiver, I'll use the free BandZip.

Unpack the required sheet from the Excel workbook using the path in the archive “xl -> worksheets”.
Open the resulting file, for example sheet1.xml in any text editor, you can even do it in a notepad.

Find a section in the text and remove it all the way to the closing bracket. Save your changes and move the file back to the archive.