Afia Kona
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.
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.
➤ 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.
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.
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.
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.
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.
Finally, we can only see the Grand Total row in the table.
Read More: How to Expand and Collapse Rows in Excel (with Easy Steps)
Similar Readings
- How to Group Rows with Same Value in Excel (6 Useful Ways)
- Group Rows with Plus Sign on Top in Excel
- How to Create Rows within a Cell in Excel (3 Methods)
- Group Rows by Cell Value in Excel (3 Simple Ways)
- How to Highlight Active Row in Excel (3 Methods)
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.
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.
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.
