Avatar of Robert Berke
Robert BerkeFlag for United States of America asked on

How to protect format of cell $A$1, but let other cells be unprotected.

I thought I completely understood Excel’s cell protection features, but today I realized I still have things to learn.
I did not know that it was impossible to protect the format of specific cells.  It is all or nothing.  If any portion of the worksheet is protected, the format of every cell is protected.
Or maybe I am missing something. If so can someone please tell me what I am missing?
Without using VBA I want the format of cell  $A$1 to ALWAYS BE MM/DD/YY.
Other cells should be unprotected so that their format can be changed.
Seems simple right?  But I am beginning to think it is impossible.
The following creates a worksheet that allows me to change the value of any cell except A1.
1 Create new worksheet.
2 Ctrl A to select all cells
3 Ctrl 1 > Font > protection > clear the "Locked" checkbox > OK
4 select A1
5 Ctrl 1 > Font > protection > turn on the "Locked" checkbox > OK
6 ribbon > review tab > protect sheet > OK .  The protect sheet tab looked like this
But I cannot change the Format of any cell in the worksheet.
I then changed step 6 and tried many variations of the 15 check boxes, but that didn’t help.

rberke (aka UncleBob)
 protect a worksheet so that cell A! cann

VBAFonts Typography

Avatar of undefined
Last Comment

8/22/2022 - Mon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Robert Berke

Not selecting the cell has some pretty extreme implications.

For instance, I  cannot easily select row 1, nor column A. I always have to use Name Box which is a huge Nuisance.
And instead of doing Ctrl A I must type A:XFD into the Name Box.

Nonetheless, your solution comes close to doing what I want.  I will close this question soon.

Robert Berke

I am sticking to my original conjecture about the Protect Sheet” dialog.  When turning on the “Format Cells”  every cell in the worksheet can be reformatted,
 If the check box is turned off, none of the cells in the worksheet can be reformatted.
I find it a little strange that it does not matter which cells are locked.

 The proposed solution by Wilder1216 only prevents easy selection of cell a1.  And that only applies to the locked cells.
 If  A! (or 1:1 or similar range) is seleted using the Name Box all cells including A1 can be reformatted using normal shortcut keys like ctrl alt $ or other normal Excel formatting techniques.
 Nonetheless, its effect is nearly the same as I want so I am awarding him the correct answer.


Thanks, and I am happy if I have been of any help.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck