How to Group Data in Excel

Staring at a spreadsheet with hundreds or even thousands of rows can feel overwhelming. Grouping data in Excel condenses massive datasets into a clear, digestible summary, letting you expand and collapse sections to see either the big picture or the fine details. This article breaks down a few simple methods for grouping data, from manual outlining to powerful PivotTable features, helping you create organized and professional-looking reports.

Why Bother Grouping Data in Excel?

Grouping data is like creating an interactive table of contents for your spreadsheet. Instead of endlessly scrolling, you can hide the detailed line items and show only the important subtotals. This small step transforms a cluttered report into a high-level summary that is easy for anyone to understand.

Here’s why it’s so useful:

• Improves Readability: Collapsible sections make your report cleaner and less intimidating, allowing stakeholders to focus on the information that matters most.
• Creates Interactive Summaries: Users can click to expand or collapse sections to explore the data at different levels of detail, from yearly totals down to daily figures.
• Simplifies Complex Reports: For financial statements, sales reports, or project plans with many sub-tasks, grouping helps organize information hierarchically.

Ultimately, it’s about control. You get to decide what level of detail your audience sees first, guiding their attention to the key takeaways.

Method 1: Manually Grouping Rows and Columns

Manual grouping is your go-to method when you want full control over what gets bundled together. It's perfect for creating custom summaries that don't rely on existing formulas. Think of it as manually creating foldable sections within your spreadsheet.

How to Group Rows

Let's say you have a sales report with data for multiple cities, which are organized by region. You want to create a collapsible group for each region.

Example Data:

Region City Sales North Seattle $5,000 North Portland $4,200 North Total $9,200 South Austin $7,500 South Dallas $8,100 South Total $15,600

Here’s how to group the "North" region data:

1. Select the Rows: Click on the row number for "Seattle" and drag down to select the row for "Portland." Only select the detailed rows you want to hide, not the summary row.
2. Navigate to the Group Feature: Go to the Data tab on the Ribbon.
3. Click Group: In the "Outline" section, click the Group button. A small dialog box might appear asking if you want to group rows or columns, select "Rows" and click OK.

You’ll immediately see a bar with a minus symbol (-) appear in the margin to the left of your newly grouped rows. Clicking the minus symbol will collapse the "Seattle" and "Portland" rows, hiding them from view and only showing the "North Total" row. The symbol will change to a plus sign (+), which you can click to expand the group again.

How to Group Columns

The process for grouping columns is nearly identical. This is extremely useful for chronological data, like grouping months into quarters.

Example Data: A budget tracking sheet with monthly columns.

Jan | Feb | Mar | Q1 Total | Apr | May | Jun | Q2 Total

To group the months for Q1:

1. Select the Columns: Click on the column letter for "Jan" and drag to select "Feb" and "Mar." Again, do not include the total or summary column in your selection.
2. Navigate to Group: Go to the Data tab.
3. Click Group: In the "Outline" section, click Group. A margin will appear above the column letters, allowing you to collapse and expand the columns for Q1.

Creating Nested Groups

You can even create groups within groups. For instance, you could group months into quarters, and then group all four quarters into an annual total. To do this, simply repeat the grouping process for a larger range. First, group the individual months. Then, select all the quarterly columns (e.g., A through M, including the quarterly totals) and click Group again. Excel will create a second level of outlining for the full year.

Method 2: Use Auto Outline for Instant Summaries

If your data is already structured with summary rows or columns using formulas like SUM(), AVERAGE(), or SUBTOTAL(), you can sit back and let Excel do the work for you with its Auto Outline feature.

When Does Auto Outline Work Best?

Auto Outline is designed for data that has a consistent hierarchical structure. For example, if you have rows for individual sales transactions followed by a row summing up that region's sales, Auto Outline will identify that SUM formula and automatically group the transactional data for you.

How to Apply an Auto Outline

Using it couldn't be simpler:

1. Click any single cell inside your data range. You don’t need to select everything.
2. Go to the Data tab.
3. Find the Group button and click the small dropdown arrow next to it.
4. Select Auto Outline.

Excel will instantly scan your data for summary formulas and apply grouping levels to your entire sheet. You’ll see the familiar outline bars appear in the margins, ready for you to expand and collapse. It's a huge time-saver for standardized reports that are already formatted with subtotals.

If it doesn't do what you expected, you can easily remove it by going to the Ungroup dropdown and clicking Clear Outline.

Method 3: Flexible Grouping with PivotTables

When it comes to summarizing and grouping large amounts of raw data, nothing in Excel beats a PivotTable. This feature allows you to dynamically group dates, numbers, and text without adding any new formulas or changing your original data source. It’s the most powerful and flexible method by far.

Grouping Dates (into Months, Quarters, and Years)

This is one of the most common and useful PivotTable features. Imagine you have a long list of daily sales records and you want a summary by month or quarter. Manually, this would be a nightmare. In a PivotTable, it takes seconds.

Step-by-Step Instructions:

1. Create your PivotTable: Select your raw data, go to the Insert tab, and click PivotTable. Add your "Date" field to the Rows area and a field like "Sales" to the Values area.
2. Right-Click a Date: In the generated PivotTable, right-click on any date cell in the first column.
3. Select "Group": From the context menu, choose Group.
4. Choose Your Grouping Units: A dialog box will pop up. Here you can select how you want to aggregate the dates. For a monthly report, select "Months" and "Years." You can choose multiple options. To see quarters, simply select "Quarters."
5. Click OK: Your PivotTable will instantly reorganize, displaying your sales data neatly bucketed into the time periods you selected.

Grouping Numbers (Creating Numeric Ranges)

You can also use PivotTables to group numeric data into ranges or bins. This is great for creating distributions, like grouping customers by age brackets or products by price ranges.

Let's say you have a list of customer ages and you want to see how many customers fall into 10-year age groups (20-29, 30-39, etc.).

Step-by-Step Instructions:

1. Create the PivotTable: Put the "Age" field in the Rows area and a count of customers (e.g., drag Customer ID to Values and set it to Count) in the Values area.
2. Right-Click a Number: Right-click on any of the age values in the first column of the PivotTable.
3. Select "Group": Choose Group from the menu.
4. Define the Ranges: Another dialog box will appear.
5. Click OK: The PivotTable will transform your long list of individual ages into clean, readable age brackets.

Quick Tips for Managing Your Groups

Once you've set up your groups, here are a few extra tips to work with them efficiently.

• Use the Outline Buttons: In the top-left corner of your worksheet (by the margins), you'll see small numbered buttons (1, 2, 3..). These correspond to the levels of your outline. Clicking '1' shows the highest-level summary, '2' shows the next level down, and so on. It's the fastest way to expand or collapse everything to a specific level.
• Copying Only Visible Cells: Be careful when copying and pasting a collapsed summary. By default, Excel copies the hidden cells too! To avoid this, first collapse your data to the level you want to copy. Then, select the range, go to the Home tab, click Find & Select, choose Go To Special..., and select Visible cells only. Now you can copy and paste just the summary data. The keyboard shortcut for this is Alt + , on Windows.
• Ungrouping Data: To remove a group, just select the rows or columns you want to ungroup, go to the Data tab, and click Ungroup. To remove all grouping from the sheet at once, click the dropdown under Ungroup and select Clear Outline.

Final Thoughts

Grouping data, whether you do it manually, with Auto Outline, or through a PivotTable, is a fundamental Excel skill for transforming dense spreadsheets into structured, reader-friendly reports. These techniques allow you to summarize information effectively and present it in a way that’s easy to analyze at a glance.

If you're finding that too much of your week is spent downloading CSVs and manually creating these kinds of reports, you might be ready for an automated solution. We built Graphed because we believe getting insights from your data shouldn't be so manual. Instead of wrangling rows in Excel, you connect your data sources once and use plain English to ask for the charts and dashboards you need. Dashboards update in real-time, so you can stop rebuilding reports and spend more time acting on the insights.