8 Ways To Collapse Rows in Excel

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.

Example dataset 1

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

Select rows to be hidden
Select rows to be hidden

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.

Row collapse in Excel using Hide Rows
Row collapse in Excel using Hide Rows

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.

Collapsed all redundant rows using Hide Rows
Collapsed all redundant rows using Hide 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

Example dataset 1

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.

Activate Sort & Filter
Activate Sort & Filter

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

Applying simple filters
Applying simple filters

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.

Excel collapse rows using Sort & Filter
Excel collapse rows using Sort & Filter

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

Sample Dataset 2

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 Sorting & Filtering
Enable Sorting & Filtering

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

Choose a color code
Choose a color code

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.

Collapesd rows in Excel using Filter by Color
Collapsed rows in Excel using Filter by 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.

Example dataset 1

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

Custom Views
Custom Views

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.

Add View dialog
Add View dialog

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.

Three views for the same dataset
Three views for the same dataset

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

Default Sheet View
Default Sheet View

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.

IT Expenses With Grand Total
IT Expenses With Grand Total

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.

Grand Total Sheet View
Grand Total Sheet View

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.

Example dataset 1

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.

Inserted blank rows strategically
Inserted blank rows strategically

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.

Select cells and click on Group
Select cells and click on Group

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

The Group dialog
The Group dialog

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

The group outline
The group outline

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

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

Three group outlines
Three group outlines

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

Collapse rows button
Collapse rows button

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

Collapsed all rows in the dataset
Collapsed all rows in the dataset

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.

Example dataset 1

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

Auto Outline command
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.

Auto outlines in Excel
Auto outlines in Excel

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

Row collapse in Excel using Auto Outline
Row collapse in Excel using Auto Outline

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.

Sample dataset 3

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.

Subtotal command button
Subtotal command button

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.

Configuring Subtotal
Configuring Subtotal

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
Auto Subtotal outlines
Auto Subtotal outlines

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

Hide detail command
Hide detail command

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

Row collapse in Excel using Subtotal
Row collapse in Excel using Subtotal

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.

Sample dataset 3

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.

PivotTable dialog box
PivotTable dialog box

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 PivotTables Fields
The PivotTables Fields

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.
The PivotTable
The PivotTable

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

Row collapse buttons
Row collapse buttons

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

Collapsed rows using PivotTable
Collapsed rows using PivotTable

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:

VBA script 1
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
Macro dialog
Macro dialog

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.

Input box to enter cell ranges
Input box to enter cell ranges

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

Input box to enter cell ranges
Input box to enter cell ranges

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.

Excel collapse rows using VBA hide rows method
Excel collapse rows using VBA hide rows method

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:

VBA script 2
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:

Group by column
Group by column
  • An input box where you must enter a letter or column header to group by rows.
Subtotal function
Subtotal function
  • 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.
Total column
Total column
  • An input box so you can choose the column or letter below which Excel will populate the subtotal values.
Subtotal successful
Subtotal successful
  • Finally, Excel will create the subtotal along with an outline for the grouped rows.
Collapse rows using VBA Subtotal
Collapse rows using VBA Subtotal

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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