How to Collapse Columns in Excel (6 Easy Ways)

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.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


6 Ways to Collapse 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

  • At 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.
  • Secondly, go to the Data tab.
  • Thirdly, select the Group drop-down on the Outline group.
  • Fourthly, 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 in 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 (3 Easy Methods)


2. Utilizing 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 (5 Easy Methods)


3. Using Ribbon to Collapse Columns in Excel

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 (7 Quick Methods)


4. Set 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

  • At the very beginning, select the columns D:F that need to be collapsed.
  • Secondly, move to the Home tab.
  • After that, select the Format drop-down on the Cells group.
  • Then, click Column Width from the options.

How to Collapse Columns in Excel

  • Suddenly, it opens the Column Width input box.
  • Now, write down 0 into 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 (5 Easy Methods)


Similar Readings


5. Applying Keyboard Shortcut

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 (3 Ways)


6. Employing VBA Code

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 (15 Examples)


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 (4 Issues & Solutions)


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 of two CategoriesFruit 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

  • At first, select the rows containing orders for the CategoryFruit 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 (2 Quick Ways)


Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

Practice Section


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. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo