This effortless and detailed Excel tutorial will show you how to collapse rows in Excel so you can focus on the important data by hiding unnecessary clutter.

Are you tired of scrolling through endless data in your Excel spreadsheet? Do you just need to see the bottom line – the totals, averages, and key figures?

Microsoft Excel’s got your back with a handy feature called row collapse in Excel. It lets you hide all those extra rows, so you can focus on the information that matters most.

Forget fancy tricks, I’ve put together the simplest ways to create collapsible rows in Excel. Follow these steps, and you’ll be a pro in no time!

📒 Read More: 6 Ways to Freeze Rows and Columns in Microsoft Excel

Using the Hide Rows Feature

The easiest method is to hide the rows you don’t need to see or present.

This method is highly convenient for tiny datasets and if you’ve just started with data analytics and visualization in Microsoft Excel.

Suppose, in the above example dataset, you only need to present the subtotals and the grand total to your client, supervisor, to audience.

You can simply select the rows you want to hide by pressing the Shift key and then clicking on the first cell, A2, and the last cell, E6, within the A2:E6 cell range.

Press the Hide Rows shortcut, which is Ctrl + 9, to hide the selected rows.

Now, repeat the same steps for the rest of the rows.

You’ve successfully collapsed the redundant rows in the worksheet to only show the headline figures for business expenses, like department-wise subtotal and grand total.

Since the method is completely manual, you might find it a bit boring if you’re an experienced Excel user. For you, I’ve created an automated method that performs the same steps as you’ve seen above but with the help of VBA programming. You can find the advanced version of this method below in the Excel VBA section.

Using Sort & Filter

The Sort & Filter tool is yet another basic Excel feature to collapse unnecessary rows in Excel so you can see a better picture of the dataset.

Again, this is a semi-automatic method where you need to manually enable the Sort & Filter for the source dataset and choose the necessary filtering so Excel shows only the intended rows.

Simple Filtering

Let’s consider that you want to collapse the rows for Facilities and Infirmary expenses in the above dataset. So, you can easily comprehend the effect of IT expenses in the Grand Total expense figures.

To achieve this, press Ctrl + Shift + L to activate the Sort & Filter tool on your worksheet.

Click on the Filter drop-down arrow in the Dept column and uncheck the row items you don’t need below the Search field in the context menu.

Click OK to collapse the unchecked rows.

Find above how your dataset will look when you apply data filtering in Excel to collapse rows.

📒 Read More: 6 Ways to Clear All Filters in Microsoft Excel

Filter by Color

Suppose, in the same example dataset, there are colored rows for expenses of different departments. In this case, you can use the Filter by Color feature in the Sort & Filter tool.

Enable Sort & Filter by pressing Ctrl + Shift + L after selecting any cell on the header row of the dataset.

Now, click on the Dept drop-down arrow.

A context menu will show up. Hover your mouse cursor over the Filter by Color menu.

The color filtering options will open in an overflow menu on the right side.

Click on a color by which you want to filter the dataset to collapse the intended rows. In the current tutorial, I’ve selected the yellow color.

Find above the filtered dataset by the yellow color that only shows the expenses of the IT department.

The downside of this method is you can only choose one color at a time. The rows containing the chosen color will stay and the rest of the rows will be collapsed to give you a focused view.

Using Custom Views

You can create different views for the same dataset in advance before presenting your report to the audience.

During the presentation, simply switch to those pre-configured views to show important rows and figures by collapsing redundant data rows.

Let’s see below how to create different views for a worksheet for the sample dataset shown above.

When all rows are expanded in your worksheet, go to the View tab and click on the Custom Views command inside the Workbook Views commands block.

Click Add on the Custom Views dialog box and give the current view a name. For example, I’ve named the original dataset as the Default Sheet View.

Then, I hid all the data rows containing individual department expenses and kept only the subtotals and the grand total figure.

I followed the previous steps again to configure this view as the Grand Total Sheet View.

I created a third view by collapsing all the data rows except for the IT rows, the IT Sub Total row, and the Grand Total row. I named it the IT Expenses With Grand Total view.

Find above the different views that I created for the sample dataset.

Now, when presenting this business expenses report to an audience, I’ll switch to the Default Sheet View from the Views tab > Custom Views > select the Default Sheet View > click on Show.

This would show the entire dataset showing all the rows.

To report the impact of the IT expenses on the overall business expenses, I’d switch to the IT Expenses With Grand Total view by following the above steps.

Finally, to show the subtotals and the grand total figure, I’ll just switch to the Grand Total Sheet View option.

