How to remove editing protection in excel. How to remove a password from an Excel file: several simple methods

An integral component of the office suite, Microsoft Excel has long, and deservedly, become the main tool in business areas. It is most actively used by accountants, estimators, mathematicians and businessmen.

Many people, in order to protect their calculations from strangers, put protection on an Excel workbook, or protect a separate sheet. If this is information intended only for the owner, there are no problems. But more often it is intended for the work of company employees. The file is sent by mail, forgetting to remove the protection, or without warning the recipient about the existence of such a sheet in the file.

Working with Excel 2003

The task is to add a list to a page. When you try to enter the next sequence number, you are faced with the message: The cell or chart is protected from changes.

Excel itself tells you the action plan. We follow the path: Tools → Protection → Unprotect the sheet.

Working with Excel 2007, 2010

In this version of Excel, a different menu is used instead of “Tools”: “Review” → “Unprotect sheet” (“Changes” group).

Important! The “Unprotect sheet” option is active if it is protected. In the case of 2007 or 2010, the workbook has been shared, which needs to be canceled.

Resetting a protected sheet password

If you have sent an Excel file in which the sheet is password protected and the owner is unavailable or, simply put, has forgotten the password. The user's only task is to reset the password in order to be able to work with this page.

This is done:

1. Using add-ons.
2. Using an archiver and notepad.

We find and install a macro that performs our task.

Installing the add-on

1. Place the downloaded macro (add-in) in the Addins folder (default macro folder).
2. Go to Tools → add-ons → overview.
3. Select the desired file.
4. We install.

The macro is launched along the path from step 2. macro settings. We remove the protection by resetting the password for the active sheet.

After launching, it seems that the program has frozen, because the process takes about two minutes, after which we receive a message.

Using an archiver and notepad

Important! This method is only possible for files with the xlsx extension. Those. files created in versions later than 2003. Or you can resave the file in a newer version.

Procedure:

1. Open with an existing archiver, for example, WinRar.
2. Among the archive files there will be sheet1.xml. Open it with any text editor (for example, Notepad).
3. Find the text sheetProtection password.
4. Remove the password value (the one in quotes).
5. Or we use this value as the sheet protection password.

If the advice in the article did not help you or you have a question about Windows or Microsoft products, write to us. Together we will try to find the answer.

