How To Group Rows in Microsoft Excel

350

As a versatile spreadsheet tool, Microsoft Excel offers a wealth of functionalities to streamline your data management and analysis workflows. One such powerful feature is the ability to group rows, enabling you to organize your datasets, enhance data summarization, and improve overall readability and reporting.

In this comprehensive Excel tutorial, we’ll explore various methods for grouping rows, catering to datasets of diverse sizes and structures. While manually selecting and grouping rows might seem like a viable option, it can quickly become cumbersome, especially when dealing with large or complex datasets. Instead, we’ll dive into semi-automated and fully automated techniques that will save you time and effort, ensuring your Excel worksheets remain organized and optimized for efficient data analysis.

Using the Auto Outline Command

The Auto Outline command in Excel automatically creates an outline for your data provided that it’s in the right format. You can use it successfully if your data is well-organized in a structured format. Typically, it should be hierarchical data where summary rows are distinct from the detailed data.

For example, you can apply the command in the above dataset. It contains a summary row after a few rows of numerical data. The summary rows also contain the SUM function to calculate the totals of the previous rows.

Organize your worksheet data in the above format and select the whole dataset.

Now, go to the Data tab and click on the Group drop-down command of the Outline commands block.

Click on the Auto Outline option in the context menu.

Excel shall automatically group the summary and detailed data rows in the selected worksheet as shown in the above screenshot.

If you click 1 in the outline navigation panel, you’ll only see the summary rows.

Contrarily, if you click 2 in the same panel, you’ll see all the data rows including the summary rows.

The drawback of this technique is your input dataset must be highly organized in a hierarchical structure. You can’t apply this method to any dataset.

If you attempt to use it on an unorganized dataset, you’ll get an error as shown above.

Using the Group Tool

Need to group rows in Excel without restructuring your dataset? Leverage the powerful Group tool to streamline your data organization. This intuitive Excel command allows you to select any number of rows and bundle them together, creating a hierarchical row structure. With the ability to group rows within existing groups, you can establish a step-wise row hierarchy, enabling you to selectively display the data you need at any given time. This versatile approach empowers you to maintain a clutter-free workspace, enhance data visibility, and facilitate efficient analysis and reporting, all without disrupting the underlying dataset.

See below how you can group the rows of the above dataset in the following groups:

• Rows visible for January
• Rows visible for January and February
• All rows are visible

First, select the whole dataset below the column header row and click on the Group command in Data > Outline commands block.

On the Group dialog, select Rows and click OK.

It’ll create an outline for the entire dataset.

Now select the rows for February and March and apply the Group command again for these rows. A 3rd outline will show up in the dataset.

Here, if you click the 2nd outline, only the January rows will be visible.

Finally, select the March rows and apply the Group command again. This will create the 4th outline.

Here, if you click on the 3rd outline, you’ll see rows from January and February.

The 4th outline makes March rows visible along with the 2nd and 3rd outlines, which makes all the datasets visible.

Excel Group Rows Hotkey

If you need to pace up the process of Excel group rows, you can select the target rows and press Alt + Shift + Right Arrow keys on the keyboard.

When you must delete a row group in Excel, you can press the Alt + Shift + Left Arrow shortcut.

Using the Subtotal Command

Let’s say, you’ve got a list of purchases for your organization as shown in the above screenshot.

You want to group all the rows of purchased items based on the departments, show subtotals of department-wise purchases, and finally show a grand total of all purchases.

In this scenario, you can use the Subtotal tool of the Outline commands block to group rows in Excel.

Select the entire input dataset along with the column headers. The Subtotal tool will use the column headers to create Excel group rows as well as show calculation options.

Click on the Subtotal command from the Data tab.

You should see the Subtotal dialog box. Here, do the following as outlined below:

• At each change in: This is the column header by which you want to create a subtotal row. In this example, it’s the Dept column.
• Use function: Use this drop-down menu to choose the calculation you want in the subtotal row. I’ll use the SUM function.
• Add subtotal to: Checkmark the column header to which you want to apply the subtotal calculation. Basically, Excel will perform an SUM for the values in the selected column at each subtotal interval. I’ve selected the Cost column in this exercise.
• Checkmark the Summary below data checkbox.
• Keep the rest of the items in their default settings state.

Click OK on the Subtotal dialog box to apply the configuration.

Excel will now transform the input dataset to show two outlines.

