Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Unlocking a Worksheet with an Unknown Password.

Unlocking a Worksheet with an Unknown Password

by Allen Wyatt
(last updated June 30, 2015)

32

Dennis has a travel expense worksheet that he has inherited; it has been passed down within his organization for years. Some of the cells in the worksheet need to be unlocked so that Dennis can correct the outdated formulas they contain. There is the problem, though: The worksheet is locked, and changing the formulas is not permitted because of the protection. Dennis wants to know how he can unprotect the worksheet so he can make the necessary changes.

Depending on the way that the worksheet was protected, you may be able to simply copy the worksheet contents to a new worksheet. Follow these steps:

  1. Create a new, blank worksheet. This can be in the current workbook or in a new workbook.
  2. Display the original worksheet that is protected. The first cell in the worksheet (A1) should be selected.
  3. Press Shift+Ctrl+End. Excel should select all the used cells in the worksheet. (If it doesn't, hold down the Shift key and press the arrow keys until you have selected all the worksheet cells.)
  4. Press Ctrl+C. This copies the selected cells to the Clipboard.
  5. Display the new, blank worksheet you created in step 1. Cell A1 should be selected.
  6. Press Ctrl+V. Excel pastes the contents of the Clipboard to the new worksheet.

The new worksheet can be changed in any way you need. If the old worksheet has links to other worksheets, you may need to copy those separately or establish those links manually. You will also need to adjust row heights and column widths, as necessary, to match the old worksheet.

If the above doesn't work, for some reason, you will need to actually try to crack the worksheet password. There are macros available, on the Internet, that will remove or identify any internal passwords, such as those used to protect a worksheet. If you do a search for "Excel password cracker" or something similar, you will find lots of candidates. The problem is that you'll also get lots of search results for programs that remove workbook passwords—something you don't need for this particular purpose.

Let me save you some trouble, however. There is a perfectly good internal password remover that is available at this page:

http://www.mcgimpsey.com/excel/removepwords.html

The page explains the macro (which is rather long) and even provides a download you can use, if desired.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2776) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Unlocking a Worksheet with an Unknown Password.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Stepping Through Head Formats

You can use the shortcuts described in this tip to quickly change the heading levels of the headings in your document. You'll ...

Discover More

Using Different Colors with Tracked Changes

When changes are made in a document with Track Changes turned on, each author's changes are normally shown in a different ...

Discover More

Accessing Old Excel Data

If you have some legacy data stored in very old spreadsheet files, you may be wondering how to get that data into an Excel ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (menu)

Protecting Individual Worksheets, by User

Excel allows you to protect worksheets, individually. Users can still see that the worksheets are there, but they cannot ...

Discover More

Using a Protected Worksheet

If you have a worksheet protected, it may not be immediately evident that it really is protected. This tip explains some of ...

Discover More

Preventing Someone from Recreating a Protected Worksheet

When you share a protected workbook with other people, you may not want them to get around the protection by creating a new ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 + 0?

2017-08-30 02:15:23

romantrump

You can free download Atom TechSoft Excel Password unlocker software that is easily recover lost Excel file password and unlock excel file without any problems....


2017-03-01 07:38:23

Yanis

Haha Unlocking? By copy/paste and rebuild all connections? Joke.


2016-06-17 16:36:19

Lorrie White

I could have thought this up myself. The worksheet in question is the first of 52 worksheets in the workbook. All worksheets are protected with the same password. The first worksheet includes formulas that pickup ending figures in an entirely different workbook. Someone has screwed up along the way and put the wrong password in this first worksheet. We are now through 25 or 26 weeks and need to get the figure in week one corrected. So it is not as simple as copying and pasting into a new workbook. Can any one help!


2016-04-27 03:48:03

Cristi

It works! Thank you!


2016-04-09 05:02:26

Petros

@Sandip - Get this:

http://www.spreadsheet1.com/password-protection-remover-free-office-excel-add-in.html


2016-04-09 00:37:39

Sandip

This doesn't work on Office 2013 or 2016 when sheet is protected using same version of office. Office 2010 or lower have weak protection but not such macros are not working or it will take month or years to do this task. Do you have any solution for 2013?