Any user of 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 is suitable if you do not want to resort to the help of third-party programs that allow 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, you can also remove the password from an Excel document using special utilities that greatly simplify this task. In our example, we will turn to the help of the Accent OFFICE Password Recovery utility.

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

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 desire. 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. For 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. For 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 the cells, as well as remove the protection against unwanted access or amendments in whole or in part. These are the simplest ways to restrict rights that can be configured in Excel itself.

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 (to 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.

When we share our Excel data with other people, we may need to protect the data in the sheets from being changed or edited.

In this article we will look at how to protect an Excel sheet from changes and editing, as well as how to remove the protection.

Data Protection Basics in Excel

Sheet protection in Excel does not allow you to reliably restrict access to the data in your files or sheets. This is due to the fact that Excel uses a simple password encoding algorithm that is easy to crack. Older versions of Excel (earlier versions lower than Excel 2010) use such a simple coding algorithm that anyone who understands VBA can hack it. Versions of Excel 2013 and 2016 use a more advanced encryption algorithm, but there are also simple approaches to cracking it.

Protecting Excel sheets or files is most needed to limit unwanted changes and editing of data or formulas. I do not recommend storing and forwarding sensitive information in Excel spreadsheets.

In order to minimize the risk of unwanted changes to your files or sheets, adhere to the following rules:

• Use the latest versions of Excel - 2013 or 2016;
• Use strong passwords to protect sheets;
• Protect the file structure from changes to avoid editing, deleting or moving data;
• Use different passwords to open the Excel file and allow editing;

How to protect an Excel sheet

To protect a sheet in Excel, we will take the following steps:

• Right-click on the tab with the Excel sheet at the bottom of the page and select “Protect sheet...” . Or go to the tab “Review” on the toolbar and click the button “Protect Sheet” :

• In a pop-up window “Sheet protection” :
  • In field “Password to disable sheet protection” enter the password that is required to remove the protection. Write down your password in a safe place.;
  • If you do not want to protect your sheet with a password, but just want to warn users that editing data on the sheet is not welcome, then leave the password field empty;
  • In chapter “Allow all users of this sheet” let's set the settings for those actions that will be allowed to all users who do not have a password. By default, items are ticked “Highlighting locked cells” And “Selecting unlocked cells” . If you leave only these items, then users without a password will only be able to view and highlight locked and unlocked cells.
  • If you want to allow users who don't know their password to do things like: formatting cells, rows and columns, the ability to insert/delete columns and rows, add hyperlinks, sort data, use autofilters, build pivot tables and graphs , then tick these boxes:
  • After we click the “OK” button, the system will ask us to enter the password assigned to the sheet again.

How to remove protection from an Excel sheet knowing the password

If you know the password to the sheet, then removing the protection will not be difficult.

For this:

• Right-click on the Excel sheet tab and select “Remove sheet protection...” =>

• You can also remove protection from the sheet through the tab “Review” on the toolbar by clicking on the item “Unprotect sheet” => enter your password in the pop-up window and click “OK”.

How to unprotect a sheet in Excel if you forgot your password

As I wrote above, removing protection from a sheet if you don’t know the password is not the most difficult task. To remove password protection from an Excel sheet, use the methods described below.

Unprotect an Excel sheet without password using VBA code (for Excel 2010 and below)

If you are using Excel 2010 or lower, you can unprotect a worksheet without a password using a macro. If you are using Excel 2013 or 2016 version, then save the protected file in xls format (Excel 97-2003 workbook). Then do the following:

• Open the file and go to the password protected worksheet. It is important that the sheet is active; to do this, I recommend clicking on any cell on the protected sheet;
• Press the key combination Alt+F11, thereby calling up the menu Microsoft Visual Basic ;
• In the window that opens, find the section “Project – VBAProject” => right-click on the section item ( VBAProject (filename.xls ));
• Select the item from the drop-down menu "Insert" => "Module" ;

• In the window that opens, enter the code:

Sub PasswordBreaker() "Breaks worksheet password protection. Dim i As Integer, j As Integer, k As Integer Dim l As Integer, m As Integer, n As Integer Dim i1 As Integer, i2 As Integer, i3 As Integer Dim i4 As Integer , i5 As Integer, i6 As Integer On Error Resume Next 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: For n = 32 To 126 ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6 ) & Chr(n) If ActiveSheet.ProtectContents = False Then MsgBox "Password is " & Chr(i) & Chr(j) & _ Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _ Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) Exit Sub End If Next: Next: Next: Next: Next: Next Next: Next: Next : Next: Next: Next End Sub

• Press F5 or click "Run" to run the code. Excel will “freeze” for 3-5 minutes and then display a window with a password.

• This is not an actual password, but one that was generated by the system. Click “OK” - your sheet is unlocked!

If your file contains several protected sheets, use this macro for each sheet separately.

Unprotect an Excel sheet by copying data to another sheet

Another way to unprotect an Excel worksheet is to copy the contents of the protected worksheet to a new Excel worksheet. To do this, do the following:

• Open the Excel file with the protected worksheet;
• Go to the protected sheet and select the cell with the left mouse button A1 ;
• Press the keyboard shortcut Shift + Ctrl + End to highlight all content on a sheet;
• Create a new sheet in your Excel workbook ( Ctrl + N );
• Left-click on a cell A1 on a new sheet;
• Press the keyboard shortcut Ctrl+V and paste the copied data.

This method will work provided that the selection of locked and non-locked cells is allowed by the author of the file.