Lock Formatting in Excel but Still Allow Data Entry

Locking formatting while people still type into the sheet sounds like a contradiction, but it is one setting and one switch. Excel locks every cell by default, and that lock does nothing until the sheet is protected.

Select the cells people should fill in, open Format Cells (Ctrl+1 on Windows, Command+1 on Mac), and clear the Locked box on the Protection tab. Then go to Review, Protect Sheet, leave "Format cells", "Format columns" and "Format rows" unticked, keep "Select unlocked cells" ticked, and click OK. People can now type into the unlocked cells and cannot restyle anything. A password is optional. The rest of this post covers what each checkbox really does, where pasted content can still get past you, and how Google Sheets handles the same job differently.

What does locking a cell actually do?

Nothing, on its own. This trips up almost everyone the first time. Every cell in a new workbook has the Locked property switched on, but Locked only matters once the sheet is protected. Think of it as a flag on each cell and a master switch for the whole sheet.

When the switch is on:

• Locked cells cannot be edited, cleared or overwritten.
• Unlocked cells accept typing and deleting.
• Formatting is controlled by a separate list of permissions in the Protect Sheet dialog, not by the Locked flag.

That last point is the useful one. "Lock formatting" is not a property you set on a cell. You get it by deciding who may use the formatting commands, and the Protect Sheet dialog is where that decision lives. The Locked flag only decides which cells accept input.

So the job splits in two: mark the input cells as unlocked, then protect the sheet with the formatting permissions switched off.

How do you unlock only the input cells?

Do this before you protect the sheet, because a protected sheet will not let you change the flag.

1. Select the cells people should be able to type into. Hold Ctrl (Command on Mac) to add separate blocks. Entire input columns work well if the form is a log, but select only as many rows as you need if it is a fixed layout.
2. Press Ctrl+1 (Windows) or Command+1 (Mac), or right-click and choose Format Cells.
3. Open the Protection tab.
4. Clear the Locked checkbox and click OK.

Then protect the sheet:

1. Go to the Review tab and choose Protect Sheet.
2. Tick or clear the options (the next section covers which ones).
3. Add a password if you want one, then confirm it if prompted.
4. Click OK.

These steps match Microsoft's own instructions for protecting a worksheet, which cover both Windows and Mac. The Review tab and the Format Cells shortcut are the same on both. The dialog layout differs a little, so read the labels rather than relying on position.

Two practical habits help here. Shade the unlocked cells a light, consistent colour before you protect the sheet, so people can see where to type without hunting. And apply your number formats, alignment and column widths first. Protecting the sheet freezes the look you have at that moment, which is the whole point, so make sure the look is finished. If it is not, run the file through the Excel formatter before you lock it, so what you lock is a clean version.

Which Protect Sheet options should stay unticked?

The Protect Sheet dialog lists what all users of the sheet are still allowed to do. Anything you tick is permitted. Anything you leave clear is blocked. The default state ticks only the two selection options, which is already close to what a data-entry form needs.

The matrix below describes each option as Microsoft documents it, with the effect on someone filling in your sheet and a recommendation for a form where the formatting must hold.

Option	What it lets a user do	Protects formatting if left clear?	For a data-entry sheet
Select locked cells	Move the pointer onto locked cells	Not a formatting control	Tick, unless you want locked cells to be unclickable
Select unlocked cells	Move the pointer onto unlocked cells and Tab between them	Not a formatting control	Tick. Clearing it stops people entering data
Format cells	Change anything in the Format Cells or Conditional Formatting dialogs	Yes. This is the main one	Leave clear
Format columns	Change column width or hide columns	Yes, for widths and visibility	Leave clear
Format rows	Change row height or hide rows	Yes, for heights and visibility	Leave clear
Insert columns	Add columns	Indirectly. New columns arrive unformatted	Leave clear
Insert rows	Add rows	Indirectly. New rows can break a layout	Leave clear for a fixed form, tick for a growing log
Insert hyperlinks	Add hyperlinks, even in unlocked cells	Indirectly. Links can change the look of text	Leave clear
Delete columns	Remove columns	Not formatting, but removes structure	Leave clear
Delete rows	Remove rows	Not formatting, but removes structure	Leave clear
Sort	Use sort commands	No. It reorders, not restyles	Tick only if every cell in the range is unlocked
Use AutoFilter	Use the filter dropdowns	No	Tick if people need to filter
Use PivotTable reports	Format, change or refresh PivotTables	Partly. Lets people restyle the pivot	Leave clear unless people use pivots
Edit objects	Change charts, shapes, text boxes and controls, and add or edit notes	Partly. Charts and shapes are formatting too	Leave clear
Edit scenarios	Change scenarios	No	Leave clear

Two details from Microsoft's documentation are worth knowing. First, Format cells covers the Conditional Formatting dialog too, so leaving it clear also stops people editing your rules. Second, if you applied conditional formatting before protecting the sheet, it keeps responding to the values people type. A rule that turns an overdue date red still turns it red on a protected sheet. Only the editing of the rule is blocked.

Sort deserves a warning. Sorting a range that includes any locked cell fails on a protected sheet, so the permission only helps if the whole range is unlocked.

Why can pasting still break your formatting?

Paste is where the earlier setup used to leak, and this is the part of the topic with the most outdated advice.

