If you’ve ever watched a formula silently break after copying it to a new row, or had a collaborator accidentally overwrite your carefully built spreadsheet, you already know why cell locking matters. Excel defaults to locking every cell the moment you apply sheet protection, which sounds helpful until you realize you need some cells to stay editable. The good news: Excel gives you precise control once you know where to look.

4 related posts

Default Cell State: All cells locked by default · Primary Shortcut: F4 key · Protection Method: Format Cells > Protection · Alternative Ways: 4 methods · Freeze Panes: Lock rows and columns

Quick snapshot

1Confirmed facts
2What’s unclear
  • Excel Online lacks full F4 functionality
  • Exact version when Command+T was introduced on Mac
  • Apple Numbers still has no keyboard shortcut for $
3Timeline signal
  • F4 has been standard Excel shortcut since pre-2010
  • Microsoft maintains updated Mac protection guides
  • Mobile app equivalents remain limited
4What’s next
  • Selective cell protection for collaboration workflows
  • Range permissions for multi-user sheets
  • Password protection as optional layer
Label Value
Default Setting All cells locked
Enforce Lock Protect worksheet required
F4 Cycles 4 states: relative, col abs, row abs, full abs
Freeze Limit One pane area only
Password Optional for Protect Sheet
Mac Absolute Ref Command + T

How do I lock specific cells in Excel?

Excel’s protection system has two steps that people often conflate: setting the Locked flag and actually enforcing protection. By default, every cell carries the Locked checkbox enabled in the Format Cells dialog, but that flag only takes effect once you apply sheet protection. According to Laptop Mag (Tech publication), most users don’t realize protection is dormant until the Review tab command runs.

Select and format cells

  • Highlight the cells you want to keep editable
  • Right-click and choose Format Cells, or press Ctrl+1 (Windows) / Command+1 (Mac)
  • Navigate to the Protection tab and uncheck Locked
  • Only the cells you explicitly unlock will remain editable after protection activates

Protect the worksheet

  • Go to the Review tab and click Protect Sheet
  • Set a password if you want to prevent others from removing protection
  • Microsoft Support notes that the password is optional — without it, anyone can unprotect the sheet
  • Click OK to apply. Locked cells are now read-only; unlocked cells stay interactive
Bottom line: Cell locking requires two deliberate actions: uncheck Locked on cells you want editable, then protect the sheet to enforce the settings. Protection is not on by default until you trigger it.

How do I lock cells in Excel using F4?

The F4 key is Excel’s fastest way to anchor formula references without typing dollar signs manually. On Windows, pressing F4 while your cursor sits inside a cell reference toggles through four states: relative, column absolute, row absolute, and fully absolute (YouTube Tutorial). This is what makes formulas copy correctly — without locks, cell references shift with each row, breaking calculations that should stay fixed. If you work with currency conversion rates, locking the reference to your rate cell prevents it from drifting when you copy the formula down.

Absolute referencing with dollar signs

  • A relative reference like B4 shifts when copied; $B$4 never changes
  • $B4 locks the column but lets the row move; B$4 locks the row but lets the column move
  • Excel Campus documents that F4 cycles these states in order with each press

Toggle F4 in formulas

  • Type your formula and place the cursor inside any cell reference
  • Press F4 to lock it; press again to cycle to the next state
  • When copying a formula down or across, locked references stay put while unlocked ones adjust
The catch

F4 for absolute referencing works on Windows only. Mac users need Command+T instead — and that only works while you’re actively editing a formula, not after you’ve pressed Enter.

How do I lock rows and columns in Excel?

Freeze panes serves a different purpose than cell protection: it keeps headings visible as you scroll, but it doesn’t prevent editing. When a header row contains labels, locking it in place means you always know what column you’re working in, even when you’re 500 rows down the sheet.

Freeze top row

  • Go to the View tab
  • Click Freeze Panes and select Freeze Top Row
  • Row 1 stays put regardless of how far you scroll down

Freeze first column

  • Same View tab, Freeze Panes menu
  • Select Freeze First Column
  • Column A remains visible as you scroll right

Freeze panes both ways

  • Click the cell below and to the right of the area you want frozen — for example, B2 freezes row 1 and column A simultaneously
  • Then choose Freeze Panes from the menu
  • This creates one locked pane in the top-left corner; all other areas scroll freely
Why this matters

For spreadsheet users handling HKD to SGD exchange rates, freezing panes prevents scrolling past column headers and losing context. One frozen row or column costs nothing and saves constant back-scrolling.

How to make only certain cells editable in Excel?

The standard workflow for collaboration scenarios: lock everything by default, then selectively unlock the cells contributors need to fill in. According to Laptop Mag (Tech publication), this approach prevents accidental overwrites of formulas and structure while giving reviewers a clear entry point.

Unlock editable cells

  • Select the range intended for user input
  • Open Format Cells (Ctrl+1 or Cmd+1) and uncheck Locked on the Protection tab
  • Repeat for any other ranges that need editing access

