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