How to Group Columns in Excel Pivot Table (2 Methods)

In this tutorial, I will discuss how to group columns in 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 in Column Labels.

How to Group Columns in Pivot Table


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


2 Methods to Group Columns in Excel Pivot Table

1. Apply 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.

Apply PivotTable and PivotChart Wizard to Group Columns in Pivot Table

  • Then, click on I will create the page fields option like below and select Next.

Apply PivotTable and PivotChart Wizard to Group Columns in Pivot Table

  • Now, click on the right-side arrow of the Range.

Apply PivotTable and PivotChart Wizard to Group Columns in Pivot Table

  • Select the range for our Pivot Table.

Apply PivotTable and PivotChart Wizard to Group Columns in Pivot Table

  • After you enter the range again Click Next.

Apply PivotTable and PivotChart Wizard to Group Columns in Pivot Table

  • Choose the New Worksheet option as below and press Finish.

Apply PivotTable and PivotChart Wizard to Group Columns in Pivot Table

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

Apply PivotTable and PivotChart Wizard to Group Columns in Pivot Table

  • Then go to the PivotTable Analyze tab from the ribbon and select Group Selection.

Apply PivotTable and PivotChart Wizard to Group Columns in Pivot Table

  • As a consequence, Sales 1 and Sales 2 Columns are grouped together.

Apply PivotTable and PivotChart Wizard to Group Columns in Pivot Table

  • You can rename the group name too as below.

  • Similarly, you can group the column Sales 3 and Sales 4 and get the following result lastly.

Read more: Pivot Table Custom Grouping


Similar Readings


2. Use 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.

Use Excel Power Query Editor to Group Columns in Pivot Table

  • As a result, the below table is created.

Use Excel Power Query Editor to Group Columns in Pivot Table

Use Excel Power Query Editor to Group Columns in Pivot Table

  • Then the Power Query Editor window will show up. By default, our table data will be displayed with an autogenerated query.

Use Excel Power Query Editor to Group Columns in Pivot Table

  • Next, select the below columns (see below screenshot).

Use Excel Power Query Editor to Group Columns in Pivot Table

  • After that, from the Power Query Editor window go to Transform > Unpivot Columns > Unpivot Only Selected Columns.

Use Excel Power Query Editor to Group Columns in Pivot Table

  • As a result, we will get the below data in the Power Query Editor.

Use Excel Power Query Editor to Group Columns in Pivot Table

  • Again from the Power Query Editor window and go to Home > Close & Load > Close & Load.

Use Excel Power Query Editor to Group Columns in Pivot Table

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

Use Excel Power Query Editor to Group Columns in Pivot Table

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

Use Excel Power Query Editor to Group Columns in Pivot Table

  • 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 Date on Rows, Attribute on Columns, and Value on Values field one-by-one.

Use Excel Power Query Editor to Group Columns in Pivot Table

  • 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: How to Make Group by Different Intervals in Excel Pivot Table


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.

Ungroup Columns in Excel Pivot Table

  • Then go to PivotTable Analyze > Ungroup.

Ungroup Columns in Excel Pivot Table

  • As a result, columns will be ungrouped.

Note:

You can group/ungroup simply using the mouse right-click as below.


Conclusion

In the above article, I have tried to discuss two methods to group columns in Pivot Table elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Further Readings

Tags:

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo