worksheet ask password

• Ablebits blog
• Excel macro

How to protect and unprotect Excel sheet with or without password

The tutorial shows how to protect Excel sheet with or without passwords. It also explains how to unlock Excel spreadsheet without password if you forgot or misplaced it.

When sharing your worksheets with other people or sending your Excel files outside of your organization, you may want to prevent other users from changing cells that shouldn't be changed, and also from moving or deleting important data in your worksheet or workbook.

Luckily, Excel provides a quick and easy way to lock a sheet or password protect an entire workbook to prevent accidental or deliberate changes in their contents.

What you need to know about Excel worksheet protection

Worksheet protection in Excel is not a security feature. It's is not actually intended to prevent people from accessing data in a password-protected worksheet. Why? Because Microsoft Excel uses a very simple encryption algorithm for worksheet protection. Even when you protect your Excel worksheet with a password, that password is very easy to break.

In older versions (below Microsoft Excel 2010), it doesn't even matter how long and complex your password is. Anyone who has at least some basic knowledge of VBA can crack it in minutes (here's an example of the VBA code that easily cracks passwords in Excel 2010 and lower).

The recent versions of Excel use more sophisticated methods of sheet protection, but they are also vulnerable and can be unlocked without knowing a password. In other words, if someone really wants to get access to a protected sheet, they will.

So, does this mean that Excel worksheet protection is absolutely pointless? Not at all! It makes sense to lock Excel sheets to avoid accidental changes in the sheet contents. A common example is Locking formula cells to prevent inadvertent erasure or modification of formulas.

The bottom line is - lock an Excel spreadsheet to prevent other users from accidentally tampering with your data, but never store sensitive information in your Excel sheets.

If you strive to ensure the maximum security protection for your Excel files, be sure to do all of the following:

• Use the modern versions of Excel with enhanced data encryption mechanisms.
• Protect your sheets with strong passwords that include different types of alpha numeric characters and special symbols. At that, try to make passwords as random as possible, for example by using our password generating tool .
• Protect the workbook structure to prevent other people from adding, moving, renaming, deleting or unhiding your sheets.
• For workbook-level security, encrypt the workbook with different passwords from opening and modifying .
• If possible, store your Excel files with sensitive information in a secure location, e.g. on an encrypted hard drive.

How to protect Excel sheet

To protect a sheet in Excel, perform the following steps.

• To password protect your Excel sheet , type a password in the corresponding field. Be sure to remember the password or store it in a safe location because you will need it later to unprotect the sheet.
• If the worksheet protection is nothing more than a precaution against accidental modification of the sheet contents by yourself or by the members of your local team, you may not want to bother about memorizing the password and leave the password field empty . In this case, unprotecting the sheet takes just a single click on the Unprotect Sheet button on the ribbon or in the sheet tab's right-click menu.
• By default, the following 2 options are selected: Select locked cells and Select unlocked cells . If you leave only these two options selected, the users of your sheet, including yourself, will be able only to select cells (both locked and unlocked).
• To allow some other actions such as sorting, filtering, formatting cells, deleting or inserting rows and columns, check the corresponding boxes.
• If you don't check any action, users will only be able to view the contents of your Excel sheet.
• Click the OK button.

• The Confirm Password dialog box will pop-up prompting you to retype the password so that an accidental misprint won't lock your Excel sheet forever. Retype the password and click OK. Done!

How to unprotect Excel sheet

As you have just seen, it's very easy to password protect a sheet in Excel. Unprotecting a spreadsheet is even easier, provided that you know the password. Though, unlocking an Excel spreadsheet without password is no big deal either :)

How to unprotect Excel sheet with password

If you know the password for the protected sheet, removing protection is a matter of seconds. Just do any of the following:

If you are dealing with a password-protected worksheet , Excel will ask you to enter the password. Type the password and click OK.

How to unprotect Excel sheet without password

