How to Collapse Columns in Excel (6 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Collapse Columns in Excel

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.

How to Collapse Columns in Excel

  • The above steps will make the selected columns grouped as indicated on the upper side as shown in the image below.

How to Collapse Columns in Excel

  • Now, click on the minus (-) sign shown in the image.

How to Collapse Columns in Excel

  • Finally, we can see that columns D:F are collapsed.

How to Collapse Columns in Excel

  • Then, click on the plus (+) sign at the top of Column G.

How to Collapse Columns in Excel

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

How to Collapse Columns in Excel

  • Again, we collapsed the three columns in our dataset.
  • However, you can notice that Column C and Column G are located beside each other.

How to Collapse Columns in Excel

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.

How to Collapse Columns in Excel

  • Finally, we collapsed columns D, E, and F.

How to Collapse Columns in Excel

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.

How to Collapse Columns in Excel

  • Hence, here is the expected result, columns D:F are now hidden.

How to Collapse Columns in Excel

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.

How to Collapse Columns in Excel

  • Suddenly, it opens the Column Width input box.
  • Now, write down 0 in the Column Width box.
  • Then, click OK.

How to Collapse Columns in Excel

  • As a result of the above steps, we collapsed columns D:F successfully.

How to Collapse Columns in Excel

Read More: How to Hide Selected Columns in Excel


Similar Readings


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.

How to Collapse Columns in Excel

  • After that, press the SHIFT key and tap the RIGHT ARROW () key twice to select from Column D to Column F.

How to Collapse Columns in Excel

  • Finally, press CTRL+0 on your keyboard to get the desired result.

How to Collapse Columns in Excel

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.

How to Collapse Columns in Excel

  • Suddenly, the Microsoft Visual Basic for Applications window will open.
  • Then, go to the Insert tab.
  • After that, select Module from the options.

How to Collapse Columns in Excel

  • It opens the code module where you need to paste the code below.
Sub Collapse_Columns()
    Range("D:F").EntireColumn.Hidden = True
End Sub

How to Collapse Columns in Excel

  • Then click on the Run button or press the F5 key on your keyboard.

Employing VBA Code

  • After that, return the worksheet VBA.
  • Instantly, the worksheet looks like the one below.

Employing VBA Code

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.

Using Home Tab Ribbon

  • Thus, here is the expected result, columns D:F are now expanded.

Using Home Tab Ribbon

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.

Preparing Dataset

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.

Using Group Feature


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.

Switching Between Plus and Minus Sign

  • Now, click on the minus (-) sign shown in the below screenshot.

Switching Between Plus and Minus Sign

  • Finally, we can see that rows 5:10 are collapsed.

Switching Between Plus and Minus Sign

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


Related Articles

Shahriar Abrar Rafid
Shahriar Abrar Rafid

Welcome to my profile! I'm thrilled to have you here. As a dedicated Naval Architecture and Marine Engineering graduate from the prestigious Bangladesh University of Engineering & Technology, I am deeply immersed in the realm of research and analysis. My current focus revolves around Microsoft Excel, where I engage in extensive work and conduct insightful research. Through this platform, I share articles that shed light on the vast possibilities of Excel. I'm also an avid reader and passionate traveler, constantly seeking knowledge and implementing it effectively in my work. Join me on this exciting journey as we explore Excel and optimize our productivity together.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo