Group and Collapse Rows in Excel: Build a P&L That Expands on Click

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

Your management pack has an 84-row P&L. The finance director wants one page. The auditor wants every nominal account. The operations manager wants overheads exploded but nothing else. So you keep three versions of the same statement, and by the second month one of them is wrong.

Row grouping solves this properly: one sheet, one set of numbers, and +/- buttons down the left margin that fold detail accounts into their section totals. Click a numbered level button and the statement snaps between summary and full detail in a keystroke. It is a two-second job, and most people reach for Hide Rows instead — which is the version that gets queried in review.

This post covers the shortcut, collapsing and outline levels, nested groups, Auto Outline, Data > Subtotal for raw ledger extracts, and the SUBTOTAL function underneath it — all against one worked P&L, plus the one function-number choice that silently zeroes a collapsible report.

The worked example

Everything below refers to this sheet. Detail accounts sit under each section heading; the bold rows are formulas.

Three blocks of detail to fold away: rows 4:6, 10:12 and 17:23. Building the statement itself is covered separately in building a P&L in Excel.

Group rows: the two-second version

1. Click row header 4 and drag to row header 6 so all three rows are selected edge to edge.
2. Press Shift+Alt+Right Arrow.

That is it. An outline pane appears to the left of the row numbers: a vertical bracket spanning rows 4 to 6, a minus button sitting at row 7, and small numbered buttons 1 and 2 at the top.

The ribbon route is Data tab > Outline group > Group (Alt, A, G, G); on a narrow window that group collapses into a single Outline dropdown at the right-hand end of the tab. Ungroup is Shift+Alt+Left Arrow.

Two things decide whether this works first time.

Select entire rows, not cells. If you select B4:B6 and press the shortcut, Excel cannot tell what you meant and opens a small Group dialog with two radio buttons, Rows and Columns. Choose Rows and you get the same result. This is also the honest answer to "how do I group cells in Excel": you cannot. Excel groups whole rows or whole columns and nothing in between — an outline is a property of the row, not of a range.

Leave the total row outside the group. Group 4:6 and the total in row 7 stays on screen when the detail folds. Group 4:7 and the total disappears with everything else, leaving a section heading followed by nothing. The rule holds for every block: group the detail, exclude the line that sums it.

Repeat for the other two blocks — select rows 10:12, Shift+Alt+Right; select 17:23, Shift+Alt+Right. Thirty seconds for the whole statement.

Columns work identically. On a 12-month P&L, grouping Jan:Mar next to the Q1 column gives a statement that folds from months to quarters horizontally as well as from accounts to sections vertically.

Collapse, expand and the level buttons

The minus button at row 7 folds rows 4:6 and turns into a plus. Click it again to expand. That is what your reader will actually use.

The numbered buttons at the top of the pane are the ones worth learning — they act on the whole sheet at once:

Keyboard equivalents, with the cursor anywhere inside a group: Alt, A, H hides detail, Alt, A, J shows it. And Ctrl+8 toggles the outline symbols themselves — useful when you want the collapsed layout without the pane eating your left margin, and the reason a colleague may swear there is no outline on a sheet that has one.

Two behaviours to know before circulating the file. The collapse state saves with the workbook, so whatever level you leave it on is what the recipient opens into. And collapsed rows do not print — level 1 plus Ctrl+P is your summary pack, level 2 the supporting schedule, from one sheet with no second version to keep in step.

Where the +/- button lands

The minus button appears at row 7 because Excel assumes summary rows sit below their detail. If your layout puts totals above the detail, the button lands in the wrong place and the wrong row folds away.

Fix it once per sheet: click the dialog launcher arrow at the bottom-right of the Outline group on the Data tab. The Settings dialog has two checkboxes, Summary rows below detail and Summary columns to right of detail. Untick the first for a totals-above layout, click OK, and existing groups re-anchor. The setting is per worksheet, and it also governs how Auto Outline reads the sheet.

Nested groups: subtotals inside sections

Seven overhead accounts is too long for a summary page, but "Total overheads" alone hides the payroll split everybody asks about. Nesting gives you both. Insert a subtotal row so salaries (17) and employer NI (18) roll into Total staff costs in row 19, pushing rent through other overheads into rows 20:24 and Total overheads to row 25.

Group rows 17:18 (inner), then group rows 17:24 (outer). A second bracket appears outside the first and the level buttons become 1, 2, 3:

Excel supports up to eight outline levels (the documented limit as of 2026); three is usually the practical ceiling before a pack becomes a puzzle.

One trap with multiple groups on a sheet: two adjacent groups with no row between them merge into one. Group 4:6 then 7:9 and you get a single bracket over 4:9. In the P&L this never bites — the total rows separate every pair of blocks. In a schedule with no total rows, leave a blank row between blocks.

Auto Outline: let Excel read your formulas

If the totals are genuine formulas rather than hard-keyed numbers, Excel builds the outline itself. Click any cell in the statement, then Data tab > Group dropdown > Auto Outline (Alt, A, G, A) — on the P&L above it produces the same three groups in one command.

It works by tracing which cells each summary formula refers to, so it needs:

• Real formulas in the total rows. =SUM(B4:B6) outlines; a pasted 800,000 does not.
• A consistent direction. Every total below its detail, or every total above it, matching the Settings checkbox above. A sheet that mixes both confuses it.
• Detail that is contiguous. SUM ranges with gaps or cross-sheet references give patchy results.

When it cannot find that structure, Excel refuses with "Cannot create an outline." That message is diagnostic: it usually means a section total is hard-keyed or points at the wrong range — worth knowing whether or not you wanted an outline.

Auto Outline also groups columns where columns carry summary formulas — a Q1 column that sums Jan to Mar gets its own bracket. On a monthly P&L that is a bonus; if you only wanted rows, run Ungroup dropdown > Clear Outline and do it by hand. Clear Outline is also the reset before re-running Auto Outline over a changed layout.

Data > Subtotal: outlines from a raw ledger extract

Grouping assumes a structured statement. Often you have the opposite: a flat transaction dump, one row per posting, no totals anywhere — the shape of a general ledger export. Data > Subtotal turns that into a grouped, collapsible report in one dialog.

1. Sort first, by the column you want to break on: Data tab > Sort. Non-negotiable — Subtotal inserts a break at each change in the value, so unsorted data gives you a subtotal every few rows and a report worse than the raw list.
2. Click any single cell inside the list. Do not select the whole range.
3. Data tab > Outline group > Subtotal (Alt, A, B).
4. In the dialog: At each change in — Account. Use function — Sum. Add subtotal to — tick your Amount column and untick everything else. Excel pre-ticks the rightmost column, which is usually a date or a reference and produces nonsense.
5. Check Replace current subtotals and Summary below data; tick Page break between groups only if you want one account per printed page.
6. OK.

Excel inserts a labelled row after each account ("Materials Total"), a Grand Total at the bottom, and a three-level outline: 1 for the grand total, 2 for account totals, 3 for every transaction.

To nest, sort by Category then Account, run Subtotal on Category, then run it again on Account with Replace current subtotals unticked. You get four levels: grand total, category, account, transactions.

Three things that catch people out:

• The Subtotal button is greyed out on an Excel Table. Click inside the table, Table Design > Convert to Range, confirm, then run Subtotal.
• Removing it is one click: re-open the dialog and press Remove All. Deleting rows by hand is how you lose transactions.
• The inserted rows are real rows with real formulas — the practical advantage over a PivotTable, which summarises more flexibly but produces a layout you cannot type into. The trade-off is set out in summarising data in Excel; note that a PivotTable has its own separate grouping feature and its own refresh behaviour.

SUBTOTAL(9,...) — and the 109 that zeroes your report

Look at what Subtotal actually wrote into the total rows: =SUBTOTAL(9,C2:C15), not =SUM(C2:C15). The difference matters enough to use it in hand-built statements too.

SUBTOTAL ignores other SUBTOTAL results inside its own range. So a grand total of =SUBTOTAL(9,C2:C500) over a column that already contains a dozen account subtotals returns the sum of the transactions once, not twice. Plain SUM over the same range double-counts everything and foots to exactly twice the right answer.

The first argument is the function number:

Both families ignore rows hidden by a filter. The difference is rows hidden any other way — and collapsing an outline group hides rows in exactly that sense. Put =SUBTOTAL(109,B4:B6) in a total row, click level 1, and the figure you were trying to keep visible drops to zero. In any workbook with outline groups, use 9, not 109.

If detail rows can carry errors — a #DIV/0! from an allocation, an #N/A from a lookup — use =AGGREGATE(9,6,B4:B6) instead. Option 6 ignores error values, and AGGREGATE ignores nested subtotals the same way, so it is a direct replacement.

Grouping versus hiding rows

Both fold rows out of sight. Only one of them survives a review conversation.

Mechanically they are close cousins; the difference is what the recipient concludes. Hidden rows read as something has been taken out of this schedule, and the reviewer's next move is to unhide everything and check that nothing was removed rather than folded. Grouped rows read as the detail is here, folded — and the reviewer clicks instead of emailing you.

One shared trap: copying a range that contains collapsed or hidden rows can bring the folded rows with it, because Excel special-cases filtered lists but not outlines. To paste the summary view and only the summary view, select the range, press Alt+; (Go To Special > Visible cells only), then copy.

How HISAB 360 helps

Outlining is the manual version of a question every reviewer asks: what is inside this total? HISAB 360 is a paid Excel add-in that answers it directly — double-click a total in a report it built and the underlying transactions expand inline, underneath the figure, then collapse away again when you are done. No pre-built groups, no second schedule, and the anchor survives rows being inserted above it.

That matters most when the detail is not in the workbook yet. HISAB connects to QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage Accounting, pulling invoices, bills and GL activity into sheets, so drilling into "Total overheads" reaches actual ledger postings rather than whatever was pasted in last month. Its AI panel, docked in the Excel window, will also build the sectioned statement in the first place from a plain-English request, and write the SUBTOTAL totals rather than leaving you to choose between 9 and 109. It generates and installs VBA on request too — including the routine that keeps outline buttons alive on a protected sheet.

Honest limits: Windows desktop Excel only (Microsoft 365 or 2016+), not Mac and not Excel on the web, and it is paid. The trial is 15 days with 50 AI credits and no card. Generated reports deserve the same review as a junior's: check one total by hand before it goes out.

Rules for a pack that gets shared

• Total rows outside the group, always. The single commonest cause of "grouping broke my P&L".
• A summary or blank row between adjacent blocks, or Excel merges the two groups into one.
• Set the level before you save. The reader opens into whatever state you left.
• On a protected sheet, outline buttons stop working — there is no checkbox for it in the Protect Sheet dialog. The fix is a Workbook_Open routine that sets EnableOutlining = True and protects with UserInterfaceOnly:=True; it must run each session, because that flag is not saved with the file.
• Grouping rows is unrelated to grouped worksheets — the feature where clicking several sheet tabs types into all of them at once. Different feature, same word, and the second one edits data you cannot see.

The shortcut sheet:

Outlining takes a minute to learn and changes how your reporting pack gets read — it sits alongside the other habits 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.