Shahriar Abrar Rafid
Need to learn how to collapse columns in Excel? The feature in Excel to collapse columns makes them disappear from the display. You may have a lot of columns in your dataset but you don’t need to work with them all at once. If you are looking for such unique tricks, you’ve come to the right place. Here, we will take you through 6 easy and convenient methods for collapsing columns in Excel.
Collapsing columns allow us to navigate through the spreadsheet easily and make it look clean.
Suppose we have Scores of Mid-Term Tests of 10 students of a particular institution. The dataset includes the ID and Names of the students. Also, it contains their respective English, Maths, and Social Science marks, as well as their Total marks.
Now, we will collapse columns D, E, and F to make them vanish from the display.
1. Using Group Feature to Collapse Columns in Excel
This method will show how to use the Group feature to collapse columns in Excel. Let’s go through the procedure below.
📌 Steps
- First, select the columns you want to collapse. To do this, move your cursor to the column header. Then, advance the cursor to the column heading till which you want to collapse. While doing this, keep the mouse on a long single-click. In this case, we selected Column D:F.
- Next, go to the Data tab.
- Then, select the Group drop-down on the Outline group.
- Finally, choose Group from the drop-down list.
- The above steps will make the selected columns grouped as indicated on the upper side as shown in the image below.
- Now, click on the minus (-) sign shown in the image.
- Then, click on the plus (+) sign at the top of Column G.
- Thus, you can expand the collapsed columns again.
- At this moment, you can collapse the columns in another way.
- Now, click on button 1 on the top left side of the image below.
- Again, we collapsed the three columns in our dataset.
- However, you can notice that Column C and Column G are located beside each other.
Read More: How to Group and Hide Columns in ExcelÂ
2. Applying Context Menu to Collapse Columns in Excel
The second method shows how to collapse columns in Excel utilizing the context menu. In our dataset, there are three columns for marks of three papers. Let’s hide them using the context menu.
📌 Steps
- Firstly, select columns in the D:F range.
- Then, right-click anywhere on the selected range.
- After that, select the Hide option from the context menu.
- Finally, we collapsed columns D, E, and F.
Read More: How to Hide Multiple Columns in Excel
3. Collapse Columns with Excel Ribbon
The Home tab ribbon of Excel provides the option to collapse columns. In this method, we are going to explore that option.
📌 Steps
- Primarily, select columns in the D:F range.
- Then, go to the Home tab.
- After that, select the Format drop-down on the Cells group.
- Later, click on the Hide & Unhide batch under the Visibility section.
- Finally, choose Hide Columns from the available options.
- Hence, here is the expected result, columns D:F are now hidden.
Read More: How to Hide and Unhide Columns in Excel
4. Setting Column Width to Collapse Columns in Excel
Another easy way to collapse columns in Excel is to set the Column Width option. Let’s explore the method step by step.
📌 Steps
- First, select the columns D:F that need to be collapsed.
- Next, move to the Home tab.
- Then, select the Format drop-down on the Cells group.
- Finally, click Column Width from the options.
- Suddenly, it opens the Column Width input box.
- Now, write down 0 in the Column Width box.
- Then, click OK.
- As a result of the above steps, we collapsed columns D:F successfully.
Read More: How to Hide Selected Columns in Excel
Similar Readings
- Excel VBA to Hide Columns Using Column NumberÂ
- Unhide Columns in Excel Shortcut Not Working
- Excel VBA to Hide Columns Based on CriteriaÂ
- Hide or Unhide Columns Based on Drop Down List Selection in Excel
- How to Unhide Columns in Excel
5. Applying Keyboard Shortcut to Collapse Columns
In this instance, I’m aware of your thoughts. Do shortcut keys exist? You’re lucky! Yes, shortcut keys exist to collapse columns more quickly. Follow the steps below.
📌 Steps
- At the very beginning, click on any cell of Column D.
- Then, press the CTRL+SPACEBAR simultaneously.
- Thus, it’ll select the whole column.
- After that, press the SHIFT key and tap the RIGHT ARROW (→) key twice to select from Column D to Column F.
- Finally, press CTRL+0 on your keyboard to get the desired result.
Read More: How to Hide Columns Without Right Click in Excel
6. Applying VBA Code to Collapse Columns in Excel
Employing the VBA code is always an amazing alternative. Follow the steps below to be able to solve the problem in this way.
📌 Steps
- Initially, press the ALT+F11 key.
- Suddenly, the Microsoft Visual Basic for Applications window will open.
- Then, go to the Insert tab.
- After that, select Module from the options.
- It opens the code module where you need to paste the code below.
Sub Collapse_Columns()
Range("D:F").EntireColumn.Hidden = True
End Sub
- Then click on the Run button or press the F5 key on your keyboard.
- After that, return the worksheet VBA.
- Instantly, the worksheet looks like the one below.
Read More: Excel VBA: Hide Columns Based on Cell Value
How to Expand Columns in Excel
In this section, we’ll discuss how to expand columns in Excel. In our previous section, we collapsed columns D:F in numerous ways. Now we’ll expand those columns and make them visible on the display again. So, without further delay, let’s jump into the approach step by step.
📌 Steps
- Firstly, select Column C and Column G.
- Then, go to the Home tab.
- After that, select the Format drop-down on the Cells group.
- Later, click on the Hide & Unhide batch under the Visibility section.
- Finally, choose Unhide Columns from the available options.
- Thus, here is the expected result, columns D:F are now expanded.
Read More: Unhide Columns Is Not Working in ExcelÂ
How to Collapse Rows in Excel
This section will explain how to collapse rows in Excel with suitable examples step by step.
Step-1: Prepare a Suitable and Structured Dataset
Let’s introduce the dataset first.
We have an order list of a bunch of Products in two Categories- Fruit and Vegetables. The dataset also provides the name of the Customer and the Price for each of the orders.
Now, we’ll collapse the rows containing the orders of Fruit. That means rows 5:10.
Step-2: Use Group Feature
- First, select the rows containing orders for the Category- Fruit i.e. (rows 5:10).
- Secondly, go to the Data tab.
- Then, select the Group option on the Outline group.
Step-3: Switch Between (+) and (-) Sign
- The above steps will make the selected rows grouped as indicated on the left side as shown in the image below.
- Now, click on the minus (-) sign shown in the below screenshot.
- Finally, we can see that rows 5:10 are collapsed.
- Again, you can expand these rows by following the steps we’ve shown above.
Read More: How to Hide Columns in Excel with Minus or Plus Sign
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
Conclusion
This article provides easy and brief solutions on how to collapse columns in Excel. Don’t forget to download the practice file. Thank you for reading this article, we hope this was helpful.
