hi nicholas buffett is here from the
junior senior school in sunny cyprus
although it's quite dark right now but
that's because of the time
okay this tutorial a little bit
different to the ones i normally make
but it's educational nonetheless the
reason i'm making it is because i had a
colleague that actually protected some
of the worksheets in a workbook with a
password so that other colleagues
couldn't modify those sheets but they
could work in the other sheet without
fear of changing the data in some of the
protected sheets
they forgot the password and instead of
having to copy the entire
contents of that workbook to another
workbook it was easier just to break the
password now there is also another issue
if a file is shared using a link
then even if you copy paste that
material into another workbook the link
has broken so you're going to have to
re-share that document with all the
staff that have access to it so in this
tutorial i'm going to show you how you
can protect a worksheet
how you can protect the workbook from
changes like people adding worksheets or
deleting worksheets and how you can make
the entire workbook
read-only and then i'm going to show you
how you can actually remove all this
protection
without using the password basically
breaking the password protection
let's get started
okay you can see here i've got one
folder
and inside this folder i've got a
spreadsheet before we begin i need to
show you how you can make the extensions
of file names visible so you can see
here the name of my file is sources.xlsx
so if you can't see the file extension
you need to be able to enable that so to
do this very quickly i'm going to show
you two quick steps first one is i'm
going to click on the little windows
icon bottom left corner i'm going to
type the word file and you should find
here file explorer options when we click
on that that window opens you can also
find this window from the control panel
so i'm going to just click here type
control go to the control panel
and again you can find the file explorer
options exactly the same dialog box
there it is there so i'm just going to
close that control panel and have a look
here so essentially what we want to do
is go to view and in view we want to
find this option here there it is hide
extensions for known file types if i
enable this and click on apply you can
see the extension of my sources has now
vanished
i need to remove that tick click on
apply and that will display the
extension so that's your first step
now the first thing i need to do is to
make a copy of this spreadsheet it's
always good to make a copy in case
something goes wrong you've got the
original to go back to so i'm going to
right click drag over
copy here and then this copy here i'm
going to rename it as
protect
sheet
so the first exercise we're going to do
is we're going i'm going to show you how
we're going to protect these sheets so
that it's read only
and then we're going to break the
protection of that sheet
so i'm going to double click on this to
open it
i'm going to choose
this worksheet here it's number three
one two three in order now this is
important because later when we want to
find the spreadsheet that we want to
unlock we need to know which sheet
number it is because we won't have the
sheet names
okay let's protect this spreadsheet i'm
going to go to review and here you've
got protect sheet so i'm going to click
on that i'm going to add a password one
two three four click ok add the password
again one two three four
obviously you're going to put a more
secure password and now i'm going to
save this
and close
so now when i open this if i go to this
spreadsheet these spreadsheets here i
can type information in and i can modify
this spreadsheet here if i try to type
anything in it gives me a warning saying
that this sheet is protected and if you
want to be able to edit it you have to
unprotect it so if i click ok i go to
view and i'm going to click on unprotect
sheet and oops if you can't remember the
password you cannot edit it
okay so let's now i can enter the
password it will unlock but that's not
the point let's click cancel close this
and don't say
so let's move on to breaking or
bypassing the password of this
protection i'm going to
click once and click again so that i can
edit the file name or you can just right
click rename and we want to rename the
extension so this xlsx we want to change
that to zip
press enter you're going to get a
warning saying that
are you sure you want to change the
extension i'm going to click yes and
it's going to display it with whatever
your default program is for zip files my
i actually use winrar but it's not
important and in this demonstration i'm
not going to use winrar although it is
actually easier to modify in winrar so
i'm going to assume you don't have
winrar i'm going to right click and i'm
going to go to open with and i'm going
to choose windows explorer
okay so here's windows explorer and we
can see now our excel file has been
unpackaged and these are the different
files which that excel file is made up
of the one i'm interested now is this
folder called excel
let's double click on this okay so the
workbook xml this holds all the
information about the workbook and we're
going to look at that when we're going
to break the password on a workbook
protection
but right now we've got protection on
sheet three so i'm going to go to
worksheets
and you can see here i've got three
worksheets and this is what i was saying
before you need to know which worksheet
you're going to work with and if you
don't it's not that important you will
only find the line you're looking for
in the sheet which is protected so i
know it's sheet 3 because i counted it
before i'm going to right click on this
actually i'm just going to double click
on this and it's going to ask me what
application do i want to use to view
this file i'm going to choose notepad
click ok
now if your view looks like this
in and it's very very long lines like
this you can
click on format and just choose to you
look at it or view it in word wrap view
that isn't going to really help that
much more i'm going to click to be right
at the very top so you can see my
insertion point right at the very very
beginning of this just makes finding a
little bit easier
and i'm going to maximize this window
and zoom in a little bit so you can
actually see a bit better
not that there's much to see here so i'm
going to press ctrl f on my keyboard for
find or you can just go to edit
find
now i'm going to type what i'm looking
for
pro
just leave that find next
all right it can't find so i'm going to
do a down search
there you go
okay so if we have a look here it's
actually called sheet
protection
okay so let me just close
that find window
restore this so this can come up a
little bit
and here we go here
sheet protection now what i want if you
look at this line here it's actually got
the algorithm name
and here's your hash value your password
encrypted so we want to go from where it
starts sheet protection this triangular
arrow or the less than symbol
from here to the next
triangular arrow which is the greater
than in other words the close bracket so
if i go through here
and i'm going to find
there we go
so that's the end of that parameter
i think that's the right one yes it is
okay you can see that the next parameter
begins with
that triangular arrow i want to delete
this line here
so i'm just going to click on delete
now i'm going to save my file file save
it's asking me where to save the file
i'm going to put this on my desktop
and i need to leave the name as it is
sheet3 xml save
and i can close this
now if we have a look
i've got here this file
which has just been saved
i'm going to take this file and drag it
into the folder
which i viewed and let's just show you
where i had that so that's my zip file
i'm going to
right click
open with
windows explorer so i want to go to
excel
here are my worksheets and i want to
drag this in here
and when i get the warning i want to
copy and replace i want to replace the
file with the new file and the new file
doesn't have the protection i can close
this now
i'm going to go back to my folder i'm
going to rename this now so back to xlsx
enter there's a warning click yes and
your sheet now is unprotected so if i
double click on this
and look at that
my sheet is unprotected
let's go now to the next
type of protection so i'm going to
modify this
same excel file again so i'm going to go
to review and this time i'm going to do
protect workbook now what this does
let's put a password one two three four
and this is going to protect the
structure of your workbook so it's not
going to make it read only but it's
going to protect the structure and i'm
going to show you what that means
and then again enter the password click
ok
now when i save this
if i close the workbook and open it
again
right you can see here at the bottom now
it's not possible to create a new sheet
okay it's not possible to actually
delete the sheet most of these options
are gone so the structure of the
workbook is being retained
if you want to unprotect it so you can
add or delete sheets again you're going
to go to review
you're going to click on protect
workbook it's going to ask for the
password if you know the password it
will let you it will unlock it and you
can but
that's not the point let's go back
so we're going to do exactly the same
exercise as before
with the sheet protection i'm going to
click rename this file to zip
press enter there's a warning
there's my zip file right click open
with and i'm going to open this with
windows explorer
go to excel and again i'm working on the
worksheet
actually no i'm working on the
book
okay so i want to open this file now
because i protected the structure of the
workbook not of a single sheet so i'm
going to double click on this
opening notepad
and now what i'm looking for is the word
workbook protection or you so you can
look for protection if you want so i'm
going to go to edit find
and i'm going to type
work
book
pro
and let's just do a quick find next
now there it is there
workbook
pr now i want protection
so let's just carry on
pro
all right let's search the other way
there it is there
workbook protection okay so i'm just
going to zoom into that so there's the
property so i'm going to select from
that triangular bracket the left side
and i'm going to copy all the way down
to where i see that greater than bracket
there it is there okay so along this
whole line here you will not find
another triangular bracket which is
greater than
okay i need to delete this just like
before i'm going to go to file save
i'm going to save this as it is workbook
xml save
close this and it should there it is x
just appeared there's my workbook and i
want to drag this now into
that zip file copy and replace
and now
i can go back
rename this back to xmsx
dot
xlsx make sure you don't delete the dot
click yes and now when i open this
again no password but i've got the
ability to create and delete worksheets
perfect okay one last one now this time
what i'm going to do i'm going to modify
the workbook so that whenever it is
opened it is in read only mode unless
you know the password now to do this you
need to save your workbook as a new file
so i'm just going to click on save there
so i'm going to open up my workbook
in fact i'm going to delete that one
there
and here's my original file i'm going to
double click on this and now what i'm
going to do there's my three
sheets i'm going to go to file
save as
i'm going to click browse and what i'm
going to do now i'm going to tell you
where to save it so my desktop excel
password there it is i'm in the right
place i'm going to give this a new name
sources and i'm just going to call that
read
only
okay and before i click on save i need
to click here where it says tools
and now i'm going to choose this one
here general options
okay now be very careful
this password here is the password to
open an excel file
we cannot break that password
okay and i'll demonstrate that at the
end of this tutorial the one we want to
do is this one here password to modify
so again one two three four
click ok
and then re-enter the password to modify
one two three four click okay
and now i'm going to click save
okay so if i close this now
and i open this sources read only
i'm faced with this dialog box here
which says if you know the password then
enter it and you can modify the
spreadsheet if you don't you go into
read-only mode okay so this is a
fantastic way if you want to share your
spreadsheets with a staff or student but
you don't want them to add anything you
just want them to be able to to read the
data in there
then you can use this method
okay now if i want if i've forgotten the
password and of course i need to break
the password then the process is exactly
the same we're going to demonstrate this
one last time but we're just looking for
a different path now so again xlsx
change that to zip
press enter warning correct
let's open this in explorer again
so open with windows explorer and go to
the excel folder now again this property
has been applied to the workbook so i
want to open the workbook xml
notepad
and now what we're looking for is file
sharing so let's have a look go to edit
find
and i'm going to be looking for
file
sh all right let's do a down search my
cursor is in the wrong place fine there
it is there so i can close that
and
there is the file that i'm looking for
that line not the file so i want to go
from the beginning of this open bracket
here and we want to go all the way to
where it closes
there okay you want to be careful and
this way you want to do a backup of your
spreadsheet in case you delete something
more than something you shouldn't
and you make your file
unusable so i'm just going to delete
that now
again same process file save
i'm going to save this as workbook now
i've already got one called workbook in
on my desktop it doesn't matter it's
going to overwrite it save yes
and there we go so there's my workbook
i'm going to drag that in now copy and
replace
now i can go back
rename this zip file
to xlsx
and to enter and now when i open this
there's no more warning that it's a read
only you've just unlocked your file no
password okay
so the last one i want to show you and
the one that you cannot break and the
reason is because it protects the file
from opening with a password so you're
not able to actually open it from the
zip file
so this particular one
so if i just do a save there
so if i try to open this now i'm going
to go to file
save as
again i'm going to go to the same folder
so browse
i'm going to call this something else
sources
lock
open
so you can't open this file unless
you've got the password and before i
click on save as i'm going to go to
tools i'm going to go to general options
now this one here password to open one
two three four
click ok confirm the password one two
three four
click
and save
now this password if you forget it
you're in trouble so let's have a look
and see what this one does if i double
click on this one
there you go it wants the password to
open the file you can't even go into
read only mode so i need to put my
password one two three four and that
will open if i try to use the same
process as before
and i'm going to change this cancel
let's just rename that and i change that
to a zip
enter yes and now i go to open with
and i try windows explorer i'm going to
get an error and that's because for the
file to open it's encrypted you need the
password
okay that's it i hope you've enjoyed
this i hope you've learned something if
you have make sure you subscribe make
sure you like and i'll see you again in
the next video take care bye