Creating a Table Everybody Will Use? Use This Excel Sheet Protection Feature

If you’ve used Excel to create a database table with fixed formulas, and that table will be used by multiple people, there’s a way to protect the sheet from being written over while still enabling editing in fields where people need to enter data.

From the Excel Home tab:

1. In the Cells section of the ribbon, click on the Format dropdown menu.
2. Select “Protect Sheet.”
3. A Protect Sheet menu appears, giving you the option to password-protect it.
4. Choose a password and confirm it (Caveat: if you lose or forget the password, it can’t be recovered. So keep these passwords and their corresponding workbooks and worksheets written somewhere.)
5. Click OK.

At this point, anyone who doesn’t have the password and attempts to type something into the sheet will get a notice that says: “The cell or chart you’re trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password.”

The next step is to unlock the specific cells where people will enter data.

Unlocking cells in Excel

Highlight the cells that need to be unprotected. If you’re selecting multiple, non-consecutive cells (e.g., cells in another column), you’ll need to use Ctrl and the thick plus-sign cursor to highlight them all.

Click on the Format dropdown menu. Where it says “Lock Cell,” you’ll notice there’s a padlock icon with a square around it. When you select Lock Cell, the square will disappear, meaning the cells you highlighted are now unlocked even though the rest of the worksheet is protected.

Pro tip: Set a background color for the cells where data will be entered by using the paint can dropdown menu in the Font section of the ribbon.

For more Excel table and chart hacks, check out the workshops at LearnExcelNow.com as well as the on-demand ResourcefulFinancePro webinar “Excel School: Introduction to Excel Charting.”