Group Columns in Excel

Excel’s column grouping feature is a powerful tool for organising and managing large datasets. By collapsing and expanding sections, you can enhance readability, streamline navigation, and create tailored data views.

• Improved Readability: Hides irrelevant columns to focus on key data.
• Better Organisation: Categorises related data for clarity.
• Efficient Navigation: Collapses sections to reduce scrolling.
• Custom Reporting: Tailors data views for different purposes.

Group Columns in Excel

There are two ways to group columns in Excel. Below are some simple and effective methods we can follow to group columns easily.

Method 1: Manual Grouping of Columns

The manual grouping method is the most straightforward way to group columns.

Step 1: Select the Columns

Highlight the columns we want to group by clicking and dragging over the column letters (e.g., B, C, D).

Step 2: Access the Group Feature

• Go to the Data tab in the Ribbon.
• In the Outline group, click on Group.

We can also use the Keyboard Shortcut to Group Columns

• Press Shift + Alt + Right Arrow

Step 3: Collapse or Expand

A small plus (+) or minus (-) button will appear above the grouped columns. Click on it to collapse or expand the group.

Step 4: Preview Results

In the below results, the columns has been collapsed.

Method 2: Using Auto Outline to Group Columns

If our dataset is well-structured with hierarchical data, Excel’s Auto Outline feature can automatically group columns for us.

Step 1: Preparing our Data

Ensure our dataset is organized with clear headers and follows a consistent structure.

Step 2: Activate Auto Outline

• Go to the Data tab in the Ribbon.
• In the Outline group, click on Auto Outline.

Step 3: Automatic Grouping

Excel will analyze our data, detect patterns, and group the columns accordingly.

Step 4: Collapse or Expand Groups

Use the plus (+) or minus (-) buttons above the grouped columns to expand or collapse the groups as needed.

Tip: we can also create a nested column group in which we can collapse and expand multiple levels of grouped columns to focus on specific sections of our data.

Step 5: Preview Results

In the below example, we have collapsed Q1 Total. Click on the Plus Icon to Expand the Group.

Group Adjacent Columns or Rows Separately in Excel

Sometimes, Excel’s group function merges adjacent columns into a single group instead of creating independent ones. Here’s how to fix this and group adjacent columns (or rows) separately:

Step 1: Attempt Initial Grouping

• Select the first set of columns (e.g., Subjects) and go to Data > Group.
• Choose "Columns" in the pop-up and click OK to create a group.
• Repeat the same steps for the next set of columns (e.g., Marks).

Issue : Instead of two separate groups, Excel may merge all selected columns into one group.

Step 2: Fix the Grouping

• Step 1: Click on column E (or the first column between the two groups) and go to Insert > Entire Column. This creates a blank column between the two sections.
• Step 2: Re-group the "Subjects" and "Marks" columns separately using the same grouping steps.

Result

• Now, two independent groups are created, each with its own outline buttons at the top.
• Collapsing both groups will show separate collapsible sections for "Subjects" and "Marks."