This will enable you to easily show and hide different areas of the worksheet so that only relevant data is visible. In this tutorial, I will show you three methods to group columns in Excel. Note: Ensure that the worksheet does not have any hidden columns before applying any of the following methods.
Method #1: Select the Columns to be Grouped and Apply the Group Command
When we use this method, we first select the columns that we want to group and then apply the Group command. The following dataset shows the sales of various tablets for three quarters. In the above dataset, I want to group all the columns between the Tablet column and the Grand Total Column (so that all the months and quarters sales data could be easily hidden with a click). Below are the steps to group columns in Excel: Alternatively, you can use the keyboard shortcut Shift + Alt + Right Arrow. Level 1 outline is created as shown below (a gray line appears over the columns that have been grouped): When you click the outline number 1 button in the top left corner (or you click the minus button in the top right corner), all the columns within the group are hidden, as shown below: To unhide the columns, click the plus (+) button on top of the group.
How to Create Inner Groups
In the above example, we have created only one outline where all the selected columns were grouped. Now you can follow the same steps and create inner groups within the main group. For example, in our data set, we have grouped all the month’s data together as the main outline, and then we can also create inner groups for each quarter. Below is our dataset where we have a Level 1 outline where all the columns in between columns A and N are grouped. We want to create an inner group of columns within this group. Below are the steps to create an inner group of columns: Alternatively, you can use the keyboard shortcut Shift + Alt + Right Arrow. The inner group is created: Note that because only adjacent columns can be grouped, we will have to repeat the process for any other group we want to create. We can group the three columns preceding the Q2 Total column and the three columns preceding the Q3 Total column in the same way. We now have 2 levels of column groups:
One outer level 1 group is made up of columns B to M. Three inner level 2 groups; columns B-D, columns E-H, and columns J-K.
To collapse all the inner groups, click the level 2 button on the left of the groups. To collapse each inner group click the minus (-) button on top of the group.
Method #2: Select Cells in the Columns to be Grouped and Apply the Group Command
This method is a variation of Method #1. Instead of selecting the columns to be grouped and then applying the Group command, we select cells in the columns we want to group and apply the Group command. The following dataset shows the sales of various tablets for three quarters. We want to group all the columns between the Tablet and the Grand Total column. Below are the steps to group columns in Excel: Alternatively, you can use the keyboard shortcut Shift + Alt + Right Arrow. Level 1 outline is created as shown below: Once the outline has been created, you can quickly hide the columns by clicking on the minus button at the top right part of the worksheet (the minus button is at the end of the gray line). As soon as you click on the minus button, all the columns you have grouped will be hidden, and you will get the result as shown below. To unhide the columns click the plus (+) button on top of the group.
Method #3: Use the Auto Outline Option
If your dataset has a predictable pattern, you can use the Auto Outline option in the Outline group to group the columns automatically. The following dataset has a predictable pattern. Columns B, C, D, F, G, H, J, K, and L contain the same type of data, the monthly sales figures for various tablets. Each of the columns E, I, and M contains the summation of the three preceding columns. Column N contains the Grand Total of columns E, I, and M. We can easily group columns in this dataset using the Auto Outline option. We use the following steps to group the columns between the Tablet column and the Grand Total column. Levels 1 and 2 outlines are created as shown below: The columns of the dataset have been grouped into one level 1 outline and three level 2 outlines. There are three level 2 outlines because the data represents the tablet sales for three quarters. When you click the outline number 1 button in the top left corner (or the minus button in the top right corner at the end of the gray line), all the columns within the group are hidden, as shown below: We can unhide the columns or expand the group by clicking the plus (+) button on top of the group. To hide the months in groups of threes, click on the minus (-) buttons of the level 2 outline. In the following example, we have clicked the first minus (-) button of the level 2 outline to hide the first three months: To collapse all the level 2 groups, we click the level number 2 button on the left of the outline area:
How to Group Columns When Summary Columns are On the Right of Detail Columns
Sometimes your dataset may have the summary columns on the left of the detail columns, as shown below: You need to do the following before using the Auto Outline feature: You can now use the Auto Outline option as explained earlier in this method, and the result will be as follows: Notice that the Auto Outline feature has generated only one level of grouping with three groups, and the minus (-) buttons are on the left of the groups and not on the right.
How to Ungroup Particular Columns
To ungroup particular columns do the following: Alternatively, you can use the shortcut Shift + Alt + Left Arrow Key. Columns B-D are ungrouped as seen below:
How to Clear an Outline in Grouped Columns
To remove all the column groupings, do the following: The other steps would instantly remove the outline in all the column groupings would be gone. In this tutorial, I covered some methods you can use to group columns in Excel quickly. If your data is arranged where there’s a predictable pattern, you can use the Auto Outline option, which would automatically create one or more levels of grouping for your data set. You can also manually select the columns (or the cells in the columns you want to group) and then group them using the options in the ribbon. Other Excel articles you may also like:
How to Group Worksheets in Excel How to Group Numbers in Pivot Table in Excel Create Groups in Quick Access Toolbar in Excel How to Freeze Multiple Columns in Excel? Delete Blank Columns in Excel (3 Easy Ways + VBA) Select Till End of Data in a Column in Excel (Shortcuts) How to Delete All Hidden Rows and Columns in Excel How to Quickly Unhide COLUMNS in Excel (A Simple Guide)