The 1st outline collapses or expands all the rows of the dataset. If collapsed, it’ll only show the Grand Total row.

The 2nd outline collapses the data rows and shows only the subtotal rows.

The plus icons in the 2nd outline allow you to collapse or expand the items of each subtotal summary row.

Using the Consolidate Tool

Let’s consider, that you’ve got three worksheets of expenses in the Facilities, IT, and Operations for three business sites.

You want a consolidated view of all these expenses with outlines for grouped rows in a different worksheet. In this scenario, you can use the Consolidate tool in the Data Tools commands block of the Data tab.

Using the Consolidate tool is fairly simple if you follow the exact instructions outlined below:

Go to the destination worksheet and click on A1. Now, go to Data > Data Tools commands block > click Consolidate.

The Consolidate dialog will pop up.

Choose a function by clicking the Function drop-down menu. I’ve chosen the SUM function in this exercise.

Click on the Reference field and go to the first dataset of expense data in another worksheet. Select the whole dataset and click on the Add button.

Now, repeat the same steps for the rest of the site expenses data.

The Consolidate dialog will look as shown in the above screenshot.

Checkmark the checkboxes for the Top row and Left column. Also, checkmark the Create links… checkbox.

Hit OK to create the Excel group rows.

Click on the outline expansion icon or plus icon to see the underlying grouped rows.

Using the Name Manager Tool

Suppose, your dataset is small to medium and views well within the borders of the current worksheet display. Here, if you want to focus on a specific group of rows without hiding the others, you can use the Name Manager tool.

Let’s consider the dataset you’re working on is similar to the above example. You’d like to group the sales figures for NY, CA, and FL.

Select the data rows and the subtotal row for NY and click on the Formulas tab.

Click on the Name Manager command inside the Defined Names commands block.

On the Name Manager dialog, click on the New button.

Customize the values of the New Name field, like Name, Scope, Comment, etc. If you need to change the selected cell range, click on the Refers to field and highlight a new cell range from the active worksheet.

Click OK to create the NY_Sales named range.

Now, repeat the same steps for the rows for CA Sales and FL Sales.

You’ll have three new named ranges in the Name Manager dialog box. Click on the Close button.

Now, click on the Name Box drop-down menu and choose one named range, like CA_Sales.

This action will select and highlight the cell range A7:D11, which corresponds to the rows reflecting the sales figures of the CA store.

You’ve efficiently emphasized the key rows while still keeping the rest visible. Even though you may not require them right away, you also don’t want to completely obscure them.

One benefit of this approach is that you can group and emphasize rows that are not next to each other in the dataset you’ve chosen. All you have to do is familiarize yourself with different techniques for highlighting alternate rows in Microsoft Excel.

Using a PivotTable

Suppose, you’re working on a department-wise expense dataset. There, rows aren’t organized by similar departments. If you need to group rows by values of different column headers, you can use the PivotTable tool.

For example, the above is a sample dataset. Where the items inside the Dept columns are mixed. You want to view the dataset by grouping rows in different angles, like by Unit Price, Units, or Cost.

To use the PivotTable to get data insights from grouped rows matching the department names, without manually grouping the rows, highlight the entire dataset.

Click on the Insert tab and choose PivotTable from the Tables command block.

Select the Existing Worksheet option inside the PivotTable dialog box and choose a cell on the active worksheet as the destination. Click OK to apply the configuration.

You should see the PivotTable Fields navigation panel on the right side.

Drag and drop the Dept item into the Rows field.

Let’s say, you want to get data insights based on rows grouped by the Cost header. Drag and drop the Cost item into the Values field.

You should see a tabular representation of the Sum of Costs by grouping similar rows under the Dept column.

Similarly, you can calculate the department-wise total units of items purchased, by dragging and dropping the Units item into the Values field.

Using Power Query

While the built-in Excel grouping tools provide a seamless way to organize your data, Power Query offers an even more powerful solution, particularly when working with large datasets that exceed Excel’s row handling capabilities. By importing data from external databases, Power Query empowers you to harness the full potential of row grouping, unlocking deeper insights and facilitating advanced data analysis.

Beyond the confines of a traditional Excel worksheet, Power Query allows you to create data insights by grouping rows within your imported dataset, regardless of its size or complexity. This capability becomes especially crucial when dealing with expansive databases that simply cannot be accommodated within the row limitations of Excel. By leveraging Power Query’s robust data transformation and modeling features, you can group rows, apply sophisticated filters, and perform advanced analytics, all while maintaining the integrity and scalability of your data source.

