Alice has attended:
Excel Intermediate course
Freezing a format
Hi there,
I have a question I forgot to ask at a training today. I would like to lock/freeze the formatting of the majority of an excel spreadsheet, but allow users to input data into certain cells (it's a budget). Is something that can be done, and if so how?
Thanks in advance,
Alice O
RE: Freezing a format
Hi Alice,
Thank you for the forum question.
Yes, you can absolutely do that in Excel! You can lock the formatting and structure of most of your spreadsheet while allowing users to edit only specific cells (like input fields in a budget). Here's how to set it up:
Step-by-Step: Lock Formatting but Allow Data Entry
Select All Cells:
Press Ctrl + A to select the entire sheet.
Unlock All Cells:
Right-click > Format Cells > go to the Protection tab.
Uncheck Locked and click OK.
This ensures no cells are locked by default.
Select Cells You Want to Protect (Formatting, Formulas, etc.):
Highlight the cells where you want to freeze formatting or prevent editing.
Right-click > Format Cells > Protection tab.
Check Locked and click OK.
Protect the Sheet:
Go to the Review tab on the ribbon.
Click Protect Sheet.
Choose a password (optional but recommended).
Make sure "Format cells", "Format columns", and "Format rows" are unchecked (so users can't change formatting).
Ensure "Select unlocked cells" is checked (so users can still input data).
Click OK.
What This Does:
Users can only edit the cells you left unlocked.
They cannot change formatting, formulas, or structure in the locked areas.
Your budget layout and formatting stay intact!
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
