work sheet protection is great for
stopping people messing up your
spreadsheet but let's face it it creates
a pretty poor user experience in some
cases wouldn't it be great you could
just get rid of all that protection do
what you need to do and then put it back
on afterwards and no one's any the wiser
don't need to know the password or
anything well that is what I'm going to
show you how to do in this video
[Music]
I'm gonna give you three ways you can
remove the protection from an excel
sheet when you don't know the password
first one's a bit of a long shot but
it's worth a go second one uses VBA code
and the third one we're gonna be
actually hacking the file itself but it
is foolproof
so let's get on with it right
first method bit of a long shot so let's
say we've got this spreadsheet here I've
been given this and you can see you try
and change anything you've got a message
coming up saying it's protected can't do
anything so it's clearly protected at a
moment the first thing is is it look up
here is it an Excel SM file in other
words does it have macros because if it
does it's always worth checking the code
to see if the passwords been given away
push all f11 you go into the VBA editor
and in here there's likely to be find
your file so protect a cheap one in this
case go to one of the modules there
might be several modules you have to
check each one independently and what
you're looking for there's a ton of code
in here but if I just push ctrl F to
search and I'm going to search for
protect ok fine next and there you go
straight away we found a piece of code
that says and this is exactly what
you're looking for active she don't
unprotect that might not be active she
might be something else done per day and
there you go afterwards will be the
password and this is a total schoolboy
error that I've seen happen time and
time again and people write VBA code
which needs to alter protected sheets so
they just simply code in the removal of
the password and I suspect if I did
another search I'll find the password
being put back on at the end of the code
happens all the time if you find it's
fantastic you've now got a password and
you can just type it straight in and
unlock the shape so if that applies
you're on tour with
right Schumi it's not an Excel macro
workbook
you're gonna need method two so if you
can't find a pass work within the VBA
code or there isn't any VBA code then
you can actually create some VBA code to
crack the password and that's what we're
going to do here in method two now just
a quick warning on this this will just
remove the protection completely and you
will have no way of putting the
protection back again with the same a
password I've given you the code the
basic code in the description so you
need to copy and paste that code so I
just pulled up on screen on notepad so
I'm just going to do control a to select
it all control C to copy it go back to
this spreadsheet hit f11 I'll bring up
the VBA editor now you can just put this
code anywhere but I could put it on the
actual sheet that I'm trying to
unprotect that way I just you know
simple place to put it so you double
click on that and the project Explorer
and paste it in what this does is a
brute force cracker it's gonna go
through every iteration and possible
passwords
I just tried on protect the sheep and if
it's unsuccessful that would normally
generate an error but this line of code
here is gonna mean at that it was gonna
ignore all the errors it works on the
active sheet so you need to make sure
the sheep that you're trying to on
protect is the active shape by the
active sheet I mean it's just
highlighted it's the main one available
in excel at the moment so leave this
okay so I'm going to go back to this
code now I just run this it quite often
crashes the machine right so you try it
but make sure you've got a copy of your
this budget before we do that and
anything else you're working on because
it can cause some kind of heavy crash of
the computer but what I'm going to do is
show you a quick method that will
prevent the crashes or certainly prevent
them in my book and that is go through
and you can click in the margins of the
VBA code
stops and that will just stop there code
running each time it hits what you need
to do is you click anywhere in the code
and hit f5 to run it and you need to do
this several times because it's just
going to hit these stops all the time
and then as soon as it gets to the last
one it's gonna run that code and this is
going to give you an idea of how long
it's going to take so when the first
ones run you can unclick it and hit f5
again I now run it fake defective is
running that league twice now
yeah and now that's finished and um
click that one run it again and what
you're looking for is a message it's
going to come up to say when it's
complete and when it when it's finished
so I'm just going to run through this
myself and I'll get back to you when I
get that okay after an amount of time
you should get a message on screen like
this saying a complete and when I say
after some time this has just taken me
nearly two hours and it's going to
depend on what's in the spreadsheet to
be honest because when I was running
this on a blank workbook yesterday with
a very simple four letter password it
was only actually taking about five
minutes to run the whole thing but in
the real world I guess you're gonna have
more complex spreadsheets you're gonna
have more complex passwords so yeah it
could take a while so you might want to
kind of run it overnight or something
like that I click okay
obviously to jump back into the VBA but
if I go back to here you can see that we
should be able to do what we want it's
all basically unprotected you can do
what you like
so that's method to remove the password
using VBA
[Music]
okay now for the most robust method
there is offering moving passwords from
Excel files and also the ability to put
them back again so we need to come out
of Excel all certainly out of that file
that was protected sheet free I was just
okay and what you need to do is change
the file extension of the file to a zip
file okay so you've got a warning about
changing that which you can ignore okay
yes now if you can't see file extensions
you need to change a windows explorer
setting I'll just show you that in a
minute if you right-click on that zip
file and you open it with Windows
Explorer so if you can't see settings by
the way if you can't see the file
extensions go to the View menu on
Windows Explorer and make sure this is
ticked file name extensions okay but
anyway what we are going to do is go to
this new extract bar because we're now
in a zip file and hit extract all and
that's just going to put it on my
desktop which is ideal so I'll just
extract it to my desktop
instantly opens in a folder I can now
close the original so I'm now looking at
the contents of my zipped up excel
spreadsheet file and you can see there's
a folder all that will always be a
folder called excel and it's that that
you need to open within that there'll be
one called worksheets so you open up
worksheets and you'll have an XML file
for every worksheet what we need to do
then we've only got one in here and
that's the one that's got the protection
but you'll need to do this for every
sheet that's got protection right click
on it and click Edit that will open a
dialog it might it might just open in an
Esso but we're going to open it in
notepad you need to be able to edit it
open in Internet Explorer is not going
to help you if you've got something like
7z but WinRAR installed already on your
computer then you can edit a zip files
directly so you don't need to extract
than I'm showing you the software
free version for doing this hack right
there the code you don't really need to
understand it all you need to do is hit
ctrl F and find the word protect and
there we go right so you can see we now
have sheet protection and what you need
to do is highlight the code immediately
before it and make a note of it so I'm
going to use a blank notepad down here
code immediately before and then going
to take the sheet protection code and
all the code what I'm looking for is the
arrow kind of the right-hand arrow which
signifies the end of the sheet
protection code so I'm going to keep
going on until I find this should be
quite soon there it is right okay so
that's that so I'm going to now cut to
that using ctrl X go back and make
another note with that hit return and
then I'm also going to make a note of
the next piece of code put that there so
we now know that this piece of code goes
between these other two pieces so if we
want to put this password back that's
what we're that's where we need to put
it within the code so when it's bear
that in mind right so go back to the XML
file and we can just close it because
we've cut that entire protection piece
of code out and say yes and then if we
go back here to the file again and we
click unprotected free so this is the
name of the spreadsheet whatever the
name of the spreadsheet is that folder
highlighted or go to share hit zip that
will zip it all into one file and we'll
call this unprotected
three and we'll give it rather than zip
we'll call it xlsx course I'll give you
another warning click okay well I'm
gonna move that back onto the dashboard
right so we now have the same file in
Excel but this time you can see it's
unprotected so that that's that's kind
of it if all you want to do is remove
your protection what I'm going to do is
change the file and reinstate the
protection so I just highlight this
inside you know and obviously I would
highly recommend you do check this video
out right amazing dynamic excel charts
okay I'm now going to save this and I
can't put the protection back on here
because I don't know what the password
originally was I put my own password on
that that's that's not what I'm talking
about here but reinstating the original
password so we now have this unprotected
the rape so now when I reinstate the
protection so again change the file
extension to e zip and I'm going to open
it with Windows Explorer and extract all
to the desktop close that on there so
this is the extracted document again
back in the Excel folder the worksheets
folder and there's that sheet one which
I can now edit in notepad so that's all
the same as we previously did so now
what I need to do because in theory if I
click on there protect it can't find it
because there is no protection on the
sheet I want to put it back in where do
I put it
this is where our notes came in so I'm
going to look for sheet data
shoulder roll the finance sheet data
right is it
the next piece of code page margins no
it's not it's row that's absolutely
critical that you put this in the right
place so go back find the next one sheet
data right next piece of code is sheet
margins excellent right so we need to
paste that piece of code that missing
piece of code back in between these two
lines so we'll take the whole thing out
copy it paste it back in Hey close it
save it all right now we could close
that but I'm gonna keep it just a case
I'm in Coast all right so if we go back
up to our unprotected free now highlight
it all go to share code zip and pull
this repro tech dead it's reflected and
XLS x3 protected let's have a look it's
got my change on it it won't let me
change it superb
did it use the same password though
that's cake and when you give this back
to the person who's gonna be stunned by
how on earth did you change that you
must know the password for my unprotect
and I'm pretty sure yes same password up
for Excel as it was on this so there we
go
[Music]
so that is how you can remove a password
and make changes to file and reinstate
the exact same password without even
knowing what that password is hope you
enjoyed the video make sure you hit
subscribe for plenty more tips and
tricks on up for excel see you soon
[Music]