How to Protect Individual Cells in Excel - 0 views

• 
  #1 Freddie Melton on 18 Feb 12

   

  First we need to understand what happens when we password protect a spreadsheet in Excel. By default, all cells in Microsoft Excel are locked when protection is applied to the worksheet. What we have to do before we password protect the spreadsheet is to tell Excel which cells we don't wish to be locked. This is achieved by first selecting the cell or range of cells which are to be left unprotected, then right-click with the mouse to produce the Format dialogue box. Now select the tab marked 'Protection' and you will see that there are two tick-boxes, 'Locked' and 'Hidden'. By default, the Locked box will have a tick inside. This means that when password protection is applied to the spreadsheet, the cell (or cells) will be locked. So all we have to do then is to remove the tick by clicking in the box, then choose 'OK' to save the changes. The spreadsheet has now been prepared for password protection.
  
  Applying a Password to the Spreadsheet
  The next stage is to select the 'Review' tab on the Ribbon and then click the 'Protect Sheet' tool button. You will be presented with a small dialogue box which contains a password field. It also has a number of tick-boxes which allow you to choose what can and cannot be done once the spreadsheet has been protected. By default, the selection of locked and unlocked cells is permitted, but nothing else. Now type a password into the field and click OK. You will then be asked to reapply the password to ensure that you have spelt it correctly. The spreadsheet will now be protected, but the cells which you previously marked as unlocked will still be editable. To allow editing of the entire spreadsheet again, simply click the 'Unprotect Sheet' tool button and type in your password.
  
  What if you Forget Your Password?!
  If you forget your password it may seem at first that you are permanently locked out of your spreadsheet. However, there is a way around the protection, depending upon what actions you have allowed before applying your password. As previously mentioned, by default Excel will allow you to select both unlocked and locked cells when the worksheet is protected. Provided that this has been allowed, the solution is actually very simple. All that you have to do is to select the whole of the spreadsheet, then copy and paste it into a new blank workbook. This will have the effect of copying all of your data and formulas, but not the password protection. You can now save the work book, delete the original and then rename the new work book accordingly. In essence, you now have the original spreadsheet but without the password protection! However, if you had un-ticked the boxes allowing the selection of cells before applying your password, you will not be able to select the spreadsheet and hence will not be able to copy it. This is useful if you wish to prevent other who know the trick from doing the same thing. Just make sure you don't forget your password!
  
  
  James Love is the Managing Director of an IT training Company which provides Microsoft training throughout the UK. For more information on Microsoft Excel training courses in Durham visit http://www.microsofttrainingcourses.org/

   

  <div class="cArrow"> </div><div class="cContentInner">First we need to understand what happens when we password protect a spreadsheet in Excel. By default, all cells in Microsoft Excel are locked when protection is applied to the worksheet. What we have to do before we password protect the spreadsheet is to tell Excel which cells we don't wish to be locked. This is achieved by first selecting the cell or range of cells which are to be left unprotected, then right-click with the mouse to produce the Format dialogue box. Now select the tab marked 'Protection' and you will see that there are two tick-boxes, 'Locked' and 'Hidden'. By default, the Locked box will have a tick inside. This means that when password protection is applied to the spreadsheet, the cell (or cells) will be locked. So all we have to do then is to remove the tick by clicking in the box, then choose 'OK' to save the changes. The spreadsheet has now been prepared for password protection.<br /><br /><b>Applying a Password to the Spreadsheet</b><br />The next stage is to select the 'Review' tab on the Ribbon and then click the 'Protect Sheet' tool button. You will be presented with a small dialogue box which contains a password field. It also has a number of tick-boxes which allow you to choose what can and cannot be done once the spreadsheet has been protected. By default, the selection of locked and unlocked cells is permitted, but nothing else. Now type a password into the field and click OK. You will then be asked to reapply the password to ensure that you have spelt it correctly. The spreadsheet will now be protected, but the cells which you previously marked as unlocked will still be editable. To allow editing of the entire spreadsheet again, simply click the 'Unprotect Sheet' tool button and type in your password. <br /><br /><b>What if you Forget Your Password?!</b><br />If you forget your password it may seem at first that you are permanently locked out of your spreadsheet. However, there is a way around the protection, depending upon what actions you have allowed before applying your password. As previously mentioned, by default Excel will allow you to select both unlocked and locked cells when the worksheet is protected. Provided that this has been allowed, the solution is actually very simple. All that you have to do is to select the whole of the spreadsheet, then copy and paste it into a new blank workbook. This will have the effect of copying all of your data and formulas, but not the password protection. You can now save the work book, delete the original and then rename the new work book accordingly. In essence, you now have the original spreadsheet but without the password protection! However, if you had un-ticked the boxes allowing the selection of cells before applying your password, you will not be able to select the spreadsheet and hence will not be able to copy it. This is useful if you wish to prevent other who know the trick from doing the same thing. Just make sure you don't forget your password!<br /><br /><br />James Love is the Managing Director of an IT training Company which provides Microsoft training throughout the UK. For more information on <a target="_new" href="http://www.microsofttrainingcourses.org/onsite-training-courses/microsoft-excel-training-courses-in-durham" rel="nofollow">Microsoft Excel training courses in Durham</a> visit <a target="_new" href="http://www.microsofttrainingcourses.org/" rel="nofollow">http://www.microsofttrainingcourses.org/</a></div>

  ...

  Cancel

To Top