2016-03-22 23:59:27

Senny Joscar

Unlock worksheet by removing password security with the help of advance Excel password unlocker software offered by eSoftTools.


2016-03-13 01:47:30

Senny Joscar

Excel password recovery software is offered by eSoftTools to successfully unlock Excel file against any type of password security over Excel sheet.

Read More
http://recoverexcelsheetpassword.blogspot.in/


2015-11-15 01:50:16

ajith

Sir,
I forgot the password a excell workbook created in 2003 version. so please assist to open the same.


2015-10-27 05:05:38

Petros

abhijeet

You code won't work in Excel 2013 & 2016. You need this addin:

http://www.spreadsheet1.com/password-protection-remover-free-office-excel-add-in.html


2015-10-26 08:29:00

abhijeet

I have all files all sheets password break macro but i want run this macro for particular sheets so please tell me how to do this

Sub unprotect()
Dim ws As Worksheet, strWB As Workbook
Dim myPath As String, sFile As String

Application.ScreenUpdating = False
'Put this macro in folder remove that folder all files
'myPath = Application.ThisWorkbook.Path & ""
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
myPath = .SelectedItems(1) & ""
End With

sFile = Dir(myPath & "*.xl??")
If sFile = "" Then
MsgBox "No CAF_File found!!!", vbCritical
Exit Sub
End If

Do While sFile <> ""
Set strWB = Workbooks.Open(myPath & sFile)
'Run new macro
Call AllInternalPasswords
strWB.Close True
sFile = Dir
Loop
Set strWB = Nothing

Application.ScreenUpdating = True
End Sub
Public Sub AllInternalPasswords()

Dim w1 As Worksheet, w2 As Worksheet
Dim i As Integer, j As Integer, k As Integer, l As Integer
Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer
Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer
Dim PWord1 As String
Dim ShTag As Boolean, WinTag As Boolean

Application.ScreenUpdating = False
With ActiveWorkbook
WinTag = .ProtectStructure Or .ProtectWindows
End With
ShTag = False
For Each w1 In Worksheets
ShTag = ShTag Or w1.ProtectContents
Next w1
If Not ShTag And Not WinTag Then

Exit Sub
End If

If Not WinTag Then

Else
On Error Resume Next
Do 'dummy do loop
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
With ActiveWorkbook
.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 .ProtectStructure = False And _
.ProtectWindows = False Then
PWord1 = 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 Do 'Bypass all for...nexts
End If
End With
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
If WinTag And Not ShTag Then

Exit Sub
End If
On Error Resume Next
For Each w1 In Worksheets
'Attempt clearance with PWord1
w1.unprotect PWord1
Next w1
On Error GoTo 0
ShTag = False
For Each w1 In Worksheets
'Checks for all clear ShTag triggered to 1 if not.
ShTag = ShTag Or w1.ProtectContents
Next w1
If ShTag Then
For Each w1 In Worksheets
With w1
If .ProtectContents Then
On Error Resume Next
Do 'Dummy do loop
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
.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 Not .ProtectContents Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

For Each w2 In Worksheets
w2.unprotect PWord1
Next w2
Exit Do 'Bypass all for...nexts
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
End With
Next w1
End If
MsgBox "Done"
End Sub


2015-09-28 04:05:40

William Gendri

Thank you so much , for the information. the remove password works like a charm !


2015-09-25 01:05:03

Hans

What can I say other than THANK YOU.
also to McGimpsey who kindly provides this Excel Add-In.
I can't understand why WOT (Web-of-Trust) bans the site at McGimpsey.


2015-09-15 12:28:08

Colleen

I had a complicated Excel file come to me that needed some formatting changes and text updates. It was password protected, and I couldn't even select all the cells to be able to copy and paste. Fortunately, you came to my rescue!! Your macro worked perfectly. Saved me from hours of recreating the spreadsheet! Thank you.


2015-08-28 06:11:55

Michael

The provided link worked like promoted (even on MAC) and just saved me a lot of copy and past work! Thank you Allen for the post and McGimpsey for the macro.

Off to do some actual work now ;)


2015-07-18 10:09:35

mrstable

Help needed pls. I Hit Alt+F11 to view the Visual Basic Editor, I Hit Insert but no option was clickable including the module. so I can't hav access to where i wil paste the code. help pls...


2015-05-14 05:04:46

Petros

The VBA code won't work if the sheets are protected in Excel 2013 or 2016 preview

http://spreadsheet1.com/sheet-protection-2013.html


2015-05-13 17:56:45

Karicula

That was amazing - the VBA code worked perfectly the very first time to remove the password and took about 2 seconds.

http://www.mcgimpsey.com/excel/removepwords.html


2015-01-07 16:45:06

Dave Tufte

I'm seconding Donald's post from December 23 about allInternalPasswords no longer working.

I'm running Excel 2013 in Windows 8.1.

I am getting this error even when all macros are enabled.

It's a pity. I teach a spreadsheet engineering class for MBAs. Most of the students end up as the Excel jock where they work, and this macro has been part of the toolkit I pass on to them for many years.


2015-01-05 05:53:53

Gaya Prasad

Hi Dear,
How protect formula that not show ant not copy formula


2014-12-23 13:15:57

Donald

My apologies. I figured out my mistake in my execution of the macro. It worked GREAT!! Thanks!


2014-12-23 13:07:03

Donald

I downloaded the workbook. Opened up the file in the new workbook. Went to add ins and then selected the macro. I received the following message. "Cannot run the macro "allinternalpasswords.xls!Allinternalpasswords. The macro may not be available in this workbook or all macros may be disabled." What do you suggest?


2014-12-15 10:44:11

dave

When I CUT & PASTE from one worksheet to another the new, pasted version is numbers only, not formulae. This would be of no help in the present case of attempting to unlock formulae.

To copy formulae,on the original spread sheet I press CTRL~. (Tilde [~] is to the left of the number 1 at the top of the keyboard. Do CTRL~, not CTRL-SHIFT~.) This reveals formulae. COPY this spreadsheet. In the new workbook, PASTE SPECIAL/TEXT. That preserves the forumulae.


2014-12-15 00:49:26

venkat

Thanks lot, it works nice. Grate job done.


2014-11-27 05:02:54

Petros

http://www.thespreadsheetguru.com/password-recovery-help


2014-11-27 01:59:14

Farhan Naseem Siddiqui

Wow you are superb... ! Greatyyy


2014-07-03 05:34:44

Sarbabhowma.mondal

how to unlock excel password properly?
PLZ! PLZ! PLZ!


2013-09-30 10:42:30

Matt Cotton

It could have been a LITTLE less confusing:
Follow the link above, check.
Download the workbook, check.
Open the workbook you want to unprotect, check.
Go to "addins" and click on "remove passwords", check.
Click "okay" through the pop up boxes.
Presto, all protection and passwords are removed, and it even tells you what (the derived) passwords are.


2013-05-07 06:16:42

Makien

worked perfectly

Many Thanks


2013-04-07 12:26:16

Julie Gervais

Thank you for a macro that was simple to use and worked the first time. I had tried several other programs that did not deliver as promised. There was a protected hidden sheet in a personal workbook I use and I needed to remove it from the workbook. This was the most simple method and I wish I had found it the first time I tried to remove the protected sheet. I simply copy and pasted the macro into my workbook, removed unnecessary script and voila! it worked.


2012-10-24 12:30:38

Patrick

I had to use the hack and it worked. This is what happened. Running Office 2010. Have many spreadsheets opened. Opened a spreadsheet from customer (created in older version) with macros and protection, updated it and saved it. Closed out, the first sheet open after the protected one inherited the protection. Not the first time this has happened, nor does it happen all the time. I'll have to see if I can recreate it and post exactly when it happens. This was the fourth time, and the first time I found your site with the hack which worked. Thank you


2012-10-16 10:41:47

Karen Crouch

when you go into the new sheet you could also do Paste Special to paste the column widths so you don't need to manually go and change them all.
Unfortunately there is now row height paste :-(


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.