$ sign. This guide covers all three, step by step.Here's the thing most tutorials skip: Excel has no single "lock row" button. When someone asks how to lock a row in Excel, they usually mean one of three completely different features — and using the wrong one wastes your time fast.
Freezing a row is a display setting. Your header row stays pinned at the top as you scroll through thousands of data rows. You can still edit every cell freely. Scroll up, click that frozen header cell, and you can delete its contents in one keystroke — freezing never stops that.
Protecting a row is a permission setting. It prevents anyone from modifying, deleting, or typing over specific rows. It requires sheet protection to be enabled. Many users have lost important data because they thought freezing protected their header rows — it doesn't.
Locking a row reference in a formula uses the $ notation — A$1 means the row number won't shift when you copy the formula down. Totally separate from the other two. This is purely about formula behavior, not display or security.
Knowing which one you actually need saves you from chasing the wrong menu entirely. Let's go through each one in detail.
Freezing is the most common interpretation of "locking a row." It keeps a row pinned at the top of your view while the rest of the sheet scrolls normally. You're not preventing edits — you're just keeping that row visible. If you need a deeper dive into the mechanics, check out our full guide on how to freeze a row in Excel.
Row 1 is the most common freeze target — it's usually your header row. Excel has a dedicated option for this one.
Go to View → Freeze Panes → Freeze Top Row. That's it. A thin line appears below row 1, and now it stays put as you scroll down through your data.
Important: this always freezes row 1 specifically. If your headers are in row 3 or your active cell is somewhere else, this option still freezes row 1 — not whatever row you've selected.
Want rows 1 through 3 frozen? The trick is clicking the row below the last row you want locked.
Click anywhere in row 4. Then go to View → Freeze Panes → Freeze Panes (not "Freeze Top Row" — that one only works for row 1). Excel freezes everything above your selected row: rows 1, 2, and 3 are now pinned.
This same rule applies to any number of rows. Want to freeze rows 1–5? Click row 6, then Freeze Panes.
Sometimes you need the header row AND the leftmost column both visible at once. Click the cell that's below and to the right of everything you want frozen.
For example: to freeze rows 1–2 and column A, click cell B3. Then View → Freeze Panes → Freeze Panes. Excel uses that cell as the anchor — everything above it and to the left of it stays pinned.
Go to View → Freeze Panes → Unfreeze Panes. This removes all freezes at once. There's no way to unfreeze specific panes individually — it's all or nothing.
On Mac, the View menu is in the same location. The commands are identical: View → Freeze Panes → Freeze Top Row (or Freeze Panes for multiple). The keyboard shortcut is Command+1 for Format Cells, but there's no native shortcut for Freeze Panes on Mac.
Excel Online follows the same pattern: View → Freeze Rows and Columns. The options are slightly simplified but functionally the same.
Protecting a row prevents changes — no typing, no deleting, no formatting. This is the real "lock" if you're trying to stop data entry staff from accidentally overwriting your header row or key formula rows.
The process has three steps, and step 1 trips up almost everyone. You also might want to review how to lock cells in Excel for the full context on how cell protection works.
By default, every cell in Excel has the "Locked" property checked. But this setting does nothing until you enable sheet protection. The counter-intuitive part: before protecting your specific rows, you need to unlock everything else.
Press Ctrl+A to select all cells. Then press Ctrl+1 to open Format Cells. Go to the Protection tab. Uncheck Locked. Click OK.
Now everything is unlocked. Don't panic — no protection is active yet. You're just resetting the slate.
Select the specific row or rows you want to protect. Click the row number on the left to select the entire row, or drag to select multiple rows.
Press Ctrl+1 again → Protection tab → check Locked. Click OK.
Now just those rows have the Locked property — everything else is still unlocked.
Go to Review → Protect Sheet. You can set a password (optional but recommended if you're sharing the file). Click OK.
Now your protected rows can't be edited. Clicking them shows a warning. Everything else on the sheet remains editable normally.
The most common use case: lock row 1 so data entry staff can't accidentally delete or overwrite your column headers.
Result: row 1 is read-only. All other rows accept edits as normal.
Need to protect rows 1 through 5 — say, a header block plus some configuration rows?
All five rows are now protected. Users can still type anywhere below row 5.
Protected sheets disable AutoFilter and Sort by default. To keep these working:
Now the protected rows can't be edited, but users can still sort and filter the data below.
The process is identical on Mac. Use Command+1 instead of Ctrl+1 to open Format Cells. The Protection tab is in the same location. Go to Review → Protect Sheet just like Windows.
One difference: Mac Excel doesn't have a keyboard shortcut for Review → Protect Sheet, so you'll use the menu each time.
Plenty of people try to "freeze" a row to prevent editing — it doesn't work. Freezing only affects what you see. The row is still completely editable. If you scroll up and click a frozen header cell, you can delete it instantly. This is probably the single most common Excel misconception.
Conversely, protecting a row has zero effect on scrolling. Your data still disappears off-screen as you scroll down — protection doesn't pin anything to the screen. A fully protected worksheet still scrolls normally and shows no indication that rows are protected until you try to edit them.
If you need both — a visible, uneditable header row — you have to do both steps independently. Freeze first (View → Freeze Panes), then protect (three-step process above). They don't interfere with each other. In fact, frozen panes work exactly the same on protected and unprotected sheets.
One more thing: protection only prevents manual edits through the normal Excel interface. Macros (VBA) can still modify protected cells unless you explicitly lock them against VBA too using Worksheet.Protect UserInterfaceOnly:=True. For most office use cases this doesn't matter, but it's worth knowing if you're sharing files with people who run macros regularly.
Another edge case worth knowing: if you share a workbook (Review → Share Workbook), protection behavior can change unpredictably. Microsoft recommends handling protection before enabling workbook sharing, not after.
This one's different from both freezing and protecting — it's entirely about how formulas behave when copied. Learning Excel formulas deeply means understanding this notation, because it unlocks massive spreadsheet efficiency.
When you copy a formula down a column, Excel automatically adjusts all cell references relative to where the formula moved. Copy =A1*B1 one row down and it becomes =A2*B2. Usually that's exactly what you want — each row calculates using its own data.
But sometimes you need one reference to stay fixed. Maybe row 1 has a tax rate, an exchange rate, or a conversion factor that every formula in the column needs to reference. You don't want that reference shifting as you copy down — if it shifts, row 2's formula suddenly references B2 instead of B1, and your entire calculation falls apart silently.
Put a $ before the row number to lock it: A$1 means "always row 1, but the column can shift." Copy this formula anywhere down the column and the row stays 1 every time.
Put $ before both: $A$1 locks both row and column — that reference never moves no matter where you copy or paste the formula. Use this for truly fixed values like a tax rate or discount percentage.
$A1 locks only the column — the row shifts normally as you copy down. Useful when copying formulas across multiple columns.
The fastest way to cycle through these options: click inside a cell reference in your formula bar and press F4. Each press cycles through A1 → $A$1 → A$1 → $A1 → back to A1. You don't have to type the $ manually at all.
Say you have a tax rate in cell B1 and prices in column A starting at row 2. To calculate the after-tax price for each row, enter =A2*(1+B$1) in C2. Copy this formula down through C100 and every row adds the correct tax — the B$1 reference stays locked at row 1 while A2 shifts to A3, A4, and so on automatically. Without the $ lock, row 3 would reference B2, row 4 would reference B3, and every calculation below row 2 would be wrong.
This row-locking technique is one of the highest-leverage Excel skills. Once you understand it, you'll use it in nearly every non-trivial spreadsheet.
Once you've enabled sheet protection, Excel gives you granular control over exactly what users can and can't do. In the Protect Sheet dialog, you'll see a list of allowed actions — most are unchecked by default.
You can allow users to select locked cells (just read them, not edit), select unlocked cells, format cells, insert/delete rows, use AutoFilter, use PivotTables, and more. This lets you build forms where certain fields are editable and others are locked permanently. The Protect Sheet dialog has 14 different permission checkboxes — most people only notice the password field and miss the fine-grained controls entirely.
Another useful combination: protect the sheet but leave specific input cells unlocked. Users fill in their data, but they can't touch formulas, headers, or calculated fields. This is how most Excel-based forms work in professional settings. A data entry template might have 50 columns where only 8 are editable — the rest are locked formulas that calculate automatically from the user's input.
You might also find it useful to wrap text in Excel for those header rows before locking them — wrapped text in headers displays fully without column resizing. And if your data has duplicate entries you need to find, protect your formula columns first so the conditional formatting rules that highlight duplicates don't get accidentally deleted or overwritten by users.
For more complex data entry scenarios, combining protection with drop-down lists in Excel is a powerful combination — the dropdown constrains what users can type, while row protection prevents them from clearing or overwriting the dropdown validation cell itself. Without row protection, a user can simply delete the dropdown cell and type anything they want.
If you remove sheet protection later, remember that all your "Locked" cell property settings are preserved. The cells don't become unlocked — they just lose the enforcement. Re-enable protection and those settings instantly apply again. This means you can toggle protection on and off without re-doing the setup each time.
A few issues come up repeatedly when people try to lock rows in Excel.
Freeze Panes is grayed out. This happens when a cell is in edit mode (you can see the cursor blinking inside the cell). Press Escape first to exit edit mode, then try Freeze Panes again. It also gets grayed out in Page Layout view — switch to Normal view (View → Normal) and the option returns.
Protect Sheet is grayed out. This usually means the workbook structure is protected, not the sheet. Go to Review → Protect Workbook. If it's active, you'll need to unprotect the workbook first, then protect the individual sheet.
Can't find Unfreeze Panes. "Unfreeze Panes" only appears in the Freeze Panes menu when frozen panes are actually active. If you don't see it, your sheet has no frozen panes — the menu will show "Freeze Top Row" and "Freeze First Column" instead.
Protected cells are still editable after protection. This means step 1 was skipped — you set the Locked property without first unlocking everything. Go to Review → Unprotect Sheet, then restart the three-step process: Ctrl+A → unlock all → select your rows → lock them → re-protect.
Formula $ not sticking after copy-paste. Regular paste (Ctrl+V) preserves the $ locks. But if you're using paste special and pasting only values, the formula is gone entirely — you're pasting the result, not the formula. Use Ctrl+V or paste formulas specifically to keep your $ references intact.
Frozen rows not printing on every page. Freeze Panes controls the screen view only. To repeat rows at the top of every printed page, go to Page Layout → Print Titles → Rows to repeat at top. Select your header row there. This setting is completely separate from Freeze Panes and must be configured independently.
AutoFilter disappeared after protecting the sheet. By default, Protect Sheet disables the filter dropdowns. To fix it without unprotecting: go to Review → Unprotect Sheet → Review → Protect Sheet → check "Use AutoFilter" → OK. Your row protection stays in place.