As mentioned in the beginning of this tutorial, Excel worksheet protection is quite weak even in the recent versions of Excel. If you need to edit a password protected sheet but you cannot remember the password, try to unlock that Excel spreadsheet by using one of the following methods.

Unlock Excel spreadsheet with VBA code (for Excel 2010 and lower)

If you are using Excel 2010 or older, you can unlock a password-protected sheet with the following macro.

• Open your Excel document and switch to the password-protected sheet (it should be active when you run the macro).
• Press Alt + F11 to open the Visual Basic Editor.
• Right-click the workbook name on the left pane ( Project-VBAProject pane) and select Insert > Module from the context menu.
• Press F5 or click the Run button on the toolbar and wait a couple of minutes.
• The macro will report a cracked password, which is not the original one (always some combination of A's and B's), but nevertheless it works. You do not need to remember this fake password or enter it anywhere. Simply click OK and the sheet is unprotected!

Tips and notes:

• If your Excel file contains more than one protected sheet, run the macro for each sheet individually to unprotect it.
• To unprotect a sheet without password in Excel 365 - 2013 , you can save the document as Excel 97-2003 workbook (*.xls) first. Then, close Excel and reopen the workbook - it will be opened in compatibility mode. Run the macro to unprotect the worksheet, and then save the workbook back as an .xlsx file. Alternatively, you can use one of free tools, for example Password Protection Remover add-in for Excel . Or you can upload the protected Excel worksheet to your Google Drive, and then open it with Google Sheets.
• In case you are not very comfortable with VBA, you can find the detailed guidance in the following tutorial: How to insert and run VBA code in Excel .

Copy the protected sheet contents to another worksheet

Another way (or rather a workaround) to unlock an Excel sheet that was protected with password is to copy its entire contents to a new sheet. Here's how:

• Open the password-protected sheet.
• With the cursor positioned in cell A1, press Shift + Ctrl + End to select all used cells in the worksheet.
• Press Ctrl + C to copy the selected cells.
• Create a new sheet in the current workbook or in a different workbook (to create a new Excel file, simply press Ctrl + N ).
• In the new blank worksheet, place the cursor in A1 and press Ctrl + V to paste the contents of the password-protected sheet.
• This method works only if the Select locked cells and Select unlocked cells actions are allowed in the protected spreadsheet.
• If the password-protected worksheet has external links to other sheets or workbooks, you may need to re-create those links manually.
• You can use Paste Special to copy / paste the column widths so you won't need to adjust them in the new sheet manually.

How to protect a workbook in Excel

Apart from protecting the sheet contents, you may also want lock the entire workbook to prevent users from adding, moving, renaming or deleting worksheets, and viewing hidden sheets.

To protect the workbook, perform the following steps:

• Viewing hidden worksheets.
• Renaming, moving, hiding or deleting sheets.
• Inserting new sheets and chart sheets. However, users will be able to insert a chart in an existing worksheet.
• Moving or copying sheets to another workbook.

Note. The Windows option is not available (greyed out) in Excel 2013 and higher, as shown in the screenshot above. It is because Microsoft introduced the Single Document Interface in Excel 2013, where each workbook has its own top-level window that can be minimized, restored, and closed.

• In the Password box, type a password, and click the OK button. As indicated on the dialog window, a password is optional. However, if you do not enter a password, then anybody will be able to unlock your workbook with a button click.
• Retype the password in the Confirm Password window and click OK . That's it!

How to unlock a protected workbook in Excel

To remove the workbook protection, do the following:

• Type the password if prompted.
• Save the workbook.

Important note! Protecting the workbook structure should not be confused with workbook-level password encryption that can prevent other users from opening or/and modifying your workbook. If you are interested in this level of workbook protection, check out the next sections of this tutorial.

Password protect Excel workbook from opening

If you store your workbooks in a shared location such as a company local network, and you want to prevent other users from viewing your Excel files, you can encrypt your workbook with password that won't even let other people open it.

• Retype the password in the Reenter password box, and click OK .
• Save your workbook.

