From time to time during operation it becomes necessary to set or remove a password on Excel file. In this article we will look at how this can be done in various Excel versions.
If you need to set a password in Excel, you can do this in a special dialog box. The location of this window in different versions Excel is different.
The password must be set after all changes to the file before saving it. To do this you need to go to the section Service→ Options
This will open the options dialog box Excel workbooks, you need to select a tab Safety where you can enter either password to open the fileExcel, or password to change it in the appropriate field
After entering the password, you must click OK, and a window will open in which you need to confirm the password you entered. This is necessary so that you can be sure that you entered the password correctly the first time and remember it. The changes will take effect after saving the file.
You can set or remove a password in Excel using the method discussed below.
Set or remove password in Excel 2007 and 2010
Setting a password in Excel 2010 is slightly different from 2003, and now we will look at how this can be done. You can set a password immediately before saving the file. To do this, select the dialog box to save the file: File→ Save As
After this, a window will open where you need to select the item Service→ Common parameters
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 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 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:
- Install in Explorer Windows opportunity display the extension of file names 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;
- Next, you need to go to Excel file renaming mode (click 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 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;
- 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;
- 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 it appears Announcement with confirmation of file replacement - confirm;
- 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.
In order to protect data both from unauthorized persons and from their own accidental actions, users can install protection on Excel documents. Alas, not everyone knows how to remove such protection in order to gain access to information, including the ability to edit it. And what should we do if the file was received from another user who forgot to give us the password, or we accidentally forgot (lost) it? Let's take a closer look.
Note that block Excel document There are two ways: protect a sheet or a book. Accordingly, this will determine what actions need to be taken to unlock it.
Removing protection from the sheet
A security password can be set not only for the entire document, but also for a specific sheet. In this case, the user will be able to see the contents of the sheet, but he will not be able to edit the information.
To remove sheet protection, perform the following steps.
Changing the file code to remove sheet protection
This method is needed in cases where the password was lost or was not transferred along with the file from another user. It only works for those documents that are protected at the level of individual sheets, and not the entire book, because we need to get to the menu "File", which is not possible when password protecting the entire document.
To remove protection, you must perform the following sequence of actions:
- Go straight to step 4 if the file extension is XLSX (Excel workbook). If the document format is XLS (Excel 97-2003 workbook), you must first resave it with the desired extension. To do this, go to the menu "File".
- Select from the list on the left "Save as", then on the right side of the window click the button "Review".
- In the window that appears, select any comfortable spot to save the file, set the format “Excel workbook” and click OK.
- Open in Conductor folder with document in XLS format X (newly saved or originally present). To enable file extensions, go to the tab "View", where we enable the desired option in the tool group “Show or hide”.
Note: The actions in this step and below in the operating system are described using Windows 10 as an example. - Right-click on the document and in the list that opens, click on the command "Rename"(or you can just press the key F2, after selecting the file).
- Instead of expansion "xlsx" we write "zip" and confirm the change.
- Now the system will identify the file as an archive, the contents of which can be opened double click left mouse button.
- In the folder that opens, go to the directory “xl”, then - “worksheets”. Here we see files in the format XML, which contain information about the sheets. You can open them using the usual Notepad.
Note: in Windows 10, you can set the default program by file type in the system settings (launched by pressing the keys Win+I), In chapter “Applications”, then - “Default apps” – "Choice standard applications for file types".
- After successfully opening the file, we need to find the phrase in its contents "sheetProtection". To do this, we will use the search, which can be launched either through the menu “Edit”(paragraph "Find"), and by pressing the key combination Ctrl+F.
- Enter the desired phrase and press the button “Find next”.
- Once you have found the desired match, you can close the search window.
- We erase the phrase and everything that relates to it (between the opening and closing tags).
- On the menu "File" select a team "Save as"(or press the keyboard shortcut Ctrl+Shift+S).
- It is not possible to save the document immediately in the archive. Therefore, we do this in any other place convenient for us on the computer, without changing the name and specifying the extension "xml"(file type must be selected - "All files").
- Copy the newly created file to the folder “worksheets” our archive (with replacement of the original one).
Note: record "sheetProtection" present in all password-protected sheet files. Therefore, we perform the steps described above to find and delete it with all other files XML in folder “worksheets”. - Again we go to the folder containing our archive and change the extension back from "zip" on "xlsx" by renaming.
- Now you can open the file and safely edit it. You will not need to enter a password to remove protection.
Third-party programs for removing passwords
To remove your password, you can use third-party programs. At the same time, it is worth remembering the possible risks associated with downloading, installing and using non-standard tools operating system And Excel programs.
If you nevertheless decide to take advantage of this opportunity, you can pay attention to a fairly popular program Accent OFFICE Password Recovery .
Link to official page with the program: https://passwordrecoverytools.com/office-password.asp.
Please note that to gain access to all functions of the program you need to purchase a license. A demo version is available to try out the application; however, it does not allow you to delete passwords.
Conclusion
Protecting a book or individual sheet is extremely useful features Excel programs when you need to protect information from unauthorized persons or, for example, protect yourself from accidental changes to important read-only data. But sometimes the opposite need arises - to withdraw earlier established protection. This can be done in different ways, depending on how it was installed. And even if you have forgotten the password, the lock can be removed, however, only if the code was set for individual sheets and not the entire book.
From time to time I receive files Excel pages which 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 if you were 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 protection from editing from Excel sheets using the Excel program itself. 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
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 added this very feature to all programs in the office suite. 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 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.
Loading...
