#1
|
|||
|
|||
Protecting the formatting and formulas in a worksheet
Good day, In Excel 2007 I am wanting to protect the formatting and formulas in a worksheet that will be used by two other people. I have tried various settings all without luck. Imagine that you have set the formatting in column A, row 6:450, to Date. The type is 3/14/10 (dd/mm/yy). I want to retain this formatting and allow someone to input dates. The problem is that they can accidentally change the formatting or paste some other information into any cell in that column. So how does one keep the formatting set? In my worksheet I have 34 columns which I would like to protect. All 34 columns require data entry of one sort or another. Some straight text, others numeric inputs. Insofar as formulas, I have found a partial solution by just hiding the columns with my formulas, but is there a better way to protect them than just simply hiding? Thanks for your help DD |
#2
|
|||
|
|||
Protecting a sheet in Excel involves two things... worksheet protection and cell locking. Cells are locked by default, so you need to unlock the ones you want users to be able to work with, before you turn on worksheet protection. You might choose to select all the cells, unlock them, and then lock the ones with formulas... this will allow users in all but locked cells.
Then go to the Review tab and click Protect sheet, making sure you don't allow them to change formatting. The password can be left blank. |
#3
|
|||
|
|||
Thanks for your reply Kimberly.
The method you described certainly can protect the formatting, however it does not allow for any data input into those cells. Excel 2007 has one option under the protect sheet icon and that brings up the protect sheet dialogue box. This does not offer any options for protection of formatting. Under the protect workbook icon there is the option to protect structure and windows, neither of which will protect the formatting in cell and allow for data input. I would like to have the worksheet setup so that individuals who will be entering data cannot accidentally change the formatting in a cell. How does one setup the sheet to allow for formatting consistency and data entry? I have used your method for protecting my formulas. Work great! Thanks DD |
#4
|
|||
|
|||
Before enabling sheet protection, unlock any cells they should have access to. This is on the Protection tab of the Format Cells dialog box.
After I click Protect Sheet, I cannot change any formatting on the sheet at all. I can only edit the content of cells that have been unlocked. Regarding Quote:
When you click Protect Sheet, don't you get a list of things you can choose to allow the user to do, and Format Cells is one of them? Do not check the box for Format Cells and they won't be able to change your formats. |
#5
|
|||
|
|||
Here is a simple experiment. Open a new sheet in Excel. Click on the arrow in the upper left corner of the spreadsheet to highlight the entire worksheet. Right click and choose Format Cells >>> Protection >>>Unlock.
Now every cell is unlocked. Highlight A1-A6. Right Click and choose Format >>> Date >>> 3/14/01 for a format. (mm/dd/yy) Now go to protect sheet.The protect sheet dialog box appears. Make your selection and choose ok. In my version of Excel 2007, I have many options, the 2 which are checked by default are Select locked cells and Select unlocked cells. The list continues but all after the top two are not checked, including format cells. Click ok. Now in e5 type in some gibberish. Press return, than copy the contents of that cell and paste it into any cell from a1-a6. Format has now been changed. Or try to enter the date 17/3/11 which is not according to format. What happens? Format has changed, Why? How do we prevent this? Thanks DD |
#6
|
|||
|
|||
Sorry, as far as I know, you cannot prevent the behavior you are describing. You can use Data validation to force typed entries to be dates, but validation is not triggered by pasting, so I think this is of limited value to you. (Also, dates are just numbers, so numbers also get a pass.)
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Protecting or hiding ASK fields | SaneMan | Word | 1 | 01-14-2011 08:16 PM |
IF Formulas | mizzamzz | Excel | 1 | 07-08-2010 02:32 AM |
Protecting your logo in Word | TimD | Word | 5 | 04-29-2010 07:10 AM |
Protecting image in word 2007. | aligahk06 | Word | 1 | 10-12-2009 08:22 AM |
Protecting Word Document by restricting access permissions! | user | Word | 0 | 11-20-2008 01:21 PM |