If you’re importing an external dataset to Power Query, click on the Get Data drop-down menu inside the Get & Transform Data of the Data tab.

Hover the cursor over the From Database menu and choose an option from the overflow menu, like From Oracle Database.

In the current exercise, I’m exporting a dataset to Power Query from the active Excel Worksheet. For this, I’ve selected the target dataset and clicked on the From Table/Range command in the Data tab.

Find above a glimpse of how Power Query organizes your imported dataset.

Now, go to the Transform tab and click on the Group By command.

You should now see the Group By wizard.

Here’s how to configure the Group By dialog box:

• Group By type should be Basic for one-column-based grouping and Advanced for grouping by multiple columns.
• Immediately below the Basic option, choose the column by which you’re grouping rows. For example, Dept in the current exercise.
• Enter a new name inside the New column name field.
• Choose a function in the Operation field. Let’s go ahead with Sum.
• In the Column field, choose the column header on which you want to perform the Sum function. In the present exercise, it’s the Cost column.
• Click OK to finish customizing the Group By dialog box.

Power Query will show a new dataset. It’ll have only the Group By column, which is the Dept column, and the sum of the costs of all the rows in the source dataset under the Dept Wise Cost column.

Click the File tab and choose Close & Load To.

On the Import Data dialog, click on the Existing worksheet option and select the destination cell on the active worksheet.

You’ll get a data insight into the input dataset after grouping the relevant rows and summing their cost components.

Using Excel VBA

If you want to automate the Excel group rows process, you can use VBA programming. It’s truly simple! You only need to follow a few steps to create macros. I’ll share the programming scripts to be used in those macros.

First thing first, read the following Excel tutorial to learn how to create macros from scratch:

If you’re ready, use the following script to bring up the Subtotal dialog box for grouping rows in Excel:

Sub ShowSubtotalDialogBox()
    ' Declare a Range object
    Dim TargetRange As Range

    ' Show an input box and set the TargetRange to the selected range
    On Error Resume Next ' In case the user cancels the input box
    Set TargetRange = Application.InputBox("Please select the target dataset using your mouse:", Type:=8)
    On Error GoTo 0 ' Reset error handling

    ' Check if a range was selected
    If Not TargetRange Is Nothing Then
        ' Select the range
        TargetRange.Select

        ' Show the Subtotal dialog box
        Application.Dialogs(xlDialogSubtotalCreate).Show
    Else
        MsgBox "No range selected. Please try again.", vbInformation
    End If
End Sub

After creating a VBA macro using the above code, press Alt + F11 to bring up the Macro dialog.

There, click on the ShowSubtotalDialogBox macro and hit Run.

You’ll see an input box. Use the mouse to select a dataset on the active worksheet.

Click OK and Excel will show the Subtotal dialog box.

Now, follow the steps mentioned in the Subtotal section previously in this Excel tutorial to group rows.

Conclusions

So far, we’ve explored a comprehensive set of eight tried-and-tested methods to group rows in Excel, catering to a wide range of dataset sizes and requirements.

For small to medium-sized datasets, the built-in Excel user interface commands offer a seamless solution. Leverage the Auto Outline command, the Group tool, and the Subtotal command to quickly organize your data and maintain a structured, hierarchical view.

If you don’t wish to hide certain groups of rows but instead want to highlight specific rows, the Name Manager tool provides a flexible alternative, allowing you to apply targeted emphasis without disrupting the overall layout.

To create outlines and extract deeper insights from similarly organized datasets across multiple worksheets, the Consolidate tool is a powerful ally, enabling you to amalgamate and analyze your data with ease.

For more complex data management and visualization requirements, harness the capabilities of Power Query and PivotTable-based techniques. These advanced methods empower you to group rows, generate data insights, and create impactful visualizations, even when working with large or diverse datasets.

Finally, for those seeking to automate the row grouping process, the Excel VBA-based approach offers a customizable solution, allowing you to streamline your workflows and ensure consistent data organization.

Let us know in the comments if this article has helped you master the art of row grouping in Excel. If you’re aware of any additional methods that could enhance this comprehensive guide, we’d be delighted to hear your insights and incorporate them.