To remove the workbook encryption , repeat the above steps, delete the password from the Password box, click OK, and then save your workbook.

Tip. In addition to password-protection, you can secure your Excel files further by using the BitLocker Drive Encryption , which is available in the Ultimate and Enterprise editions of Windows Vista and Windows.

Password protect Excel workbook from modifying

If you don't mind other users opening your Excel file but you want to prevent them from making any changes to your workbook, perform the following steps.

• In your Excel workbook, click the Save As button, and then click the Tools button at the bottom of the window.

Tip. If you want to protect your Excel file both from modifying and opening, type the passwords in the Password to open and Password to modify boxes. It can be the same password of two different ones.

• Retype the password in the Confirm Password dialog, and click OK . If you've entered 2 passwords in the previous step, you will be asked to confirm both.
• Finally, click the Save button in the Save As dialog.

To remove the password protection , repeat the above steps, and delete the password(s) from the Password to open and Password to modify boxes. Then click the Save button to save your workbook.

I thank you for reading and hope to see you on our blog next week.

You may also be interested in

• How to hide and lock formulas in Excel
• Show and hide Excel gridlines
• How to insert comments in Excel
• How to access a corrupt .xls file in Excel 2010 - 365

Table of contents

390 comments

Hi, Englisch

Is it possible for several users to enter data but not to see the data of other users and for each user to have their own login.

Thank you for the corrected (non-syntax error) code. It worked. You saved me a lot of time.

it tooked a bit of time but it worked thankyou

i tried the VBa code - My excel is not responding after I ran the below 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)

Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next End Sub

Please help !!!!

Hi! You're asking beyond the advice this blog can provide. We don't help with creating VBA code.

please help me my excel sheet product not working my forget password

Hello, I need to make a cell for filterizing with protect and unprotect with password please help

wow that is great! I had a workbook from 2023 office 365, converted it to a 97-2004 .xls, then copy the code in the macro and the file was UNLOCKED!! Great job guys! Really WELL DONE! BIG THANK YOU

I have an Excel worksheet where I'm using the Group function to compress rows of data so that only the summary row are visible to the reviewer unless they click the "+" to see the details. I want to protect (lock) the summary rows so the data and formulas cannot be accidentally modified. However, I have tried to lock only these rows but when I do so, the Group option is also locked and the viewer (including myself) cannot access the compressed rows. Is there a way to lock only the desired rows but allow the Group option to still function?

Yesss, it worked for me with removing the "&" and replace with "&"

Thank you very much, I have been relieved of a big burden,once again, thank you so much

I forgot the password put on entire workbook for opening and modifying. Please help me how can I open my file.

my excel not responding when vba code running, please help

have tried torun the code and I get the error: compile error invalid outside procedure

I must be doing something wrong but can't figure out what

please guide, how can we prevent accidental deletion of a sheet/

You always see a warning before deleting a sheet.

Why is the compile error appearing in the VBA code

replace the "&" with "&" in the code

With newer versions of excel, is it possible to save them as older and still break the password with vba using the program shown? If not, what version/s does this become impossible?

Helo my problem is .. I use a password in excel file before 6 months.. but now not remember password... It's very important file .. plz help me... How I can Brock the lock this file...

using excel 2010 unable to unlock the file because the protected workbook option are hiding

Hi , thanks for your useful information , i script part : ActiveSheet.Unprotect it said Syntax Error ?!

Same problem, syntax error in both the downgraded version and the 2013 version.

Replace & with & and it works

Butchering this. Haven't played with html. Ok, replace & with &

you mean replace all "&amp" with just "&" ??

replace "&" with just "&"

&amp with just & no need (;)

did this: replaced &amp with just & no need (;)

still get error

Yes & with &

Sub PasswordBreaker() 'Breaks worksheet password protection.

Thank you so much!

It was amazing.

This was exactly was I was going to post until I saw someone notice that for web, the & is really "and" operator... Good call, good work, it worked like a charm!

It worked. thank you very much.

Thank you so much, it worked.

