How to Group Rows and Columns in Excel (Outline)

To group rows in Excel, select the rows, choose Data > Group, and Excel adds a small minus button that folds them away. Grouping (Excel also calls it an outline) is the tidy way to hide detail in long reports: readers see the totals first and open the detail only when they need it. This guide covers the shortcuts on Windows and Mac, outline levels, automatic grouping and how to keep your totals right.

How to group rows in Excel#

Grouping takes three steps, and the only thing to get right is the selection.

1. Select the whole rows you want to fold away. Click the first row number and drag down, or click a cell and press Shift+Space to select its row, then Shift+Down Arrow to extend. Don't include the total row: it should stay visible.
2. Group them. Go to Data > Group (in the Outline area), or press Shift+Alt+Right Arrow on Windows or ⌘⇧K on a Mac.
3. Collapse the group. Click the minus button that appears in the left margin. It turns into a plus button. Click that to expand the rows again.

If you selected cells instead of whole rows, Excel asks whether you mean Rows or Columns. Pick Rows and click OK. Faster selection habits make this smoother; the selection shortcuts lesson drills Shift+Space, Ctrl+Space and extending with Shift.

Here is a typical sales report before grouping:

Group rows 2 to 3, then rows 5 to 6. Collapse both and the report shows only the two region totals and the grand total.

How to group columns#

Columns group exactly like rows. Select whole columns (click the letters, or press Ctrl+Space on a cell), then use Data > Group or the same shortcut. The buttons appear above the column letters instead of beside the row numbers.

A common use is a budget with twelve month columns and a yearly total. Group January to December and collapse them, and the sheet shows the yearly figure alone. Grouping doesn't move or delete anything, so formulas that point at the months keep working. The rows and columns lesson covers selecting, inserting and resizing them.

How do outline levels work?#

Outline levels let you collapse every group at once. When you group, Excel shows small numbered buttons at the top left of the sheet: 1, 2 and so on.

• Level 1 shows only the outermost summary, such as the grand total.
• Level 2 shows the next layer, such as the region totals.
• The highest number shows every row.

You can put groups inside groups, up to eight levels deep. For example, group rows 2 to 7 first (both regions), then group 2 to 3 and 5 to 6 inside it. Now level 1 shows just the grand total, level 2 adds the region totals, and level 3 shows every month.

To hide the buttons without removing the groups, press Ctrl+8 on Windows. Press it again to bring them back.

How to group rows automatically#

Excel can build the groups for you in two ways.

Auto Outline#

If your sheet already has total rows with formulas such as =SUM(C2:C3), go to Data > Group > Auto Outline. Excel looks for rows that add up the rows above them and groups the detail. It works best when every total is a formula directly below its detail rows.

Data > Subtotal#

The Subtotal command inserts the total rows and groups them in one step. Sort the data by the column you want to group on (Region here), then go to Data > Subtotal, choose "At each change in: Region", "Use function: Sum" and tick Sales. Excel adds a total row under each region and a grand total at the bottom, all written as SUBTOTAL formulas, with the outline already built.

Subtotal needs the data sorted. If North rows are scattered, you get several separate North totals. It also doesn't work inside an Excel table, so convert the table to a range first (Table Design > Convert to Range).

Do collapsed rows count in totals?#

Yes. Collapsing a group only hides the rows, and =SUM(C2:C7) still adds every row, hidden or not. That is usually what you want for a grand total.

The real risk is double counting. In the report above, =SUM(C2:C7) returns 7,800, because it adds the months and the two region totals. Use SUBTOTAL instead:

=SUBTOTAL(9,C2:C7)

This returns 3,900. Function number 9 means sum, and SUBTOTAL skips any cell in its range that holds another SUBTOTAL formula. So write the region totals as =SUBTOTAL(9,C2:C3) and =SUBTOTAL(9,C5:C6), and the grand total can cover the whole column safely.

If you want a total of only the rows you can still see when groups are collapsed, use 109 instead of 9. Collapsed rows count as hidden by hand, and the 100-series numbers leave those out. The SUBTOTAL function guide lists every function number, and the article on SUBTOTAL and filtered rows explains 9 vs 109 with examples.

Group, hide or freeze?#

Grouping, hiding and freezing all change what you see, but they solve different problems.

Grouping is the friendlier choice when other people read the file, because the plus buttons show that something is folded away. Hidden rows are easy to miss; see how to hide and unhide rows and columns when you need that instead. To keep headers visible as you scroll a long grouped report, combine it with Freeze Panes.

In a PivotTable, Group means something else: it combines items, such as dates into months or quarters. You right-click an item and choose Group. The PivotTable grouping lesson practises that.

How to ungroup rows#

To remove one group, select its rows and choose Data > Ungroup, or press Shift+Alt+Left Arrow on Windows (⌘⇧J on a Mac). To remove every group on the sheet, choose Data > Ungroup > Clear Outline. Your data stays where it is, and any collapsed rows are shown again.

If you later insert rows inside a group, they join that group automatically. Rows inserted just below a group's last row may not, so check the bracket in the margin. The guide to inserting multiple rows shows the fastest ways to add them.

Practise it#

Try the exercise on this page: it is the grand total from the report above, and SUM would give the wrong answer. Then practise working with rows and columns in a live spreadsheet, where you select, insert and resize them and get instant feedback on each step.