How to Lock Rows in Excel: Freeze Headers, Protect Totals, Sort Without Scrambling the Ledger

How-to · Updated July 2026 · ~13 min read

"Lock rows" means two different things in Excel, and most guides pick one and ignore the other. There is locking rows on screen — freezing the header so it stays visible while you scroll through 4,000 lines of general ledger — and locking rows against change — protecting a totals row so a client or a stray keystroke cannot overwrite the formulas. Different mechanisms, different ribbon tabs, and neither does the other's job: a frozen row edits freely, and a protected row scrolls away like any other.

A third problem hides under the same phrase: keeping the header row out of a sort, which is where the two meanings collide — freezing does nothing to a sort, and protection can block sorting entirely if set up the wrong way round. This guide covers all of it on one example — a trial balance with a header row, a data block and a totals row — plus printing the header on every page and undoing the lot.

The four jobs, and which tool does each

Four jobs, four features. Everything below is one of these.

Freeze rows so headers stay visible

Take a general ledger export — say the one QuickBooks Online gives you, with Date, Transaction Type, Num, Name, Memo, Account, Debit, Credit down thousands of rows (how to get that export cleanly). By row 60 the header has scrolled off and column G is just a number with no name. Freezing fixes exactly this and nothing else.

Freeze the top row

1. Go to the View tab > Window group > Freeze Panes dropdown.
2. Click Freeze Top Row.

A thin line appears under row 1, which now stays put while everything below scrolls. One catch trips people constantly: Freeze Top Row freezes the top visible row, not necessarily row 1. If you have scrolled down so row 14 is at the top of the window, row 14 is what gets frozen. Press Ctrl+Home first.

Freeze several rows, or rows and columns together

Freeze Top Row only handles the single-row case. GL exports and trial balances often carry a report title in rows 1–2 and the real column headers in row 3, and you usually want the account-name column pinned too. For that, use the general command:

1. Click the cell immediately below the rows and immediately right of the columns you want frozen. To freeze rows 1–3 and column A, click B4.
2. View > Freeze Panes > Freeze Panes.

Everything above and to the left of the selected cell is now pinned. Select a cell in column A (say A4) and only rows freeze; select a whole row first and it behaves the same way. Freeze First Column on the same dropdown is the one-click version for column A alone.

Three things worth knowing about how freezing behaves:

• It is per worksheet and saves with the file — whoever opens the workbook next inherits it.
• Ctrl+Home changes meaning. With panes frozen it jumps to the first unfrozen cell (B4 above), not A1 — by design, and what you want in a ledger.
• You can only freeze from the edges. Frozen rows are always the top rows, frozen columns the leftmost. There is no freezing row 50 mid-sheet; to see two distant regions at once, use View > Split, which gives independently scrollable panes.

If Freeze Panes is greyed out, you are almost certainly in Page Layout view, where Excel disables it — switch back via View > Normal. It is also unavailable while you are mid-edit in a cell; press Enter or Esc first.

The sort trap: freezing protects nothing

Here is the failure that fills the forums. You freeze row 1, feel like the header is "locked", select a column, hit the A–Z button on the Data tab — and the header is now sorted into the middle of the data, Debit filed alphabetically between Deferred revenue and Directors loan.

Freezing is a view setting; the sort engine neither knows nor cares about it. What decides whether your header survives a sort is Excel's header detection, and it is a guess:

• The quick A–Z / Z–A buttons sort the current region — the block of cells around your selection, bounded by fully blank rows and columns — and silently guess whether the first row is a header by comparing its formatting and data type to the rows below.
• The guess fails predictably: a header row formatted like the data, headers that are numbers or dates (period columns like 2024, 2025), or a second title row above the real header.
• A blank row between header and data breaks the current region, so the sort excludes the header and everything above the blank — which sounds safe until a blank row inside the data means half your ledger did not get sorted at all. If the export is riddled with blank spacer rows, clean those first (cleaning messy data in Excel covers the fast ways).

To sort a plain range without gambling:

1. Select the entire data block including the header row — click inside it and press Ctrl+A, then check the selection edges.
2. Data tab > Sort & Filter group > Sort (the big dialog, not the A–Z buttons).
3. Confirm My data has headers is ticked, top right of the dialog. When it is, the Sort by dropdown shows your column names; when it is not, it shows Column A, Column B. That dropdown is your visual proof of what Excel thinks the header is.
4. Choose the column and order, then OK.

That works every time, but it depends on you doing it every time. The structural fix is better.

Convert to a Table: the sort-safe structure

Select any cell in the data block and press Ctrl+T (or Insert tab > Table). Confirm My table has headers in the dialog and click OK. This one change retires the whole category of sort accidents:

• The header row is never sorted. A Table knows which row is the header — it is not a guess re-made on every sort. Sort from the filter dropdowns, the Data tab, or right-click; the header does not move.
• The header stays visible without freezing. Scroll down with a cell inside the Table selected and the column letters at the top of the grid become your header names — Account, Debit, Credit instead of F, G, H. Still freeze (this only shows while a Table cell is selected), but even unfrozen, a Table never leaves you guessing which column is which.
• New rows join automatically. Type in the row below the Table and it expands to include it — no more sorting a range that silently excluded last week's additions.
• A proper totals row. Table Design tab > tick Total Row. It uses SUBTOTAL(109, ...) under the hood, which ignores rows hidden by a filter — the total tracks what is visible, and it stays at the bottom through every sort instead of being a formula parked under a range that keeps growing past it.

If the layout will feed a report — a trial balance rolling up into a P&L, for instance — the Table is also the right base for SUMIFS, as laid out in building a P&L in Excel.

One warning before the next section: Tables and sheet protection fight each other. On a protected sheet a Table generally cannot expand — typing below it will not add a row, even with row insertion allowed in the protection dialog. Behaviour here has varied a little between Excel versions, so test it on the build you actually ship from; the safe assumption is that protecting a sheet holding a Table freezes the data in size.

Lock rows against edits: Locked cells + Protect Sheet

Now the other meaning of "lock". You are sending the trial balance to a client for review, or handing a schedule to a junior to fill in: the data area should be editable, the header, formula column and totals row should not. Two facts explain everything about how this works:

1. Every cell in Excel is already marked Locked. Select any cell, press Ctrl+1, open the Protection tab — the Locked box is ticked by default on every cell of every new sheet.
2. Locked does nothing until you protect the sheet. The tick is dormant. Turning on Protect Sheet is what enforces it.

So the workflow is always: unlock what should stay editable, leave locked what should not, then switch protection on.

Step by step: protect the totals and formulas only

1. Select the whole sheet — Ctrl+A twice, or click the triangle above row 1.
2. Ctrl+1 > Protection tab > untick Locked > OK. Everything is now editable-when-protected.
3. Select what must not change: click the header row's number, then Ctrl-click the totals row's number to add it. To catch every formula on the sheet in one move, use Home > Find & Select > Go To Special > Formulas > OK — it selects exactly the formula cells and nothing else.
4. Ctrl+1 > Protection tab > tick Locked > OK. Tick Hidden as well if the formulas themselves should not show in the formula bar while protected — useful when the workbook carries rates or workings the recipient does not need to see.
5. Review tab > Protect group > Protect Sheet.
6. In the dialog, choose what recipients may still do. The two selection boxes are ticked by default; the useful extras for a ledger are Sort, Use AutoFilter, and Format cells. Add a password if you want one (see below for what it is worth), OK, and re-type it.

Now the data area edits normally, and typing into the totals row gets the standard block: "The cell or chart you're trying to change is on a protected sheet." Row deletion is blocked for any row containing a locked cell, which is precisely what you want for headers and totals.

The sort-on-a-protected-sheet trap

Ticking Sort in the Protect Sheet dialog looks like it settles the sorting question. It does not, and this is one of the most common support questions about protection: in current versions, users cannot sort a range that contains locked cells, even with Sort allowed. If you skipped step 2 and left the data area locked, sort attempts on the protected sheet fail with a protection error, Sort tick or no Sort tick.