HI I have office 365 and I wanted to remove protection from xlsx file. I converted it to xls (97-2003 file format) and run the macro. Macro run successfully and it generated a password however the protection was still not removed and i am still unable to copy the data. Appreciate if you can help.

Just to add I am using Microsoft® Excel® for Microsoft 365 MSO (Version 2206 Build 16.0.15330.20246) 64-bit

Initially the process froze Excel and was non responsive. I read further and followed the instruction to save the document as 97-2003 (*.xls). Closed and re-openned the document and then restarted the process. It worked instantly! thank you!

Wow, it's works. Initially wasn't respond. After your comment. We need to close the file and re-open it again in order to work. A genius!

The macro froze my excel for 20 minutes, at 8gig ram i5 CPU. I have terminated the application, because I cant sit and wait so long.

Your unlock of excel sheet worked, thanks.

You're a genius! Thank you for sharing. It is a great help.

My excel workbook and sheets are protected and I can't unlock or unprotect what can I do? Please help

Post a comment

• Call +27 (0) 66 492 8062
• [email protected]

Excel asking for a password on an unprotected file

A recent question we had went as follows: “Every time I open my “Example “ excel sheet, I get messages asking for a password for a completely different document, and a links message. Why is Excel asking for a password on an unprotected file? How can I stop it from happening or asking it  without just clicking don’t update when I open it?

Table of contents

Youtube excel asks for password on unprotected file, find the excel files that your spreadsheet links to, break all the links instantly, finding all the cells that link to an external spreadsheet.

The issue here is that the password is on the file that Excel is trying to link to. Excel is being extra secure and making sure that you have the right password to also see the information from the file that your spreadsheet is linked to.

So if you open a spreadsheet that you know doesn’t have a password, yet you get a screen like this, it means that Excel is trying to link to and get information out of a spreadsheet that is password protected.

If it is purposeful then you need to get the password and work with it. But it it is annoying and you want to stop it happening you need to follow a few simple steps.

Firstly you need to find all the cells that link to this protected spreadsheet. Note in the picture above it tells you which spreadsheet it is looking for the password. You can use this or else see all the files that the spreadsheet is linked to.

With your spreadsheet open, click on DATA, then EDIT LINKS to see the screen below.

Note that it tells you all the names of the spreadsheets that your spreadsheet links to. This tool is very useful but for now we are only interested in the EXACT name of the spreadsheet. Write it down or remember all of it including the .xls, xlsx etc.

Now we need Excel to find any cell that references these spreadsheets. Because of the initial screen which asked for the password, we know that the problem one is called MarkModel v1.xls so this is what we will look for.

If you know for sure that the links are not important, you can just push the Break Links button shown above and all cells that contain that link will be replaced with values. Although this is a nice quick way to do this, you may be worried about which cells have the links as perhaps it needs to stay.

We can use the FIND tool for this. You can activate the FIND tool either by using the shortcut CTRL + F or under the HOME tab, on the far right under FIND & SELECT.

Note in the image below we have typed in the exact name of the file including the .xls (this just makes sure you don’t waste your time on other cells that contain similar names).

Note that we also clicked on the OPTIONS button to expand where we search and we have chosen WORKBOOK and FORMULAS. If you don’t choose WORKBOOK, Excel will only search the current sheet and you will need to repeat it on each sheet. By making sure it searched within formula we will only look for the links.

You can now either choose FIND ALL or FIND NEXT and work through the cells. You need to decide how important the cell is, whether it is used elsewhere and whether you can either break the link and keep the number or text, or delete it completely.

If you work through the cells and you are happy they all can be broken, use the step above to break links.

If you run the FIND and it says it can’t find any matching cells, then it means the link is not in a cell but in something else. You will need to look through some or all of these items to find the link:

• named ranges
• data validation
• conditional formatting

Excel slicer with password protection

Double click Excel cell to follow formula not working

How to find cells with external links in Excel

Protection for POPIA compliance

All content copyright AuditExcel 2024

