MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Protecting a Single Cell in Excel

September 05, 2004 - by Bill Jelen

Reader Greg McFarland sent in a cool suggestion. Another reader had asked if it is possible to protect a single cell in an Excel worksheet without protecting the whole worksheet. In general, this is not possible.

However, Greg provides an interesting workaround that will keep most people from changing the cell.

  • Click in the cell
  • From the menu, select Data
  • Then select Validation
  • In the validation box are various options. Use the settings: Allow Date, between 01/01/01 and 01/01/01.
  • Click OK to close the Validation box

If anyone tries to type anything other than 01/01/01 in the cell, they will receive a message saying the value that they entered is not valid.

Greg notes,

Since doing this I never have people typing in cells on shared documents and they don’t really know enough to go to the data – validation tab to figure it out. It isn’t complete fool proof, but it works great for me.

The usual caveats: Setting up data validation does not prevent someone from using copy and then pasting an incorrect value in the cell. Also, if your spreadsheet users know the validation technique, they can override your setting. However for most novice users, this should work fairly well. Thanks to Greg for sharing the tip!

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.