Two clean ways out:

• Unlock the data block (the workflow above already does this) and lock only the header, formulas and totals. Sorting the unlocked data area then works — and the locked header is excluded from the damage automatically, because it cannot be moved. This is the rare case where protection solves the sort-scrambling problem rather than causing one.
• Review > Allow Edit Ranges (set up before protecting) defines named ranges users may edit — with an optional per-range password — while the rest of the sheet stays locked. It is the tool for the "accruals column only" kind of brief.

Beyond one sheet: Review > Protect Workbook protects the workbook's structure — no adding, deleting, renaming, hiding or moving sheets. It does not lock any cell; pair it with sheet protection when the deliverable has a tab-per-entity layout that must not be rearranged.

What sheet protection is actually worth

Be honest about the threat model. Sheet protection stops accidents, tidies the Tab-key path (with locked-cell selection unticked, Tab hops straight between the unlocked input cells), and signals intent. It is not security: sheet-protection passwords are stored as a weak hash inside the file rather than encrypting anything, widely available tools remove them, and anyone who can open a protected sheet can copy its values out. Protect sheets to stop a reviewer breaking formulas; to keep data confidential, use file-level encryption (File > Info > Protect Workbook > Encrypt with Password) — a genuinely different mechanism — or do not send the file.

Print titles: the header on every printed page

Freezing is a screen feature; it does nothing on paper or in a PDF. Print a 14-page ledger and pages 2–14 have no column headings unless you set Print Titles:

1. Page Layout tab > Page Setup group > Print Titles.
2. On the Sheet tab of the dialog, click into Rows to repeat at top, then click row 1's header (or type $1:$1; $1:$3 for a three-row header).
3. Columns to repeat at left does the same for the account-name column on wide sheets. OK, then check File > Print preview — every page now carries the header.

If the boxes are greyed out, you opened Page Setup from the wrong door: the dialog reached via File > Print > Page Setup disables the repeat boxes. Set them from the Page Layout tab instead. They are also disabled while multiple sheets are group-selected.

How HISAB 360 helps

HISAB 360 is a paid Excel add-in for Windows desktop Excel that puts an AI assistant in a chat panel inside the workbook — and this article's checklist is exactly the kind of finishing work it does without being asked. When its AI builds a financial report — an aging analysis, a set of financial statements, a reconciliation summary — it delivers the sheet with the header rows already frozen, so the deliverable scrolls properly from the first open.

Much of the rest of the checklist is a prompt rather than a ribbon hunt: ask for formatting and structure changes on the open workbook and it applies them to the live file — you watch the sheet change rather than copy instructions out of a chat window. Because it pulls invoices, bills and general ledger straight from QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks or Sage Accounting into sheets, the exports this article keeps using as examples can arrive already structured instead of as raw CSV.

Honest limits: Windows desktop Excel only (Microsoft 365 / 2016+), not Mac, not Excel on the web, and it is paid — a 15-day free trial with 50 AI credits and no card, then a subscription or perpetual licence. Protection passwords remain yours to choose and remember; no add-in changes what sheet protection is fundamentally worth.

Undoing it all

• Unfreeze: View > Freeze Panes > Unfreeze Panes — the option replaces Freeze Panes on the dropdown whenever a freeze is active.
• Unprotect: Review > Unprotect Sheet, plus the password if one was set. A lost protection password is an inconvenience rather than a catastrophe, for the reasons above — but for an encrypted file password there is no such escape, so treat those with respect.
• Un-Table: select a cell in the Table > Table Design > Convert to Range. Formatting stays; the structural behaviour (auto-expand, safe sort, structured references) goes.

The habit worth building, for any workbook that leaves your machine: structure the data as a Table, freeze the header, lock only what must not change, protect, set print titles, send. A two-minute routine, and the difference between a workbook that survives a client's review and one that comes back with #REF! in the totals row. It belongs alongside the rest of the fundamentals in Excel skills for accountants.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.