• Privacy Policy
• Terms & Conditions

Change or remove workbook passwords

Workbook passwords can be changed or removed by anyone who knows the current password.

Caution:  Passwords in Excel for Mac and Word for Mac have a 15-character limit. You cannot open a workbook or document that has been password protected in the Windows-based version of Excel or Word if the password is longer than fifteen characters. If you want to open the file in Office for Mac, ask the Windows-based author to adjust the password length.

Open the workbook that you want to change or remove the password for.

On the Review tab, click Protect Sheet or Protect Workbook .

Click Unprotect Sheet or Protect Workbook and enter the password.

Clicking Unprotect Sheet automatically removes the password from the sheet.

To add a new password, click Protect Sheet or Protect Workbook , type and confirm the new password, and then click OK .

To remove a password from the workbook, click Protect Workbook , and then click OK without entering a new password.

Require a password to open or modify a workbook

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

How to Unprotect an Excel Worksheet or Workbook (With or Without Password)

Plus the difference between a Worksheet and Workbook

Do you want to make changes to your Excel workbook or worksheet, but find that they’re locked or protected? If so, you might need to unprotect them first. This will allow you to edit the content, structure, and formatting of your spreadsheet.

But how do you unprotect an Excel worksheet or workbook? And what happens if you don’t know the password? If you’re unsure how to proceed with unlocking your data in a protected Excel file, follow the steps below.

Unprotecting a Workbook vs. Unprotecting a Worksheet in Excel

Before we start, let’s clarify the difference between unprotecting a workbook and unprotecting a worksheet. A workbook is used to describe an entire Excel file. A worksheet , however, is used to describe the individual sheets in your Excel spreadsheet—the tabs of data you’ll see at the bottom of the window.

Unprotecting a workbook means that you can make structural changes to the workbook as a whole, such as adding, deleting, hiding, or renaming worksheets. You can also change the workbook properties and settings in your Excel spreadsheet by removing the protection from it.

In contrast, unprotecting a worksheet means that you can modify the data and formatting of the worksheet, such as changing the content of a cell, inserting or deleting rows and columns, applying or removing filters, and so on. Other worksheets might remain protected.

There are differences in the type of protection available. Encrypting an Excel file prevents anyone from viewing it without knowing the password. If you simply protect the workbook or worksheets, you’ll still be able to read the data and open the file, but you may need additional passwords to make changes or remove that protection.

How to Remove Excel File Encryption

The first barrier to unprotecting an Excel worksheet or workbook is to remove encryption from the file. Encryption prevents you from opening modern Excel files without first knowing the encryption password.

To do this, you’ll need to have the password for the workbook that was used to encrypt the file. If you don’t have it, you’ll need to try a third-party Excel password removal tool to unlock your file. Make sure to back up your file before you try this.

To remove Excel file encryption, follow these steps.

• Open your encrypted Excel workbook. When prompted, insert the correct password.
• Next, press File > Info .
• Select the Protect Workbook icon.
• From the drop-down menu, select the Encrypt with password option.

• In the Encrypt Document pop-up box, remove the existing password and press OK .

• If the password was accepted, press Save to save the file without the password.

Once you’ve removed file encryption, you should be able to unlock the workbook or individual sheets from the Data menu.

How to Unprotect an Excel Workbook

An Excel workbook can be protected with or without password protection. If you want to unprotect an Excel workbook, follow these steps.

• Open the workbook that you want to unprotect with Microsoft Excel.
• In the Review tab, press Protect Workbook in the Protect section to change its status. If the workbook is currently protected, the icon will appear in an active state with a gray background and border.

• If the workbook doesn’t have a password, pressing the Protect Workbook button will change its status immediately—the icon’s status will change to match other icons on the ribbon bar.
• If the workbook is protected with a password, you’ll see a pop-up box asking you to enter the workbook password. Type it in and click OK to remove it, then type it in again to confirm. This will allow you to make structural changes to the workbook.

