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
[Music]
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
the 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 sheet 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 the password and you can
just type it straight in and unlock the
shape so if that applies you're onto a
winner
right assuming it's not an Excel macro
workbook
you're gonna need method two so if you
can't find a password 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
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 it
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 could 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 of 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 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 gonna 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
of this budget before you 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
prevents them in my book and that is go
through and you can click in the margins
of the VBA couraging but
stops now that will just stop the 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
one's run you can unclick it and hit f5
again I now run in fact affecting this
running that league twice now yeah and
now that's finished and done 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 when it when
it's finished so I'm just going to run
through this myself and then 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 OK 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
and that one was protected sheet free
hours just okay and what you need to do
is change the file extension of the file
to a zip file ok 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
you can right click on that zip file and
you open it with Windows Explorer so if
you can't see settings by the way 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 path because
we are 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 just
extracted 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 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
Internet so but we're gonna open it in
notepad you need to be able to edit it
opening it an internet explorer is not
gonna help you if you've got something
like 7z but WinRAR installed already on
your computer then you can edit zip
files directly so you don't need to
extract them
I'm showing you the software free
version of doing this hack right there
the code you don't really need to
understand it all you need to do set
control 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
just code immediately before and then
going to take this 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 codes I'm going to keep going
long 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 control X go back and make another
note of 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 on protected 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 of course I'll give
you another warning click okay I don't
leave that back on 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 of
Tory zip and I'm going to open it with
Windows Explorer and extract all to the
desktop close that one there so this is
the extracted document again back in the
Excel folder the worksheets folder and
there's the 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
troll death trophy find notes 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 extra 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 in case something
goes wrong 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 Reaper tected and
Excel sx3 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
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 by excel see you soon
[Music]
[Applause]