 |
| Protecting Cell Data in Excel After you have a developed an Excel spreadsheet that is going to be used by others you should consider protecting the spreadsheet to guard against any unplanned changes to formulas and formatting by less experienced users. Excel's Cell Protection feature can provide this functionality ensuring that critical formulas are not inadvertently changed when using the spreadsheet. In this month's newsletter I will demonstrate Excel's Cell Protection and how it can be used to protect your spreadsheet.
We will start with a sample spreadsheet that contains Sales data for an organisation. The users of the spreadsheet need to include records for the Sales Staff, Region, Month and Total Sales. After providing that information the spreadsheet calculates the % of Total Sales and the Total Sales for all Sales Staff. Those calculations are contained in the cells highlighted in Red below. If a user were to mistakenly enter data into those cells the formulas would be overwritten and the calculations would no longer work.  Step 1 The first step in protecting those cells containing formulas is to "Unlock" all cells on the worksheet. This will allow data to be entered into those cells that require it when we protect the worksheet. So select all cells in the worksheet and then right click on the worksheet and select "Format Cells". Select Protection and uncheck the "Locked" option if it is selected and then select OK.  Step 2 Now we will select only those cells that require protection for this worksheet. In this case that is E6:E10 and D13. With those cells selected choose Format Cells again and this time check the "Locked" option and then select OK. Step 3 Now before we can release the Spreadsheet Cell Protection needs to be enabled. To turn on protection for the cells that we have specified select "Protect Sheet" in the Review Tab on the Ribbon. In Excel 2003 select Tools -> Protection -> Protect Sheet.  In the dialog box you can select a number of options to allow for different actions to be taken on the cells that have been locked. For this sample we will select the default options that will restrict the entry and update of cells we have locked. You can also choose to specify a password for the worksheet protection, if you do this be sure to keep a record of the password somewhere should you need to unlock the worksheet and change the formulas at some future date. For the purpose of this sample we will not specify a password and simply select OK to enable worksheet protection. Once we do that the cells we have "Locked" can no longer be modifed. All other cells on the worksheet that are "Unlocked" can still be changed and updated. Step 4 If you now try and modify one of the cells we "Locked" then the following message will be returned to the user, and the changes made are ignored.  With Cell Protection in placed for this worksheet you can now release it for use safe in the knowledge that changes to the formulas you have developed will not be changed by those using the worksheet. Things to note about Cell Protection - Only the cells that are Locked will be protected by Worksheet Protection - By default Excel "Locks" all cells on a worksheet and you need to specify which cells do not need protection - Cell Protection is only enabled after Worksheet Protection is enabled - If you specify a password during Worksheet Protection you need that password to modify the formulas in the future - Be sure to thoroughly test a Worksheet that has been protected to ensure that the use and functionality of the Worksheet is not affected by the Protection. This is just one example of how "Cell Protection" can be used to ensure your hard work is protected when building spreadsheets. If you would like any advice or assistance in implementing Cell Protection for one of your workbooks then please Contact Us and one of our consultants will step you through the process. Subscriber Special !!
25% off all Consulting until the end of March, 2011 Contact Us now and mention this newsletter offer to secure your discount If you are not yet a subscriber to the XLAutomation newsletter then Click Here to subscribe and receive guidance and tools that will increase your efficiency in using Microsoft Excel. |
Launching "Your Business Dashboard" XLAutomation is pleased to announce the launch of a new product aimed at improving business performance analysis. Click Here for more information on this new product. Unitl the end of March, 2011 subscribers can purchase "Your Business Dashboard" with a 50% discount. Simply use the Coupon Code CPN19925869375 during the purchase to receive your discount. Job Tracker Launch special - 50% saving on offer !! Get a head start on the rest of the market....Be one of the first 100 people to buy Job Tracker and receive a huge 50% discount off the purchase price ! Simply Click Here to register your interest and you will be sent an email the day before its release to secure your 50% discount. |
| |