As with encrypted Excel files, if you don’t know the password, you’ll have to use a third-party password recovery tool to unlock a password-protected Excel spreadsheet file. If you plan on doing this, make sure you save a copy of your file first, just to protect yourself from potential data loss or corruption.

How to Unprotect an Excel Worksheet

You can also protect individual Excel worksheets from being created, edited, or deleted. This includes the ability to protect them with or without passwords.

It’s best to make these changes in an unprotected workbook. Otherwise, you may find it difficult to unlock certain functionality (such as the ability to add or delete worksheets). However, even in a locked workbook, you may still be able to remove sheet protection, but only if the XLSX file isn’t encrypted.

If you want to unprotect an Excel worksheet, follow these steps.

• Open the workbook that contains the worksheet that you want to unlock.
• From the Review tab, press the Unprotect Sheet button in the Protect section.

• If the worksheet doesn’t have a password, the Unprotect Sheet icon will immediately change its name to Protect Sheet . This confirms that the sheet is unprotected.

• If the worksheet is protected with a password, you’ll be prompted to insert the worksheet password. If you know the password, type it and press OK . If it’s accepted, the worksheet will be unprotected and you can begin to modify it.

As with other methods, unlocking the sheet without knowing the password is tricky. You’ll need to try and source the password from somebody else or use a third-party tool like Passware Excel key to remove it for you.

Protecting Your Excel Data

Unprotecting an Excel worksheet or workbook will allow you to make changes to your spreadsheet. As long as you have the password, the process is simple. Without it, you’ll need to try a third-party tool to remove the password instead, unless you can source it from the original file owner or creator.

Looking for easier ways to lock down some of your Excel worksheets? You can lock cells in Excel to make it harder for others to make changes to certain cell ranges. You could also try sharing an Excel file as read-only to limit unnecessary changes.

Ben Stockton is a freelance technology writer based in the United Kingdom. In a past life, Ben was a college lecturer in the UK, training teens and adults. Since leaving the classroom, Ben has taken his teaching experience and applied it to writing tech how-to guides and tutorials, specialising in Linux, Windows, and Android. He has a degree in History and a postgraduate qualification in Computing. Read Ben's Full Bio

Read More Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *

How to Remove Password From Excel Sheet and File (2024)

While sharing your workbooks with people outside your team (when you don’t want the recipients to edit them) you might want to protect them against any changes.

Excel allows easy ways how you can password-protect your workbooks or worksheets within a workbook 🛅

But once you’ve locked a workbook or if you receive a locked workbook, how can you unlock it again? One way might be to run the password it requires to open it. And what if you don’t know the password?

Not to worry – You can still unlock it.

In this guide below, I will explain how you can unlock Excel workbooks and worksheets with and without passwords. This is going to be an interesting one so jump right in 🚀

Table of Contents

Remove a password from an Excel file (password known)

Remove password from Excel sheet (password known)

Remove password from excel sheet (password unknown), that’s it – now what, how to remove a password from an excel file.

Have you password-protected your Excel workbook? Do you want to remove the password from your workbook now?

Let me show you how you can do that 👀

• Launch the password-protected workbook.
• Go to the Review Tab > Protect Workbook button.

If the workbook is already password-protected, this button would already be enabled (probably grey).

Upon clicking on the Protect Workbook button, the Unprotect Window dialog box will launch as follows:

• Enter the Excel password for the workbook (that you used while protecting the workbook).
• Click Okay.
• Save the workbook by pressing the Control key + S.

Try relaunching the same workbook and this time, you won’t need a password to launch it. The password is removed 🔐

If you know the password to a protected Excel sheet, removing it is a piece of cake 🍰

• Go to the Sheet tab.
• Right-click on the relevant sheet > Unprotect Sheet.
• Enter the password for the sheet in the resulting dialog box.
• Click Okay and that’s it.
• Another way how you can do this is by going to the Review Tab > Unprotect Sheet.
• Enter the password for the sheet and your sheet would be unlocked.
• You can also do so by going to the Home Tab > Format.
• From the context menu, select Unprotect Sheet.

