How to Create Collapsible Rows in Excel (4 Methods)

If you want to Create Collapsible Rows in Excel, this article is for you. Here, we will demonstrate to you 4 methods so that you can create collapsible rows in Excel.

Watch Video – Create Collapsible Rows in Excel

The following Item Price List table shows the Month, Item, and Price columns. We will use 4 methods to collapse the rows of this table. Here, we used Excel 365. You can use any available Excel version.

Method-1: Creating Collapsible Rows in Excel Automatically

Here, we will automatically generate collapsible rows.

➤ To begin with, we will go to the Data tab in the ribbon.

➤ After that, we will select Outline> Group> Auto Outline.

Now, we can see the creation of 2 collapsible rows. We can see in the top left corner the number of rows. We can also see two negative signs “–” at the left, which indicates where the collapsible rows will be generated.

➤ If we click on the “-” sign at the left, the rows become collapsed.

We can only see the January Total and February Total rows. The other rows have been collapsed.

Method 2: Create Collapsible Rows Manually

In this method, we can create collapsible rows where we need them. Here, we want collapsible rows for January month items, and we only want to see the January Total.

➤ To do so, first of all, we will select the data from cells B5 to D9.

➤ After that, we will go to the Data tab > Outline > Group > Group…

Now, we will see a Group window appear.

➤ We will select Rows, and click on OK.

Finally, we can see a negative sign “–” on the left side of the January Total column, which indicates that the rows before that column will be collapsed.

➤ If we click on the negative sign before “-” we will see that all the rows before January Total have collapsed, and we only see January Total.

In a similar way, we can collapse any rows according to our needs.

Method 3: Using the Subtotal Option to Create Collapsible Rows in Excel

Here, if we want to calculate the total and grand total of the months, we can use the Subtotal option.

For instance, we want to calculate the total for January and February month, and also we want to calculate the grand total for these two months.

➤ To do so, we will select a single cell of the table.

➤ After that, we will go to the Data tab > Outline > Subtotal.

A Subtotal window will appear.

➤ Here, we will select Month in the box At each change in > select Use function as Sum.

➤ Select Add subtotal to as Price > mark Replace current subtotal and Summary below data.

➤ After pressing OK, we can see the creation of 3 rows with the name January Total, February Total, and Grand Total.

➤ Now, if we click on January Total, we can see that it is created with the SUBTOTAL function.

Thus, February Total and Grand Total rows have also been created with the SUBTOTAL function.

Let’s collapse the rows before the January Total and February Total rows.

➤ To do so, we have to click on the negative sign “–” before the January Total and February Total rows.

We can see that the rows before January Total and February Total have collapsed.

➤ We can collapse the rows January Total and February Total, and only see the Grand Total if we click on the negative sign “–” before Grand Total.

Finally, we can only see the Grand Total row in the table.

Method-4: Creating Collapsible Rows with Excel Pivot Table

We can use a Pivot Table to create collapsible rows.

➤ Here, to create a pivot table, first, we have to select the entire dataset.

➤ After that, we will select Insert > Pivot Table.

Now, a PivotTable from table or range window will appear.

➤ We will select New Worksheet > click OK.

➤ Here, we selected Month, Item and Price.

In the Rows, we can see Month and Item, and in the Values we can see Sum of Price.

And we can see the created Pivot Table.

➤ Now, to collapse the rows, we will click on the negative sign ”–” mark which is on the left side of the January and February months.

Finally, we can see the rows with January, February and Grand Total. The rows in between have been collapsed.

Hiding Outline Symbols after Creating Collapsible Rows in Excel

Here, we do not want to see the outline symbols in our worksheet, for instance, we want to remove the plus sign “+” before the January and February Total.

➤ First of all, we will select the File tab in the ribbon.

➤ After that, we will select Options.

➤ Then, we will select Advanced > unmark Show outline symbols if an outline is applied.

After that, click OK.

Finally, we can see there is no ‘“+” sign before the January Total and February Total rows.

You can check the option again to make the plus sign visible again

Download Workbook

Create Collapsible Rows.xlsx

Conclusion

Here, we tried to show you 4 methods to create collapsible rows in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions feel free to know us in the comment section.

<< Go Back to Rows in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!