How to Protect XLS Cells From Changes

Spreadsheet formulas can be very complex and cause problems with calculations when overwritten.

Microsoft Excel spreadsheet XLS files typically contain a number of formulas or "calculated" cells that are not meant to have data entered directly into them by users. These formulas can be protected by the software to avoid accidental overwriting by users. This can save significant frustration from troubleshooting spreadsheet formulas that no longer work. Learn how to protect cells from change using the tools built into Microsoft Excel 2010.

Instructions

1. Click "Start," followed by "All Programs," "Microsoft Office" and "Microsoft Excel 2010" to launch Excel. Alternatively, launch Excel by double-clicking on a desktop shortcut or an Excel file in Windows Explorer.

2. Navigate to the worksheet that contains the cells you wish to protect.

3. Click the "Select All" button to select the entire worksheet. It has a small triangular shape and is located in the upper left corner of the spreadsheet between the column "A" label cell and the row "1" label cell.

4. Click the "Home" tab. Click "Format" in the "Cells" group and click "Format Cells" from the context menu.

5. Click the "Protection" tab and clear the "Locked" check box and click "OK" to unlock the entire worksheet. Excel locks all cells on a worksheet by default and to lock specific cells it is necessary to unlock the worksheet first and then lock specific cells.

6. Click once on the first cell that is to be protected to select it.

7. Hold down the "CTRL" key on the keyboard. Click additional cells individually to select them or drag the mouse over a range of cells to select the entire range. Release the "CTRL" key when all cells on the sheet that are to be protected have been selected.

8. Click the "Home" tab. Click "Format" in the "Cells" group and click "Format Cells" from the context menu.

9. Click the "Protection" tab and click the "Locked" check box and click "OK" to lock the selected cells.

10. Click "Format" in the "Cells" group on the "Home" tab. Click "Protect Sheet" and ensure the check box is checked for "Protect Worksheet and Contents of Locked Cells." Supply a password if desired and select additional options from the list as necessary to enable users to work with the spreadsheet. Click "OK" to protect the worksheet and lock the cells.

Tips & Warnings

• The worksheet must be "protected" before the cells marked as locked are actually locked.

• Do not lose the password when protecting a worksheet.

References

• Microsoft Office; Get to Know Excel 2010: Create Formulas; Judi Hurlock
• Microsoft Office: Unlock Specific Areas of a Protected Worksheet

Resources

• Microsoft Office: Getting Started with Excel
• Microsoft Office: Office 2010 for Business: Download a Free Trial