How to Create Collapsible Rows in Excel (Step-by-Step Guide)

Collapsible rows in Excel help you organize large worksheets so you can focus on summaries without deleting detail. Excel's Outline tools (Group, Auto Outline, and Subtotal) add expand/collapse controls beside your rows. Hiding rows is different: it temporarily removes them from view without creating those outline controls.

Below are four practical ways to create collapsible rows in Excel, plus keyboard shortcuts and how to do the same workflow in WPS Spreadsheet.

Part 1: How To Create Collapsible Rows In Excel?

If you are working on a large dataset in Excel, you can use the collapsing rows feature to hide unnecessary data and display the required data only. There are different methods to create collapsible rows in Excel. We will discuss each method in detail.

Method 1: Creating Collapsible Rows in Excel Automatically

This is the easiest method for creating collapsible rows. Follow the below-listed steps to create collapsible rows automatically.

Step 1: Open the Excel sheet and select the Data tab from the menu bar.

Step 2: Click the Group option and then select Auto Outline.

Step 3: You will see that two collapsible rows have been created. You can also notice 2 minus signs on the left. These signs indicate where the collapsible rows will be generated.

Step 4: If you click the minus sign, the rows will collapse.

Method 2: Create Collapsible Rows Manually

You can also create collapsible rows where you need them. For example, if you only want to collapse the January items and get the January Total, you can follow these steps.

Step 1: Select data from the cell A5 to D9.

Step 2: Go to the Data tab, click the Group option, and again select the Group option.

Step 3: Select the Rows option in the Group dialog box and press OK.

Step 4: You can see a minus sign on the left side of the January total. You can collapse rows using this minus sign to collapse the selected rows and only see the January total.

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

This method is used to calculate the total and grand total. In the above example, suppose you have a large dataset and want to calculate the total of each month and the grand total of all months; you can use this method.

Step 1: Select a single cell of the table and go to the Data tab.

Step 2: In the Outline section, select Subtotal from the options available.

Step 3: A Subtotal pop-up window will appear. Select the columns for which you wish to calculate the total and subtotal. In our case, we want the total price for each month and then the grand total price for all months. So we will select the Price option in the Add subtotal to: field. Hit the OK button.

Step 4: You will get the total of each month, i.e., January Total and February Total, and the Grand Total of all months.

Step 5: You can collapse the rows by pressing the minus sign to get the Total of each month and the Grand Total of all months.

This method is beneficial when you have a large dataset and want to calculate the total of each category and then the grand total of all categories. Collapsing rows will help you to get a better and easy-to-read data appearance.

Method 4: Creating Collapsible Rows with Excel Pivot Table

You can also use Pivot Table to create collapsible rows. Follow the below steps to perform this function.

Step 1: Select the entire table and go to Insert → Pivot table.

Step 2: Select the New worksheet on the PivotTable from the table or range window and click OK.

Step 3: Select Month, Item, and Price in the Pivot table fields option.

Step 4: To collapse rows, click the minus sign on the left side of the month column.

Step 5: You will only see the January, February, and Grand total. All the remaining rows have been collapsed.

Part 2: What Is the Shortcut for Collapse Rows in Excel?

Useful Excel shortcuts for outlining and collapsing rows:

• Alt + Shift + Right Arrow: Group selected rows (create a collapsible section).

• Alt + Shift + Left Arrow: Ungroup selected rows.

• Click outline level 1 / 2 / 3 (left of the sheet): Collapse or expand the whole outline to that level.

• Alt, A, H (Data tab sequence): Hide selected rows. This hides rows but does not create Group expand/collapse buttons.

Tip: After you create groups, use the minus (-) and plus (+) signs on the left to collapse or expand each section quickly.

Part 3: Create Collapsible Rows in WPS Office (Free Excel Alternative)

Although Excel is a powerful data analysis and management tool, it is not free. You must purchase a license by paying a certain amount to use Excel. WPS Office is the best alternative to Microsoft Excel. It is free to use. WPS Office is a feature-rich office solution used by millions of people worldwide. My experience using WPS Office was fantastic. It is a lightweight and easy-to-use software with a user-friendly interface. There are numerous features of WPS Office. Some of these are listed below.

Free to Use

WPS Office is free to use. You will not need to pay any amount for using it. On the other hand, if you want to use Microsoft Excel, you must pay a subscription fee for even the trial version.

All-in-one Office Solution

WPS Office is an all-in-one office solution. You can open all your Word, Excel, PowerPoint, and PDF files using WPS Office. You can also convert between different file formats easily. WPS Office has a Writer, Spreadsheet, Presentation, and PDF software to fulfill your needs.

Real-time Collaboration

WPS Office also provides real-time collaborative features. You can easily share your documents, collaborate with your teammates, and make real-time document changes. You can track and suggest changes to a single document managed by multiple users.

If you are a new user unfamiliar with Microsoft Office, using WPS Office is highly recommended due to its simple user interface. Even if you are a Microsoft Office user, you will enjoy using the WPS Office due to its unique features. WPS Office has a similar interface as that of Microsoft Office, and you can find all functions, features, and menu bars at similar locations as in Microsoft Office.

Use Word, Excel, and PPT for FREE

Free Download

How to download WPS Office for free?

You can download WPS Office for free using the following steps.

Step 1: Visit the official website of the WPS Office.

Step 2: Click the Free Download button to download the installer file.

Step 3: Run the installer file, check the license agreement terms, and click the Install Now button.

Step 4: Wait for the download and install process to complete.

Step 5: When the installation is completed, enjoy using the best office suite, WPS Office.

How to Use WPS To Create Collapsible Rows?

You can easily create collapsible rows using WPS Office Sheets. WPS Sheets is a powerful spreadsheet software equipped with all the basic excel skills and advanced functions. You can create collapsible rows using WPS following these steps.

Step 1: Create sample data in the WPS Office. We have created a monthly budget plan in WPS Sheets.

Step 2: Select all the rows of January and go to the Data tab in the main menu.

Step 3: Click the Group option, select Rows in the pop-up windows, and press OK.

Step 4: Perform a similar operation to the February rows. You will see two minus signs on the left. Click the first minus sign. All the rows of January will be collapsed, and you will only see the January total.

Step 5: Similarly press the second minus sign, all the rows of February will be collapsed, and you will only see the February Total.

Free Office Download

   

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Free Download

100% secure

Algirdas Jasaitis

FAQs

How to shade every other row in Excel?

To shade every other row in Excel (or WPS Spreadsheet), follow these steps.

Step 1: Select the rows you want to shade.

Step 2: Select Format as table from the Home menu.

Step 3: Select a table style of your choice. You can also change the shade color from this menu.

Step 4: Verify the cell range and click OK.

How do you make multiple collapsible sections in Excel?

You can easily make multiple collapsible sections in Excel by selecting your desired rows and clicking the Group icon from the Data menu.

How do you collapse the entire outline to show just the subtotals in Excel?

After you create an outline, click the outline level numbers (1, 2, 3) on the left to collapse the sheet to subtotals or summaries. Ctrl + 8 toggles the outline symbols on or off; it does not collapse rows by itself.

How To Create Collapsible Rows in Excel - Key Takeaways

You can create collapsible rows in Excel with Auto Outline, manual Group, Subtotal, or a Pivot Table. Choose Group/Auto Outline when you need reusable expand/collapse controls, Subtotal when you also need category totals, and Pivot Table when you want interactive summaries.

WPS Office is the best office suite to create, edit, and open Excel documents. WPS Spreadsheet software is a lightweight, robust, and stable tool to create professional Excel documents. You can download WPS Office from its official website for free.