Use an Excel style to identify data input cells

It’s common to highlight input cells so users know which values to change, and what to leave alone! If you protect the sheet, unlocking the same input cells is another chore you must complete. Why not format and unlock at the same time by assigning a style?

You can create a style that both formats and unlocks input cells – here’s how:

1. Click the Home tab and click the Cell Styles dropdown in the Styles group.
2. Click New Cell Style at the bottom of the list. (In Excel 2003, choose Style from the Format menu.)
3. In the Style dialog box, enter the name InputCell, and click Format. (In Excel 2003, overwrite the existing name and click Modify.)
4. Click the Border tab and build a border for each cell (I chose a solid line from the Style list and clicked Outline in the Presets section.
5. Click the Fill tab and choose light green.
6. Click the Protection tab and uncheck Locked.
7. Click OK. The Style dialog will display the formats and properties. Notice that the Protection options displays No Protection.
8. Click OK again.

Now, you’re ready to apply the style, which is now available to all the sheets in the workbook. To apply the style, do the following:

1. Select the input cells. In the sheet below, that’s B2:E5.
2. Click the Home tab.
3. Click the Cell Styles dropdown in the Styles group. (In Excel 2003, choose Style from the Format menu).
4. Click InputCell and Excel will apply that style to B2:E5. (In Excel 2003, choose InputCell from the dropdown list and click Add.)

Now you’re ready to enable protect as follows:

1. Click the Review tab and then click Protect Sheet in the Changes group. (In Excel 2003, choose Protect from the Tools menu and then choose Protect Sheet.)
2. Check the Unlocked Cells option and click OK.
3. Enter a password and click OK.
4. Confirm the password.

Users can select and enter, change, and delete data in B2:E5, but they can’t even select other cells. That’s because, by default, all cells are locked but we unlocked B2:E5 when we applied the InputCell style before enabling protection.

Adding a style to format cells can save you time, but using a style to also unlock input cells makes the style even more efficient. Once you’ve added the style to a workbook, you can quickly show most users how to apply it themselves to sheets they share.