You’ll be again prompted to the Enter password dialog box. Once the password is entered, the worksheet will be unlocked 😎

This section is going to be the most interesting. You’d often protect certain sheets from your workbook using passwords.

But what if you forget this password sometime later? Don’t worry – you can still access the sheet 📝

If you are subscribed to Excel 2013 or newer versions of Excel, follow these steps to remove the password from a protected sheet when you don’t know the password to it.

• Go to the password-protected sheet.
• Select cell A1 and press the Control Key + A to select the entire sheet.
• Launch another new sheet.
• Select Cell A1 of the new sheet and press the Control key + V to paste the contents of the locked sheet.

Use the Paste Special option ‘Keep Source Formatting’ to make sure everything sits in place when copied/pasted.

All the contents of the password-protected sheet will be copied to a new sheet (an unlocked sheet) 💪

So technically, you haven’t removed the password from a protected sheet. But we have created another copy of the same sheet.

You can only copy the contents of a locked Excel sheet if it allows you to:

• Select Locked Cells
• Select Unlocked Cells

If you’re subscribed to Excel 2010 or older versions of Excel, you can remove the password from a password-protected sheet by running a macro. Here’s how to do it 🙈

• Press the Alt Key + F11 to launch the Visual Basic Editor .
• In the VBA editor, go to the Insert tab > Module.

You will see the following window appear.

• Copy and Paste the following code into this window.
• Press the F5 key to run the Macro.
• It will generate a dupe password that you can use to unprotect a sheet.

Easy, peasy 🍋

Feeling like a low-key hacker? That’s justified.

Microsoft Excel offers satisfactory, if not excellent, protection features to protect its workbooks and worksheets against undesired changes.

Not only protection features, but this giant spreadsheet software offers so many more data processing tools and functions too 🔮

To list some of them, the VLOOKUP, SUMIF, and IF functions of Excel come on top.

You are missing out on something super big if you don’t know how to run these very basic functions of Excel. Click here and enroll in my 30-minute free Excel course now.

It is designed to teach you all these functions (and much more) about Excel.

Other resources

Do you know how to lock certain cells in Excel? Learn it in our blog here.

Professor Excel

Let's excel in Excel

Passwords in Excel: Everything about Locking and Unlocking Workbooks and Sheets

Excel is one of the most popular applications in the business life. No wonder, that there is lots of sensitive data saved in Excel workbooks and sheets. Excel provides methods to secure this data: By protecting them with passwords. But Excel also offers much more than just using passwords on complete workbooks: You can also protect contents on single sheets, areas on worksheets and single cells. In this article, you’ll learn all this: From protecting entire workbooks to single cells. Even better: You’ll learn how to unprotect them as far as possible.

Introduction

Before we jump right in, let’s take a look at the different levels of protection in Excel first.

There are three levels:

• File level: The whole file is locked. There are different variations here, but the most important one is the encryption of the file. This protection is comparatively strong  (it of course also depends on your password).
• Workbook level: Protection on workbook level let’s you open the workbook. But you can’t change the structure.
• Worksheet level: You can protect single worksheets in Excel or just some ranges.

Please note, that number two and three (workbook- and worksheet level) are rather weak and can often be bypassed.

How to set passwords for complete Excel workbooks

Excel offers to use password protection on your entire workbook. But we have to differentiate: Do you want to stop people to open the workbook or only to modify it (e.g. open as “Read-only”). Excel offers both options.

Passwords to open Excel workbooks (=file level)

The fastest way to set passwords to the entire workbooks so that people can’t open the files without them.

• For the first way just click on File and navigate to “Info” on the left side.
• The first button says “Protect Workbook”. Click on it and then on “Encrypt with Passwords”.
• Next enter your password.

Thats’ it.

Passwords to modify Excel workbooks (e.g. open read-only is allowed)

You also got the option to let people open your workbook but don’t modify the contents. You can do this via the “Save As” dialogue box. Therefore, press F12 on the keyboard.

