One of the core uses of Excel is to insert data to analyze or visualize. For a large amount of data, there can be a lot of columns and rows. Sometimes we may need two or more distant columns to do our work. But we can not get them together as there are multiple columns in between. So one solution is we can hide those columns in between for the time being and unhide them later. In this article, we will learn how to group and hide columns in Excel according to our needs.
When there is a large amount of data having too many columns, in most cases, adjacent or neighboring columns are there for the same purpose or to calculate the final result. So we may need to categorize them in a certain way or group them. One good feature of grouping columns is we can hide them when we need to. There are actually three ways to Group and Hide columns in Excel. All the methods are described below with proper steps. For Example, we will use the following dataset to demonstrate.
1. Using Excel Ribbon to Group and Hide Columns in Excel
We can use Excel Ribbon to group and hide columns in Excel. We will need to follow the steps for that.
Steps:
- First, we need to group the columns. For example, we want to hide the Product ID and Sold Amount. So we will group them first. So we will select the cell range from D4 to E9.
- Second, we will go to the Data tab in the Ribbon and select Group in the Outline section.
- Third, a small selection window will appear. We will select Columns and click on OK.
- Then we will see a small “-” mark has appeared on top of the sheet. This indicates our grouping is done.
- In the end, clicking on the “-” sign will hide the columns like in the image below. Here the “+” sign indicates the grouped columns are hidden there and we can expand.
Read More: How to Hide Columns in Excel with Password
2. Use of Excel Keyboard Shortcuts to Group and Hide Columns
We can also use keyboard shortcuts to directly group the columns. We will follow the steps below.
Steps:
- First, we will select our columns. In our case, we will select columns D and E.
- Next, we will press Shift+Alt+Right Arrow.
- Then, we will get the small Group We will select Columns and press OK.
- Finally, we will get the columns grouped. We will click on the small dash (-) button to hide it.
3. Applying VBA to Group and Hide Columns in Excel
If we are doing a large operation in Excel using VBA, and we need to group and hide our columns for any reason, we can do it through this method. The steps are below.
Steps:
- At first, we need to decide which columns to group and hide. Like the previous example, we will group and hide columns E and F. We will go to the Developer tab in the Ribbon and choose Visual Basic. A window will appear. Or we can directly trigger it by pressing Alt+F11.
- Next, select Insert and then Module in that window. A small writing window will appear.
- Then in that window, we will write the following code:
Sub G_H()
Columns("E:F").Group
End Sub
Here you can give the respective field sequence you need instead of E and F.
- Now we will press Ctrl+S to save the file as an Excel Macro-Enabled Workbook or as a .xlsm file.
- After that, we will again go to the Developer tab and select Macros. A window named Macro will appear.
- In the window select G_H and select Run.
- At last, we will get our column grouped. We will hide it by pressing the “-”.
- Again pressing the “–” sign will give us the following result.
Read More: How to Hide Columns in Excel with Minus or Plus Sign
How to Hide Columns in Excel Without Grouping?
We can also hide the columns without grouping. Here are the methods.
1. Using Excel Context Menu to Hide Columns Without Grouping
In this method, we select a column that we will hide. After selecting the column, right-click to get the Context menu. In the menu, we will select Hide and we will see our column will be hidden.
We will identify the hidden column with this sign in between two columns:
2. Use of Keyboard Shortcut to Hide Columns in Excel Without Grouping
We can also use keyboard shortcuts to hide a column. Simply we will select a column and press Ctrl+O. The column will immediately hide.
Here we hide the B, D, and F columns with keyboard shortcuts.
3. Using Excel Ribbon to Hide Columns
We can select a column or multiple columns, and go to Format in the Cells section in the Home tab. In format select Hide & Unhide. Here we will select Hide Columns and then we will see our column will hide.
After all the steps we will get the following result.
Read More: How to Hide Selected Columns in Excel
Things to Remember
- For all the grouping, we can’t go for multiple selections.
- We can’t hide multiple columns with Context Menu.
- The entire demonstration was done in Microsoft Excel 365. So the interface may vary for different versions.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
The article was all about How to Group and Hide Columns in Excel. We also showed the methods if you want to avoid grouping for hiding. If you have any sort of suggestions or you are still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions.
Related Articles
<< Go Back to Hide Columns | Columns in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
