Protect excel file with password for editing. How to password protect an Excel file and remove protection

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 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 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 top menu select the file, 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 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 quick search by content using keyboard shortcuts Ctrl+F. Find required block having 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 do this procedure with a sheet that was saved in the program version 2010 and 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 separate sheets, so if you have a completely password-protected big 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 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.

The issue of data protection is particularly relevant today. The network is full of attackers who can easily take over unprotected information. In addition, there are often cases when the user wants to install protection simply from prying eyes, for example, if several people use the same computer. In general, now we will not find out the motive for such a desire, but rather talk about how to password protect an excel file. In this article, Excel versions 2007 and 2010 were targeted, because the principle of setting a password for them will be identical. So, I suggest that everyone who wants to install protection on an Excel document use one of two methods. Which ones? Find out further!

Method one

In fact, both methods are extremely simple and equally functional, so it doesn’t matter which one you choose. It's also simple. So, let's start with the algorithm for performing the first method:

1. Let's imagine that required document ready, all that remains is to password protect it. To do this, click big button"Office" in the upper left corner of the program.
2. In the opened context menu Click "Save As".
3. A new window will open, your task is to pay attention to the small “Service” button located next to “Save” and “Cancel”. Click on it and a small menu will appear, select the line “General settings”.
4. Next, you can set passwords for two cases: for opening a document and changing it. Enter the desired code and click “OK”. From now on, every time a document is opened, each user will be prompted to enter set password. It’s better to write down your passwords somewhere, otherwise, if you forget, you’ll have to rack your brains.

Method two

In addition to the above, I can offer one more option for setting a password on an Excel document.

By following these simple rules, you can install protection on any file in no time Excel programs. I note that this function is based on 128-bit AES encryption, which is considered standard method file protection.

Video to help

Very often people are asked how to set a password on Microsoft document Excel 2007/2010 so that the document is available to other users only in read mode. Today I will explain how to install protection against changing an Excel file.
This information is intended primarily for beginners, so there is a lot of a large number screenshots and detailed description actions that ordinary user may seem to require no comment at all. Please be understanding.
1. And so, we have an open xlsx document with the name Book 1. Go to the tab File, as shown in Figure 1.
Figure 1.
2. Here we select the item Save as, outlined in red.

Figure 2.
3. The Save Document dialog box appears. At the bottom of the window, click the button Service, marked with a red marker in Figure 3.

Figure 3.
4. After this, a pop-up menu will appear. In it you need to select the item General Settings.

Figure 4.
5. We see a window general parameters, where you can set a password both for opening the file and for changing it. In our case, we only need to set a password to change it, so in the field Password to change enter the password.

Figure 5.
6. Next, press the button OK.

Figure 6.
7. In the password confirmation request window, enter our password again, then press the button OK.
Figure 7.
8. The system returns us to dialog box save the file where we press the button Save circled in the figure below.

Figure 8.
9. And so, we have an xlsx document that is protected by a password for modification. Let's try to open it.

Figure 9.
10. When you try to open a file, you will be asked for a password full access. In addition, as we see in Figure 10, we can open it in reading mode.

Figure 10.
11. In the window title Microsoft Excel it will be visible that the document is opened in limited mode.

Figure 11.

If you work a lot in Excel and want to protect your documents, then you should consider protecting your files with a password.

Whether you want to restrict access to an entire document or simply prevent certain users from making changes to a file, setting up a password only takes a minute or two. If your spreadsheet falls into the wrong hands, the results can be quite disastrous, so it's worth taking the time to avoid unnecessary problems in the future.

How to protect a password from an Excel file?

Excel simplifies your spreadsheet by locking it with a password. It's just three simple steps:

1. Go to menu File > Details > Books > Encrypt with Password.
2. Enter your desired password and click OK.
3. Confirm your password.

However, depending on what you are trying to protect, the method you use may vary slightly.

Encrypt an Excel document

The most common method of providing electronic security Excel spreadsheet is encryption. This means that whenever someone opens a file, they must enter a password.

Open a document and select File > Details > Protect Workbook.

From the drop down menu select Encrypt with password, then enter the required password in the next dialog box.

After clicking OK in the next window you will need to re-enter your password.

The next time you open the document, you will be prompted to enter a password to continue.

Allow read-only access

Setting up basic encryption is fairly simple, but Excel actually offers a variety of options when it comes to password protection. For example, it is possible that someone can open a document, but editing it will not be available. Such a system is very easy to implement.

In the drop down menu select General Settings.

Here, you noticed, there are two different fields: Password to open And Password to change. You can leave the last one blank if you want to limit editing rights without restricting access to the document itself.

The next time you open the document, you should receive a slightly different password prompt, as you can see above.

Worksheet protection

If your document contains more than one sheet, you can limit editing rights to one or more sheets, but not the entire workbook.

Go to the tab Review and press Protect sheet.

Here you can enter the desired password for the current sheet.

However, you will notice that there are also a series of tags that will allow all users to access certain features. This can be handy if you want to give them the ability to sort the columns but not make any significant changes.

Adding a Password Using VBA

Last method: your spreadsheet uses VBA macros and you want to allow someone to edit the document itself, but you also need to add additional protection to the written code. Here's how to do it.

Tab Developer > Macros.

Create a new macro. You can choose any name.

This article uses the following code:

Range("A1").Value = "Password Protected" !}

However, in practice, you simply use whatever macro you need to protect. In the editor Visual Basic go to Tools > VBAProject Properties.

In the tab Protection you can check a box that will allow you to lock the project for viewing Lock project for viewing. There are also fields where you can add password protection to access project properties.

It's worth noting that while such protection can be useful, it has its limitations.

Protect yourself

When it comes to an important table, it's better to be safe than sorry.

In many cases, workplaces use general access To Excel document to perform various tasks. There may be undesirable consequences if the wrong users received the file.

Yes, it will take some time to enter the password, but it's worth it. Take the time to consider whether you really need to protect the entire document or just certain sheets to avoid sad consequences.

Leave your comment!

From time to time during work there is a need to set or remove a password for an Excel file. In this article we will look at how this can be done in different versions Excel.

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 Excel versions 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 file Excel, 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 → General Settings