How to Create Collapsible Rows in Excel (4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Download Workbook


4 Methods to 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.

How to Create Collapsible Rows in Excel


Method-1: Create Collapsible Rows 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 collapsible rows. We can also see two negative signs “” at the left, which indicates where the collapsible rows will generate.

How to Create Collapsible Rows in Excel

➤ 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.

How to Create Collapsible Rows in Excel

Read More: How to Expand or Collapse Rows with Plus Sign in Excel (4 Easy Methods)


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.

How to Create Collapsible Rows in Excel

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 become collapsed.

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

How to Create Collapsible Rows in Excel

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

Read More: How to Group Rows in Excel with Expand or Collapse (5 Methods)


Method-3: Using Subtotal to Create Collapsible Rows

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.

How to Create Collapsible Rows in Excel

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 January Total and February Total rows.

➤ To do so, we have to click on the negative sign “” before 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.

How to Create Collapsible Rows in Excel

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

How to Create Collapsible Rows in Excel

Read More: How to Expand and Collapse Rows in Excel (with Easy Steps)


Similar Readings


Method-4: Create Collapsible Rows with 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.

How to Create Collapsible Rows in Excel

Read More: How to Group Rows in Excel Pivot Table (3 Ways)


If You Don’t Prefer Showing Outline Symbols

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.

How to Create Collapsible Rows in Excel

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


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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.


Related Articles

Afia Kona

Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo