In this tutorial, I will discuss how to group columns in the Excel Pivot Table. The ability to group data in subsets is one of the useful features in Pivot Tables. You can group data date-wise, month-wise, and so on. But, those groupings are limited to Row Labels. Grouping columns is a bit tricky. For example, we have a dataset containing date-wise sales data at different stores like below. Now, we will create a Pivot Table based on these data and group them into Column Labels.
1. Applying PivotTable and PivotChart Wizard to Group Columns in Pivot Table
We cannot group columns by simply inserting a Pivot Table. For instance, in this method, I will use the PivotTable and PivotChart Wizard to create the Pivot table first and then group it into columns. Follow the below steps to create the expected Pivot Table.
Steps:
- First, go to the source data sheet and press Alt + D + P from the keyboard.
- As a result, the PivotTable and PivotChart Wizard will show up. Click on the Multiple consolidation ranges and PivotTable options as below screenshot and press Next.
- Then, click on I will create the page fields option like below and select Next.
- Now, click on the right-side arrow of the Range.
- Select the range for our Pivot Table.
- After you enter the range again Click Next.
- Choose the New Worksheet option as below and press Finish.
- After following the above steps, finally, we got the Pivot Table as we wanted. Now, notice the header of the Pivot Table, you will see the Column Labels drop-down icon. Now, from this Pivot Table, we will group sales data.
- To group data of Sales 1 and Sales 2 columns, select them first.
- Then go to the PivotTable Analyze tab from the ribbon and select Group Selection.
- As a consequence, Sales 1 and Sales 2 Columns are grouped together.
- You can rename the group name too as below.
- Similarly, you can group the columns Sales 3 and Sales 4 and get the following result.
Read More: How to Make Group by Same Interval in Excel Pivot Table
2. Using Excel Power Query Editor to Group Columns in Pivot Table
We can create a Pivot Table using the Power Query Editor in Excel and thus group columns. Let’s have a look at the steps involved in this process.
Steps:
- First, go to the source dataset and press Ctrl + T. Next the Create Table dialog box will pop up. Check the range of the table is specified correctly, then press OK.
- As a result, the below table is created.
- Now, from Excel Ribbon, go to Data > From Table/Range.
- Then the Power Query Editor window will show up. By default, our table data will be displayed with an autogenerated query.
- Next, select the below columns (see below screenshot).
- After that, from the Power Query Editor window go to Transform > Unpivot Columns > Unpivot Only Selected Columns.
- As a result, we will get the below data in the Power Query Editor.
- Again from the Power Query Editor window and go to Home > Close & Load > Close & Load.
- As a result, you will get the below table in Excel main window.
- Now select the above table and from Excel Ribbon go to Table Design > Summarize with PivotTable.
- Subsequently, PivotTable from table or range dialog box will show up. Check the Table/Range field and click on the New Worksheet option, and press OK.
- As a result, a blank Pivot Table will be created.
- Now, you have to set the row/column values for the Pivot Table. To do that, click on the blank Pivot Table and go to the PivotTable Fields Then, drag the Date on Rows, Attribute on Columns, and Value on Values fields one by one.
- Finally, here is our expected Pivot Table where we can group columns.
- Then, similar to Method 1, I have grouped columns as below.
Read More: Pivot Table Custom Grouping
Ungroup Columns in Excel Pivot Table
You can easily ungroup columns in the Pivot Table from the Pivot Table Analyze tab.
Steps:
- First, click on the group name.
- Then go to PivotTable Analyze > Ungroup.
- As a result, columns will be ungrouped.
⏩ Note:
You can group/ungroup simply using the mouse right-click as below.
Read More: How to Group Rows in Excel Pivot Table
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
Conclusion
In the above article, I have tried to discuss two methods to group columns in the Pivot Table elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.
