How to Group Rows and Columns in Excel

The video guide shows how to group and ungroup rows using the Group and Ungroup buttons on the Data ribbon. The same principle works with columns. A common use of grouping would be in an accounts sheet containing monthly and annual data. Historic monthly data may serve as a useful reference but, unlike the annual totals, you may not want it visible all the time. Grouping months into years ensures historic data can be accessed through a single click.

By default, Excel will assume you want to hide the rows above a summary row, or the columns to the left of a summary column. This is generally the case when the summaries contain subtotals but may not be the case if you're grouping project tasks into phases. This data is more often structured to introduce Phase 1 in the first row, with the smaller tasks 1.1, 1.2 listed underneath.

To group the rows beneath a title row, you need to select Advanced Options from the Outline submenu on the Data ribbon. This is the small icon in the bottom right-hand corner of the Outline submenu. It can also be accessed using the keyboard shortcut ALT->A->L. By default 'Summary rows below detail' will be checked. If you uncheck it, rows will be grouped towards the top row. There is an equivalent 'Summary columns to right of detail' that can also be unchecked. These settings apply to the entire worksheet.

Grouping can be used if you are want to use grouping to hide infrequently used columns in a large database. If your database contained employees' physical addresses, you could group those columns under their name. This technique relies on the hidden columns having an intuitive link to the displayed columns. If not then it makes more sense to give users navigational buttons to show/hide columns using VBA macros or Office script.

By Ed Bolton, founder of Excel4Business Ltd. Last reviewed October 2026.