EXCEL + GOOGLE SHEETS HOW-TO
How to Lock Cells in Excel and Google Sheets (and Protect Formulas)
"Lock cells" means three different things: stopping edits, keeping rows visible while you scroll, and fixing a reference inside a formula. This guide covers all three in both apps, with the exact menus, shortcuts and the traps to avoid.
START HERE
Which "lock" do you actually need?
Most searches for "how to lock cells" are really one of these three jobs. They use different features, so pick the right one first.
$B$1 doesn't shift when you copy the formula. Shortcut: F4.
MICROSOFT EXCEL
Excel: protect formulas but allow input
Here is the part that confuses everyone: in Excel every cell is already "Locked" by default, but the setting does nothing until you protect the sheet. So the workflow is backwards: first unlock the cells people should type in, then protect the sheet.
Select the input cells
Select every cell users are allowed to change: amounts, dates, names, categories. Hold Ctrl (⌘ on Mac) to select ranges that aren't next to each other.
Unlock them
Press Ctrl+1 (⌘+1 on Mac) to open Format Cells, go to the Protection tab and clear Locked. Click OK.
Protect the sheet
Go to Review › Protect Sheet. Keep "Select locked cells" and "Select unlocked cells" ticked, and tick anything else users need, such as Sort, Use AutoFilter or Format columns.
Decide on a password
A password is optional. If you set one, store it safely: Microsoft states it cannot retrieve a forgotten worksheet password.
Test like a user
Try typing in a formula cell (Excel should refuse) and in an input cell (it should work). Press Tab: on a protected sheet it jumps between unlocked cells, which makes data entry faster.
| Task | Excel for Windows | Excel for Mac | Excel for the web |
|---|---|---|---|
| Unlock input cells | Ctrl+1 › Protection › clear Locked | ⌘+1 › Protection › clear Locked | Review › Manage Protection › Unlocked ranges |
| Protect the sheet | Review › Protect Sheet | Review › Protect Sheet | Review › Manage Protection › Protect sheet |
| Hide formulas | Format Cells › Protection › Hidden, then protect | Format Cells › Protection › Hidden, then protect | Set in the desktop app |
| Let specific people edit a range | Review › Allow Edit Ranges | Not in the same form | Unlocked ranges with an optional password |
Menu names follow Microsoft 365 as of September 2026. Older versions and the web app can place commands differently.
GOOGLE SHEETS
Google Sheets: lock cells from editing
Google Sheets works the other way round from Excel. Nothing is protected until you create a protected range or sheet, and editing rights are tied to people with access to the file.
Open the protection panel
Go to Data › Protect sheets and ranges, then click Add a sheet or range.
Protect the whole sheet, except inputs
Choose Sheet, pick the tab, then tick Except certain cells and add your input ranges. This is the Sheets version of "protect formulas, allow input", and it's easier to maintain than protecting formula ranges one by one.
Choose warning or restriction
Click Set permissions. Show a warning still allows edits but asks people to confirm, which is ideal for your own files. Restrict who can edit blocks everyone except the people you choose.
Check it with a second account
You won't see the restriction as the owner, because owners can always edit. Open the file as an editor (or ask a colleague) to confirm the formula cells are really locked.
Shared team files
Restrict formula and settings tabs to yourself, leave data-entry cells open to editors, and use warnings on anything that's easy to change by accident.
What protection can't do
Sheets protection has no password, can't lock formatting while leaving values editable, and Google itself says it shouldn't be used as a security measure: people with access can still copy, download or print the data.
IMPORTANT
Cell protection is not security
Microsoft and Google say the same thing: sheet protection prevents accidental changes, it doesn't keep data private. Choose the tool that matches your goal.
| Goal | Use | Where |
|---|---|---|
| Stop accidental edits to formulas | Sheet protection with unlocked input cells | Excel: Review › Protect Sheet · Sheets: Data › Protect sheets and ranges |
| Stop people adding, deleting or renaming tabs | Workbook structure protection | Excel: Review › Protect Workbook |
| Stop anyone opening the file without a password | File encryption | Excel for Windows: File › Info › Protect Workbook › Encrypt with Password · Excel for Mac: File › Passwords |
| Control who can see a Google Sheet | Sharing permissions | Share button: viewer, commenter or editor, or remove access |
LOCK WHEN SCROLLING
How to lock rows and columns when you scroll
If you want the header row to stay on screen, you don't need protection. You need Freeze. It changes only your view and doesn't stop anyone editing.
| What to keep visible | Excel | Google Sheets |
|---|---|---|
| Top row | View › Freeze Panes › Freeze Top Row | View › Freeze › 1 row |
| First column | View › Freeze Panes › Freeze First Column | View › Freeze › 1 column |
| Several rows and columns | Select the cell below and to the right of the area, then View › Freeze Panes › Freeze Panes | Click a cell, then View › Freeze › Up to row N and Up to column X |
| Undo it | View › Freeze Panes › Unfreeze Panes | View › Freeze › No rows / No columns |
LOCK A CELL IN A FORMULA
Lock a reference with $ (absolute references)
When you copy a formula, Excel and Google Sheets shift its references. That's usually helpful, until a tax rate in B1 becomes B2, B3 and so on. A dollar sign stops the shift: $ before the column letter locks the column, $ before the row number locks the row.
| Reference | Type | Copied 2 rows down and 2 columns right | Typical use |
|---|---|---|---|
$A$1 | Absolute | $A$1 | A single rate, target or setting used everywhere |
A$1 | Mixed: row locked | C$1 | A header row that each column reads from |
$A1 | Mixed: column locked | $A3 | A label or value column that each row reads from |
A1 | Relative | C3 | Row-by-row calculations |
Shortcut: click inside a reference in the formula bar and press F4 to cycle A1 → $A$1 → A$1 → $A1. In Excel for Mac use ⌘+T; F4 also works in Google Sheets (on some Mac keyboards you'll need Fn+F4).
TRY IT
What happens to my reference when I copy the formula?
Enter a reference, choose which part to lock, and say how far you copy the formula. Negative numbers copy up or to the left.
The same rules apply in Excel and Google Sheets.
LIFEHACKS
Six habits that make locked sheets easy to use
- ✓Color the input cellsOne light fill for every editable cell tells users where to type before they hit a protection message.
- ✓Protect after the final testUnprotect, make changes, re-protect. Protection gets in your own way while you're still building.
- ✓Add data validation to inputsUnlocked cells still accept anything. A dropdown or number rule keeps inputs clean (Data › Data Validation in both apps).
- ✓Put settings on one tabRates, categories and targets in a single Settings tab, referenced with $, are easier to protect and update.
- ✓Keep an unprotected masterSave an editable master copy and hand out protected copies, so a lost password never locks you out of your own work.
- ✓Write a one-line noteA short "Type in green cells only" line at the top of each tab prevents most support questions.
WHEN IT DOESN'T WORK
Troubleshooting locked cells
| Problem | Likely cause | Fix |
|---|---|---|
| I "locked" cells in Excel but can still edit them | Locked only takes effect once the sheet is protected. | Review › Protect Sheet. |
| Nobody can type anywhere after protecting | All cells were still Locked (the default). | Unprotect, unlock the input cells with Ctrl+1, protect again. |
| Protection in Google Sheets doesn't stop me | You're the owner, or the range uses "Show a warning". | Test with an editor account; switch to "Restrict who can edit". |
| Sort and filter stopped working | Those options weren't allowed when protecting. | In Excel, unprotect, apply the filter first, then re-protect with Sort and Use AutoFilter ticked. |
| My $ reference still moved | Only one part was locked (A$1 or $A1), or rows were inserted above it. | Use $A$1. Inserting rows or columns updates even absolute references, which is expected behavior. |
| Forgot the Excel sheet password | Microsoft can't recover it. | Use your unprotected master copy. Keep one for every file you protect. |
READY-MADE TEMPLATES
Prefer a spreadsheet that's already set up?
Our templates for Excel and Google Sheets separate input cells from calculated ones. If you sell spreadsheets yourself, protect the formulas before delivery and read our guide to selling PLR spreadsheets on Etsy.
FAQ
Locking cells: questions
How do I lock cells in Excel so they can't be edited?
Unlock the cells people may edit (Ctrl+1 › Protection › clear Locked), then protect the sheet with Review › Protect Sheet. All cells that are still Locked can no longer be changed.
How do I protect formulas but still allow input?
In Excel, unlock only the input cells and protect the sheet. In Google Sheets, protect the sheet via Data › Protect sheets and ranges and tick "Except certain cells" for the input ranges.
How do I lock cells in Google Sheets?
Go to Data › Protect sheets and ranges › Add a sheet or range, select the range or sheet, then Set permissions to show a warning or restrict who can edit.
How do I lock a row so it stays visible when scrolling?
Use Freeze: in Excel, View › Freeze Panes › Freeze Top Row; in Google Sheets, View › Freeze › 1 row. Freezing affects only the view.
How do I lock a cell in a formula?
Add dollar signs to make it an absolute reference, such as $B$1, or press F4 while the cursor is in the reference (Command+T in Excel for Mac). The reference then stays the same when the formula is copied.
Is sheet protection secure?
No. Microsoft and Google both describe it as a way to prevent accidental changes, not as a security feature. To keep a file private, use Excel file encryption or Google Sheets sharing permissions.
Sources and further reading
- Microsoft Support: Lock or unlock specific areas of a protected worksheet
- Microsoft Support: Protect a worksheet
- Microsoft Support: Freeze panes to lock rows and columns
- Microsoft Support: Switch between relative, absolute and mixed references
- Google Docs Editors Help: Protect, hide, and edit sheets
- Google Docs Editors Help: Keyboard shortcuts for Google Sheets
Menus and shortcuts checked September 2026 in Microsoft 365 and Google Sheets documentation. Interfaces change; if a menu has moved, search the app's help for the feature name.
