Software

Use an Excel style to identify data input cells

Susan Harkins shows you how to format and unlock input cells in Excel at the same time by assigning a modified style.

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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

16 comments
art777
art777

Thank you Susan! I am new to Excel, and I like it. You tip is very well worded, lucid and helpful. Arthur

jane.leonard
jane.leonard

Great tip, never really used the cell styles . Thanks

mjacquet
mjacquet

Very good: really easy to implement and mighty useful. Great in a template. Thanks

TheNorm
TheNorm

Thank you, Susan H. I'm preparing a spreadsheet for website publication and this will be very handy. I'll share it with my colleagues, if that's okay. I also like the idea of conditionally formatting the cell when it has been filled in. I'll try that. Another idea is to change the COLOR when the input does not meet validation criteria. Thanks Again.

robinr
robinr

This Cell Style only appears on the workbook that you create it in? Is there a way to always make it available?

coveycraig
coveycraig

Refered to "Word 2003" no less than five times when the article is about Excel 2003 -> 2010.

bowlsys
bowlsys

Is this discussing Word or Excel?

derek.newsbox1
derek.newsbox1

Useful. I would like to be able to make the background change when cell has had data entered. I can do that by Conditional formatting, e.g., by using a formula =ISBLANK() Can this be made part of a style? I admit I should try and see first.

DBlayney
DBlayney

This is a super little tip. I can see that I shall probably define a few of these for general use such as Numeric_Input, Alpha_Input, Date_Input and so on. Wouldn't it be good if data validation could be included inthe style definition?

ssharkins
ssharkins

Yes, you're right -- I'll get that corrected right away. I write about them all, almost every day, and occasionally, I do lose my place. Proofreading my own work is my hardest job.

LocoLobo
LocoLobo

She's just telling you how to do the same in Word instead of Excel.

DaveUnger
DaveUnger

Excellent tip, Susan! I use a somewhat similar technique in VBA, when I need to filter a selected cell. I "group" all the pertinent cells in a sheet by style. Now, when the cell is selected, the SelectionChange sub checks the Target.Style, and branches to the required procedure.

ssharkins
ssharkins

Thank you Mark, was just getting ready to take care of that but you beat me to it! ;)

ssharkins
ssharkins

I'd been writing about Word and just kept pulling Word out of my hat instead of Excel. This is totally about Excel -- nothing to do with Word. Sorry for the inconvenience.

Editor's Picks