This is one of the best ways to collapse unnecessary rows when presenting reports. You can conveniently switch to different views with minimum clicks, and hence the least inconvenience for the audience. However, the method is only good for small to medium datasets.

You can also use this method for big datasets but the manual labor would be excessive.

Using Manual Row Grouping

You can use the Group tool to create collapsible rows in Excel. In such groups, you’ll find an outline with a clickable button that can collapse and expand rows.

Suppose, you want to create collapsible row groups for granular costs of items for each department, like IT, Facilities, and Infirmary. The idea is to collapse rows to show the subtotals and the grand total figure.

When using the Group tool, separate each group of rows you want to collapse with a blank row as shown in the above image. These blank rows tell the tool which row belongs to the previous group and which one starts the next group.

Now, select the cells through A2 to A6 and click on the Group command inside the Outline block of the Data tab.

On the Group dialog box, select the Rows option and hit OK.

Excel will create a group outline for the selected cell range.

Now, repeat the same steps for A9 through A13 and A16 through A20.

In the end, you should have three outlines for the three groups of rows.

Click on the minus icons (-) or row collapse buttons on the outlines to hide these row groups.

After collapsing all the minute data rows, you get the above view. It only shows the IT Sub Total, Facilities Sub Total, Infirmary Sub Total, and Grand Total rows.

Click on the plus icons in the group outlines to expand the rows again if needed.

Using Auto Outline

If you’ve appropriately organized your dataset so that Excel can read a pattern from that, you can use the Auto Outline tool to group rows and thereby create collapsible rows.

For example, the above dataset is a perfect candidate for the Auto Outline command.

To create collapsible rows for the granular expense items for IT, Facilities, and Infirmary, select the entire dataset from A1 to E20.

Now, go to the Outline commands block and click on the Group drop-down menu. There, select the Auto Outline command.

Excel will create all possible outlines for the dataset and show those in the left-side navigation panel.

Now, click on the row collapse buttons on the outlines to hide the redundant rows.

For example, hide all the IT, Facilities, and Infirmary row groups.

Doing so will keep the subtotals and the grand total figures.

Using Data Subtotals

This is another automatic way to create collapsible rows from a structured dataset.

For example, you’ve got a dataset similar to the above example. The dataset shows collated sales figures achieved by different sales agencies for different store locations.

Now, you can apply the Subtotal command to group rows by store locations, create subtotals by stores, and a grand total sales figure including all the stores.

To try out this method, select an appropriate dataset including the column header row. The Subtotal tool will show the column headers in the configuration dialog box.

Now, click on the Subtotal command button inside the Outline block of the Data tab.

You should see the Subtotal dialog box.

There, perform the following customizations. However, in most cases, Excel reads the data pattern and automatically selects the Subtotal configurations.

• At each change in: Store (column)
• Use function: Sum
• Add subtotal to: Mar (column)
• Summary below data: Checkmark

Click OK on the dialog box to create the subtotal outlines.

Select the entire dataset. Now, click on the Hide Detail button in the Outline commands block to hide all minute data rows.

You’ll keep the NY Total, CA Total, FL Total, and Grand Total rows as shown above.

Using a PivotTable

PivotTable is another intuitive tool for collapsing rows in Excel.

For instance, in a dataset similar to the above, you’d like to create a store-wise view of sales by agencies, by months, subtotal of sales, and a grand total sales figure. You can do so using the PivotTable by following these simple steps.

Select the entire dataset including the column headers and click on the PivotTable command inside the Tables block of the Insert tab.

On the PivotTable dialog box, select the Existing Worksheet option. Select an empty cell on the active worksheet to populate the PivotTable. Click OK to apply the setting.

The PivotTable Fields should show up on the right side.

Drag and drop the fields below the Search field to the areas as instructed below:

• Store will go to the Rows box.
• Agency will also go to the Rows box.
• Put all the month fields inside the Values box.

If you’ve distributed the fields as mentioned above, you should see a PivotTable as shown in the above screenshot.

Click on the row collapse button as indicated in the image.

Doing so will hide agency-wise sales values but keep the subtotals and the Grand Total row.

Using Excel VBA

Excel VBA enables you to automate most of the data analytics and visualization tasks of Microsoft Excel with programmatic scripts and VBA macros.

In most cases, you’ll need to execute a VBA code or macro and Excel will automatically perform all the actions mentioned in the program script.

Find below a few simple yet effective VBA scripts to automatically create collapsible rows in Excel with custom inputs from your end.

Before beginning with the scripts, check out below how to create a macro from a VBA code:

📒 Read More: How To Use The VBA Code You Find Online

Collapse Rows by Hiding With VBA