Protect sheet with exceptions

  • Review tab > Protect Sheet
  • In the protection options dialog, you can also uncheck “Select locked cells” or “Select unlocked cells” to further restrict user interaction
  • The sheet remains protected while only your designated cells accept input
Bottom line: Unlocking specific cells before protecting the sheet is the standard workflow for shared templates, data-entry forms, and collaborative budgets where formulas must stay intact.

Can I lock cells without protecting a sheet?

The Locked checkbox alone does nothing — Excel ignores it until protection is active. There are no native alternatives: without Review > Protect Sheet, cell locks remain dormant. Third-party workarounds exist but involve hiding sheets via VBA or marking them as “Very Hidden,” which falls outside standard Excel workflows.

Use data validation

  • Data validation can restrict what values a user enters, effectively limiting choices without protection
  • Combine with conditional formatting for visual cues on restricted cells
  • This doesn’t prevent editing entirely — it just guides input

VBA alternatives

  • Macros can run on sheet changes to revert unauthorized edits
  • Requires macro-enabled files (.xlsm) and basic VBA knowledge
  • Not practical for most users or shared environments
The paradox

Excel bundles the lock flag with sheet protection by design — there’s no separate “lock and forget” setting. For most users, protecting the sheet is the intended and simplest solution, even if it feels like an extra step.

Upsides

  • Formula references stay fixed when copying with absolute refs
  • Sheet protection prevents accidental edits to structure
  • Freeze panes keeps headers visible without locking content
  • Password protection adds a security layer for sensitive spreadsheets
  • Mac and Windows paths both lead to the same outcomes

Downsides

  • F4 absolute refs don’t work on Mac keyboards
  • Lock flag is ignored without explicit sheet protection
  • Excel Online has limited F4 functionality
  • Apple Numbers has no equivalent keyboard shortcut
  • Mobile apps lack full protection options

The F4 shortcut to lock a reference only works on Windows. If you’re running MAC, use the shortcut: ⌘ + T.

— Spreadsheeto (Excel tutorial site)

By default, when you protect cells in a sheet or workbook, all of the cells will be locked.

— Laptop Mag (Tech publication)

Note: The password is optional. If you do not supply a password, any user can unprotect the sheet.

— Microsoft Support (Official documentation)

For anyone building shared spreadsheets — whether tracking currency conversion rates or managing budget templates — Excel’s locking system balances simplicity with precision. The two-step process (format then protect) rewards consistency: once the habit clicks, protecting formulas and allowing input becomes second nature. The trade-off is real for Mac users, but Command+T closes the gap for formula work, and the protection workflow works identically across platforms.

Related reading: F4 keyboard shortcuts in Excel

Additional sources

excelcampus.com, youtube.com, youtube.com

Beyond locking specific cells, freezing the first row keeps your headers visible while scrolling through large datasets in Excel.

Don't miss these

Frequently asked questions

How to lock cells in Excel with password?

When you protect the sheet via Review > Protect Sheet, a password field appears. Fill it in to require that password for unprotection. According to Microsoft Support, the password is optional — if you leave it blank, anyone can remove protection without credentials.

How to lock cells in Excel formula?

Place your cursor inside the cell reference in a formula and press F4 (Windows) or Command+T (Mac). Each press cycles through: relative → column locked → row locked → both locked. The dollar signs ($) that appear anchor that reference when the formula is copied.

How to lock cells in Excel when scrolling?

Use Freeze Panes from the View tab — this keeps rows or columns visible as you scroll, but doesn’t prevent editing. For actual protection against edits, use Format Cells > Protection > Locked followed by Review > Protect Sheet.

How to lock cells in Excel shortcut?

F4 toggles absolute references in formulas on Windows. On Mac, Command+T does the same while editing a formula. For cell protection shortcuts: Ctrl+1 (Windows) or Command+1 (Mac) opens Format Cells where you toggle the Locked checkbox.

How to lock cells in Excel online?

Excel Online supports the Format Cells and Protect Sheet commands, but keyboard shortcuts differ from desktop. F4 for absolute references has limited support in the web version. Use the ribbon menus for consistent results when locking cells online.

How to lock cells in Excel Mac?

Select cells, press Command+1 to open Format Cells, go to the Protection tab, and ensure Locked is checked. Then go to Review > Protect Sheet. For formula locking with absolute references, use Command+T while editing the formula — F4 does not work on Mac Excel for this purpose.

Why is F4 not locking cells?

F4 toggles absolute references in formulas, not the Locked protection flag. Also, F4 works on Windows only — on Mac it acts as a system special key. If F4 does nothing on a Mac, you may need to enable full function keys in System Preferences > Keyboard. Google Support notes this is a common configuration issue.

How to activate F4 key in Excel?

On Windows, F4 works by default for absolute references. On Mac, use Command+T instead — F4 is intercepted by macOS for system functions like screen brightness. If F4 seems unresponsive on Windows, try pressing it while your cursor is inside an active cell reference in a formula.