You may have seen the previous article about how to protect cells from editing in Microsoft Excel, but if you want to only deal with free software, how can we achieve similar results with OpenOffice Calc?
First you allow editing of certain cells, and then you protect the rest of the sheet from editing.
Confine Spreadsheet Input
Select the area you want to allow data entry in:
Now right-click and select the ‘Format Cells’ option from the pop-up menu. In the ‘Format Cells’ dialog select the ‘Cell Protection’ tab.
Next, un-tick the ‘Protected’ box and press ‘OK’.
Now to protect the rest of the sheet: from the Tools menu, select ‘Protect Document’, and then ‘Sheet…’.
You will be asked for a password:
If you leave this blank and press OK, the sheet is protected from any casual user trying to alter the content, but it is not secure as anyone could remove this protection the sheet using the same procedure.
If you do choose and enter a password, then only those users who know the password can un-protect the sheet and wreck your nice formulas.
If you don’t want some contents to even show at all, you can tick the ‘Hide All’ box in the ‘Format Cells’ dialog for those cells. If you do that, then once you protect the sheet, the cells will appear empty and will not be editable.
If on the other hand, you want the cell’s contents to appear, but you want to hide the formula that generates it, then you can tick the option ‘Hide Formula’ and then the result will appear in the cell (protected from editing, of course), but the formula bar will appear empty instead.
Protecting Sheet Names
You can go even further and prevent those pesky users from rearranging or renaming the sheets. To do this, open the ‘Tools’ menu and select ‘Protect Document’ and then select ‘Document…’. You can now enter a password for messing with the document’s sheets.
As you can see, OpenOffice has made it pretty easy to protect your work from all but the most determined spreadsheet vandal or clumsy co-worker!
About the author
Yoav is the CEO of Cogniview, a company that creates PDF to XLS conversion software.
Prior to that, Yoav Ezer was the CEO of Nocturnus, a technology-centered software solution company.