Catalog

A Step-by-Step Guide to Create Collapsible Rows in Excel

November 14, 2023 2.9K views

Collapsing rows in Excel helps you to organize data for better readability and user experience. The easiest way to collapse rows is by selecting the rows you want to collapse and clicking the Hide button. Collapsing rows will hide the selected rows.

Collapsing rows in Excel

Collapsing rows in Excel

However, you can also use other methods to expand/collapse rows in Excel. This article will help you learn how to create collapsible rows in Excel using different methods.

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.

 Select the Data tab

Select the Data tab

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

Click Group and then click Auto Outline

Click Group and then click 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.

 Collapse options

Collapse options

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

Rows Collapsed

Rows Collapsed

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.

Select the Data

Select the Data

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

Group option

Group option

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

Group Dialogue box

Group Dialogue box

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.

Rows Collapse

Rows Collapse

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.

 The Data tab in Excel

The Data tab in Excel

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

The Subtotal option

The Subtotal option

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.

 The Subtotal Window

The Subtotal Window

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

 The Total and Grand Total

The Total and Grand Total

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.

Collapsing the rows to get the Total and Grand total only

Collapsing the rows to get the Total and Grand total only

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.

 Insert Pivot table

Insert Pivot table

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

Select the New worksheet and then click OK

Select the New worksheet and then click OK

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

Select Month, Item, and Price

Select Month, Item, and Price

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

Click the minus sign

Click the minus sign

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

 All rows are collapsed

All rows are collapsed

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

You can also collapse rows by using the shortcut keys in Excel. Use the Alt + A + H keys to hide the group of rows. Using this shortcut, you can easily collapse rows in Excel.

Part 3: Best Alternative To Microsoft Excel- WPS Office

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.

WPS Office

WPS Office

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.

Free Download WPS Office

Free Download WPS Office

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.

Free all-in-one Office suite

Free all-in-one Office suite

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.

Team Collaboration in Real Time

Team Collaboration in Real Time

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.

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.

Go to the WPS Office website

Go to the WPS Office website

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

Free Download WPS Office

Free Download WPS Office

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

Check and Click Install Now

Check and Click Install Now

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

Installing the WPS Office

Installing the WPS Office

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

WPS Office after installation

WPS Office after installation

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 and advanced functions of Microsoft Excel. 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.

Creating data in WPS Sheet

Creating data in WPS Sheet

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

Select rows and go to the Data tab

Select rows and go to the Data tab

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

Rows Dialogue box

Rows Dialogue box

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 Jaunray total.

 January rows collapsed

January rows collapsed

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

February rows collapsed

February rows collapsed

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • 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.

5,820,008 User
avator
Algirdas Jasaitis
logo

FAQs

1. How to shade every other row in Excel?

To shade every other row in WPS Excel, follow these steps.

Step 1: Select the rows you want to shade.

Select the row

Select the row

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

 Click Format as table

Click Format as table

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

Choose a table style

Choose a table style

Step 4: Verify the cell range and click OK.

Click OK

Click OK

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

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

If you have already created collapsible rows, you can use the shortcut Ctrl + 8 to hide the outlines.

How To Create Collapsible Rows in Excel - Key Takeaways

In this article, we discussed different methods regarding how to create collapsible rows in Excel. Collapsing rows helps to organize data better and improve user experience and readability. You can collapse rows in Excel using any of the four methods discussed above.

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.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.