How to Group Rows and Columns in Microsoft Excel

The Problem with Hiding Rows and Columns

When working with large, complex Excel spreadsheets—such as financial budgets, project timelines, or inventory trackers—the sheer volume of data can quickly become overwhelming. You often need to focus only on the high-level summary totals, but you still need to keep the detailed underlying data intact within the sheet.

Most beginners solve this problem by highlighting rows or columns, right-clicking, and selecting “Hide.” While hiding data works temporarily, it creates massive problems later. Hidden rows are invisible, meaning other users (or even future you) might easily forget they exist. When you copy and paste data, hidden rows are often accidentally copied along with the visible data, causing frustrating errors and broken formulas.

Instead of hiding data, the professional approach is to Group your rows and columns. Grouping creates a collapsible hierarchy. It adds physical plus (+) and minus (-) buttons to the margins of your spreadsheet, allowing you to instantly collapse or expand entire sections of data with a single click, without ever losing track of where your data is stored.

How to Group Rows in Excel

Grouping rows is highly effective when you have detailed sub-categories that roll up into a main category (for example, breaking down “January Expenses” into individual line items like rent, utilities, and software).

1. Open your Microsoft Excel spreadsheet.
2. Ensure your data is organised logically. The summary row (e.g., “Total January Expenses”) should sit immediately above or below the detailed rows you want to group.
3. Highlight the specific row numbers on the far-left side of the screen that you want to group. (Do not just highlight the cells; click and drag down the actual grey row numbers on the margin).
4. Important: Do not include your summary row in this selection. Only highlight the detailed rows that you want to collapse out of the way.
5. Click on the Data tab at the very top of the Excel ribbon.
6. Look to the far-right side of the ribbon for the “Outline” section.
7. Click the Group button.

A thin line with a small minus (–) button will immediately appear in a new margin to the left of your row numbers.

• Click the minus (-) button to collapse those rows.
• Click the plus (+) button that replaces it to expand the rows again.

How to Group Columns in Excel

Grouping columns works exactly the same way, but it is typically used for time-based data (for example, grouping all twelve months into a collapsible “Year” section).

1. Highlight the column letters (A, B, C, etc.) at the very top of the screen that contain the detailed data you want to collapse.
2. Again, do not highlight the summary column (e.g., the “Total” column).
3. Click the Data tab.
4. Click the Group button in the Outline section.

A line with a minus (–) button will appear above the column letters, allowing you to instantly collapse months of data out of your view.

How to Use the Outline Level Buttons (1, 2, 3…)

Once you create a group, you will notice small numbers (1, 2, 3, etc.) appear in the extreme top-left corner of your spreadsheet, where the row and column headers meet.

These are Outline Level buttons, and they are incredibly powerful if you have grouped multiple different sections across a massive spreadsheet.

• Clicking “1”: Instantly collapses every single group on the entire spreadsheet, leaving only your top-level summary data visible.
• Clicking “2”: Instantly expands the first level of groups.
• If you have created “groups within groups” (nested groups), clicking “3” will expand the deepest level of detail.

These buttons allow you to switch a massive spreadsheet from a “high-level executive summary” to a “detailed granular report” in one single click.

How to Ungroup Rows and Columns

If you no longer need the collapsible sections, you should remove the groups to keep the spreadsheet clean.

1. Highlight the rows or columns that are currently grouped. (You can also simply click the top-left corner between Row 1 and Column A to select the entire sheet).
2. Click the Data tab.
3. Click the Ungroup button (located directly next to the Group button).
4. If prompted, choose whether you want to ungroup Rows or Columns, and click OK.

The plus and minus buttons will disappear, returning your spreadsheet to its normal state. All of your data will remain completely intact.