For years, pasting into an unlocked cell on a protected sheet carried the source cell's formatting with it, whatever the protection settings said. Fonts, fills, borders and number formats all arrived. Many tutorials still describe it that way. Microsoft's current protection page says otherwise: "Paste now correctly honors the Format cells option." So in current Excel, with Format cells left clear, an ordinary paste into an unlocked cell should bring in the value and leave the cell's existing formatting alone.

I could not run every version of Excel for this post, so check yours. It takes a minute. Open a second workbook, make a cell bright yellow with bold red text, copy it, and paste it into an unlocked input cell on your protected sheet. If the cell stays as it was, your build honors the setting. If the yellow arrives, you are on an older build or an environment that behaves differently, and you need the habits below.

Even on a current build, three things can still cause trouble:

• Older Excel versions and files shared with them. Anyone on an older release can paste formatting into unlocked cells regardless of your settings.
• Data validation and conditional formatting. Microsoft's community guidance notes that users cannot edit these on a protected sheet, but copying and pasting from elsewhere can overwrite them. A validation rule on a cell is replaced if someone pastes a plain cell over it.
• Content from outside Excel. Pasting from a web page or a PDF drops text in, and what you get depends on the paste option. Plain text is safe. Rich content is a case to test rather than assume.

The reliable habit is to paste as values. On Windows, press Ctrl+Alt+V and choose Values. On Mac, use Paste Special from the Edit menu. The Paste Values option puts the content in and never touches formatting, on any version.

How do data validation and an input tab help?

Protection stops edits to the sheet's structure. Two other tools handle what goes into the cells and where the damage can land.

Data validation limits what an unlocked cell accepts. On the Data tab, choose Data Validation in the Data Tools group, then set a type such as whole number, date or list, along with an input message and an error alert. Microsoft's guide to applying data validation covers the options. Two cautions apply. Data validation settings cannot be changed once a sheet is protected, so build them first. And as noted above, a paste can overwrite a rule, so validation reduces mistakes but is not a lock. A dropdown list is the most useful version for a form, because it makes the right answer a click and the format question disappears.

A separate input tab is the sturdiest design when the formatting really matters. Put the typing on one plain sheet, with unlocked cells and nothing to damage. Put the formatted output, the report people read, on a second sheet that is fully locked and pulls from the first with formulas. A bad paste on the input tab then damages nothing a reader sees. This is the same split behind a good monthly report layout, and it works well for a project tracker where several people update status but one person owns the look.

A short checklist covers most shared files:

1. Finish the formatting first.
2. Unlock only the input cells and shade them.
3. Add data validation to the cells that have a right answer.
4. Protect the sheet with Format cells, columns and rows left clear.
5. Run the paste test from a second workbook.
6. Tell people to paste as values.

How do protected ranges work in Google Sheets?

Google Sheets takes a different route, and one limit matters for this exact job. Go to Data, then Protect sheets and ranges. You can protect an entire sheet and tick "Except certain cells" to leave input cells open, then decide who may edit the protected part: only you, people in your domain, or a custom list. You can also choose to show a warning on edit instead of blocking it.

The limit is stated in Google's help page on protecting sheets and ranges: when you protect a sheet, you cannot at the same time lock the formatting of cells and let users edit their values. Protection is about who can edit a cell. It does not offer a separate "may type, may not restyle" permission the way Excel's Format cells option does.

That changes the workaround. In Sheets, the dependable pattern is the input tab described earlier. Give people edit access to the input sheet and protect the formatted report sheet so only you can change it. For more on how the two apps differ in everyday formatting, see the Google Sheets formatting tips.

Set the formatting once, then lock it

Protection preserves whatever is on the sheet at the moment you click OK. If that moment is a half-finished file, you will be answering requests to change it for months, and you will have to unprotect, edit and reprotect each time. Format the file properly, test the paste, and then lock it. The Excel formatter gives you a clean, consistently formatted version to lock down, and it is free.

FAQ

Can I protect formatting without a password?

Yes. The password in the Protect Sheet dialog is optional. Without one, anyone can remove protection from the Review tab with a single click, so it stops accidents but not a determined person. With one, they need the password to unprotect. Excel's sheet protection is meant to prevent accidental changes, not to secure sensitive data, so do not treat it as security. If you set a password, store it somewhere safe, because recovering a lost one is difficult.

Why can users still change colours on a protected sheet?

Usually because Format cells is ticked in the Protect Sheet dialog, which allows everything in the Format Cells dialog, including fills and fonts. Open Review, then Unprotect Sheet, then Protect Sheet again and clear Format cells, Format columns and Format rows. If colours still change, the likely cause is conditional formatting responding to a typed value, which is designed to keep working, or a paste in an older Excel version.

Does protecting a sheet stop conditional formatting changes?

It stops people editing the rules, as long as Format cells is left clear, because that option covers the Conditional Formatting dialog. It does not stop the rules from doing their job. A rule that existed before you protected the sheet keeps recolouring cells as people enter values that meet its conditions.

Can I lock formatting in Google Sheets?

Partly. You can protect a sheet and leave chosen cells open, but Google states that you cannot both lock cell formatting and let users edit values on the same protected sheet. Use a separate input sheet for typing and protect the formatted report sheet instead.