On the bottom of the “Save As” window, you got the “Save” button. On the left side of it is a button “Tools”. When you click on it, you will see 4 options, one of them is “General Options”. Click on it.

Now you will see a small new window like the screenshot on the left side. You can either set up a password to open the workbook or to modify it (or both, of course). Once you click enter, you have to enter the passwords again in order to confirm them.

How to protect the workbook structure (=workbook level)

If you don’t want other users to mess with your workbook structure, you can also protect it. That way, your co-workers or clients can’t

• view hidden worksheets,
• add worksheets,
• move worksheets,
• delete sheets,
• hide worksheets,
• rename worksheets.

For protecting the worksheet structure, go to Review and click on “Protect Workbook”. Choose a password or leave the password field blank. If you don’t enter a password, the workbook is still protected, but it can be removed easily by just clicking the “Protect Workbook” button again.

Do you want to boost your productivity in Excel ?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!

How to protect worksheets (=worksheet level)

Protecting single worksheets is quite simple. Go to the sheet you want to protect. On the “Review” ribbon, click on “Protect Sheet”.

Now you can select the detailed rights of users. E.g. should the user be allowed to select cell, to format cell, insert cells and so on.

Confirm by clicking OK. The button “Protect Sheet” on the “Review” ribbon should now be called “Unprotect Sheet”.

Please note:

• If you’ve before defined ranges on the worksheet you users are allowed to modify, users will still be able to edit those cells.
• You don’t have to require a password. You can use this function also without a password. But please keep in mind, that users can easily unprotect the sheet by clicking on the “Unprotect Sheet” button.

How to protect areas on worksheets (=worksheet level)

Let’s say, you’ve created a beautiful form. Now other people (co-workers, clients) have to fill it out. But you want to prevent, that these people change anything else than the dedicated fields.

As an example, you got a simple form and users are only allowed to change cell C3. This example is shown in the picture on the left side.

There are two steps:

• Define the areas which should be protected. Therefore, click on “Allow Users to Edit Ranges” on the Review ribbon. Next click on new. Now you can define which ranges the user is allowed to edit. The rest of the worksheet will be protected.After selecting the cell or cell range, you can also choose a range password. This password can be different than the general worksheet password. If the user enters this password, he can edit the cell.
• Protect the sheet. Either on the same window (left bottom corner) or click on “Protect Sheet” on the Review ribbon. Only after activating the sheet protection,

Strategies to remove password protection

Password protection for entire workbooks.

Please note: We don’t actively maintain Password Manager any longer. If you are still interested in it, please contact us .

Unfortunately (or luckily, depends on your situation) the encryption of whole files in Office is very strong. Therefore, there are no tools available for hacking an encrypted workbook. But there are some advice on how to open a password protected workbook:

• Trial and error: Try your recent passwords. Maybe you got a hit.
• Passwords are case-sensitive. That means, small and capital letters matter. So maybe you’ve switched on caps lock?
• Especially for multilingual users: Is it possible, that your keyboard input language has (automatically) changed? Next to the clock in the right bottom corner of your screen, what keyboard language does it show?
• Use a password manager. We offer a password manager, called Professor Excel Password Manager. It saves your password when you open the workbook for the first time. You can also look up passwords or export them. It saves your passwords locally (with a light encryption) and won’t be transferred over the internet. Please be aware, that anyone with access to your computer could look up your passwords.

Password protection for ranges or worksheets

There is a VBA macro for unprotecting Excel worksheets available on the internet. It’s quite short but unfortunately seems only to work until Excel 2010.

Credit (with some minor changes) and for more information, please refer to this website .

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

To enable cells to be edited while leaving o 00004000 nly some cells locked, you can unlock all the cells and then lock only specific cells and ranges before you protect the worksheet.

Once you encrypt an Excel file, no one else will be able to open it. This is the most common and recommended technique to lock an Excel file.

Leave a comment Cancel reply

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

Privacy Overview