The following VBA code will show visual queues so you can select the target rows to collapse those using the Hide Rows method:

Sub HideRows()
    Dim rng As Range
    Dim Answer As VbMsgBoxResult
    Dim AllRanges As Range
    
    Do
        Set rng = Nothing
        On Error Resume Next
        Set rng = Application.InputBox("Select rows to hide", Type:=8)
        On Error GoTo 0
        
        If Not rng Is Nothing Then
            If AllRanges Is Nothing Then
                Set AllRanges = rng
            Else
                Set AllRanges = Union(AllRanges, rng)
            End If
            
            Answer = MsgBox("Do you want to select another set of rows to hide?", vbYesNo)
        Else
            Exit Do
        End If
    Loop While Answer = vbYes
    
    If Not AllRanges Is Nothing Then AllRanges.EntireRow.Hidden = True
End Sub

After creating the VBA macro, press Alt + F8 to bring up the Macro dialog box.

There, select the HideRows macro and hit the Run button.

You’ll see an input box to either enter or select the cell ranges to hide.

Now, the script will ask you if you’d like to select another set of rows.

If you choose Yes, the first input box will show.

If you choose No, Excel will hide the rows. Find above a summary view of the original dataset created by collapsing unnecessary rows.

Collapse Rows Using VBA Subtotal

Use the following script to automate the Subtotal command as you learned previously:

Sub AutomateSubtotal()
    Dim GroupByCol As String
    Dim FunctionChoice As String
    Dim TotalColumn As String
    Dim FunctionNumber As Integer
    
    ' Get the column to group by
    GroupByCol = Application.InputBox("Enter the column header or letter to group by:", "Group By Column", Type:=2)
    If GroupByCol = "False" Then Exit Sub
    
    ' Get the function to use for subtotaling
    FunctionChoice = Application.InputBox("Enter the function to use for subtotaling (SUM, AVERAGE, COUNT, etc.):", "Subtotal Function", Type:=2)
    If FunctionChoice = "False" Then Exit Sub
    
    ' Map function choice to function number
    Select Case UCase(FunctionChoice)
        Case "SUM"
            FunctionNumber = xlSum
        Case "AVERAGE"
            FunctionNumber = xlAverage
        Case "COUNT"
            FunctionNumber = xlCount
        Case "COUNTA"
            FunctionNumber = xlCountNums
        Case "MAX"
            FunctionNumber = xlMax
        Case "MIN"
            FunctionNumber = xlMin
        Case "PRODUCT"
            FunctionNumber = xlProduct
        Case "STDEV"
            FunctionNumber = xlStDev
        Case "STDEVP"
            FunctionNumber = xlStDevP
        Case "VAR"
            FunctionNumber = xlVar
        Case "VARP"
            FunctionNumber = xlVarP
        Case Else
            MsgBox "Invalid function choice. Please try again.", vbCritical
            Exit Sub
    End Select
    
    ' Get the column header or letter for the subtotal to be added
    TotalColumn = Application.InputBox("Enter the column header or letter to add the subtotal:", "Total Column", Type:=2)
    If TotalColumn = "False" Then Exit Sub
    
    ' Find the column numbers based on headers or letters
    Dim GroupByColNum As Integer
    Dim TotalColNum As Integer
    GroupByColNum = Cells.Find(What:=GroupByCol, LookAt:=xlWhole, MatchCase:=False).Column
    TotalColNum = Cells.Find(What:=TotalColumn, LookAt:=xlWhole, MatchCase:=False).Column
    
    ' Clear any existing subtotals
    ActiveSheet.Cells.RemoveSubtotal
    
    ' Add the subtotals
    ActiveSheet.Cells.Subtotal GroupBy:=GroupByColNum, Function:=FunctionNumber, TotalList:=Array(TotalColNum), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    
    MsgBox "Subtotaling completed successfully!", vbInformation
End Sub

Upon execution, the script will show the following input boxes:

• An input box where you must enter a letter or column header to group by rows.

• A dialog box where you need to mention the function to use. You can choose from these options: SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR, and VARP.

• An input box so you can choose the column or letter below which Excel will populate the subtotal values.

• Finally, Excel will create the subtotal along with an outline for the grouped rows.

You can now click on the row collapse buttons on the outline to hide redundant rows and consolidate the report.

Conclusions

You should now know how to collapse rows in Excel using manual and automatic methods.

Choose the techniques depending on your requirements and Excel expertise levels.

If the article helped you learn the Excel collapse rows skill, you can acknowledge it in the comment box. Do you know a better and more realistic method than the ones demonstrated above? Don’t forget to share it in your comments.