How to Group Data in Pivot Table (3 Simple Methods) 

Grouping data with Excel’s PivotTable makes it easy to summarize specific data through filtering. For this purpose, this article aims to guide you on how to group data in the Pivot Table. Moreover, filtering grouped data with Slicers and the process for un-grouping data are also discussed in this article.


Download Practice Workbook

You can download the practice workbook from the link below.


3 Methods to Group Data in Pivot Table 

Microsoft Excel makes the process of grouping data very convenient. So, without further delay, let’s see the step-by-step process of grouping data.

1. Grouping Data by Texts in Pivot Table

Let’s consider the following dataset shown in B4:C14 cells. Here, the first column shows the Item name, while, the second column displays the Sales amount in USD.

Dataset 1

Step 01: Insert a Pivot Table

  • Firstly, select the dataset as shown below and click on the Insert ribbon.
  • Then, you’ll find the PivotTable drop-down on the right where you need to select the From Table/Range option.

Group by Text

  • Next, a dialog box appears in which you have to check the New Worksheet option and press OK.

Group by Text

Step 02: Construct Pivot Table

  • After completing step 1, a new sheet containing your PivotTable will be inserted.
  • Secondly, the pane on the left side is called the PivotTable Fields.

How to Group Data in Pivot Table by Text

  • Following, drag the Sales and Item fields into the Rows and Values fields respectively as shown below.
  • Moreover, you can also check the box beside each field to place them into the Rows and Values fields.
  • Just like that, a table is generated, it’s that easy.

How to Group Data in Pivot Table by Text

  • Next, you can format the numeric values by right-clicking the mouse and selecting the Field Value Settings as portrayed below.

How to Group Data in Pivot Table by Text

  • Now, provide a suitable name in the table header and click on Number Format.

How to Group Data in Pivot Table by Text

  • In turn, select the Currency format and press OK to confirm your selection.

How to Group Data in Pivot Table by Text

Step 03: Group Text Data in Pivot Table

  • Thirdly, select the items that you want to group. As a note, you can multiple items by holding the CTRL key and left-clicking with the mouse.

How to Group Data in Pivot Table by Text

  • In addition, locate the PivotTable Analyze button on the top ribbon.
  • Next, click on the Group drop-down and choose Group Selection. Additionally, you can rename the groups according to your preference from the Formula Bar.

How to Group Data in Pivot Table by Text

  • In a similar fashion, you can group other Text data, as illustrated below.

How to Group Data in Pivot Table by Text

Read More: [Fixed] Excel Pivot Table: Cannot Group That Selection (2 Easy Solutions)


2. Grouping Data by Dates in Pivot Table

In PivotTables, cells with date formatting are automatically grouped into Days, Months, Years, etc. In fact, this is a convenient feature that saves time when you’re in a hurry.  Let’s see the process in detail.

2.1 Grouping Dates Automatically

Suppose we have the following dataset shown in B4:D14 cells. Here, the first column shows the Item name, next, the second column displays the Delivery Date, and lastly, the third column shows the Sales amount in USD.

Dataset 2

Step 01: Navigate to Pivot Table

  • To start, click on the Insert ribbon at the top.
  • Following, select the PivotTable drop-down and choose the From Table/Range option.

Group by Date

  • Then, select the following options in the dialog box as shown in the picture below.

Group by Date

Step 02: Group Dates in the Pivot Table

  • Secondly, drag the field items into the Rows and Values fields such that the table below appears.

How to Group Data in Pivot Table by Date

  • Finally, rename the table headers in the formula bar, thus completing the process of grouping dates.

How to Group Data in Pivot Table by Date

Read More: How to Rename a Default Group Name in Pivot Table (2 Ways)


2.2 Grouping Dates Manually

If you wish to group Dates manually or in a custom format, you can also do that in PivotTable. So, just follow along.

Step 01: Go to the Excel Options

  • Initially, you have to disable the feature that groups Dates automatically.
  • To do this, click on the File tab at the top.

Group by Date

  • Then, go to Excel Options at the bottom of the page.

Using Excel Options

  • Next, select the Data tab and insert a check mark on the option shown below.

Using Excel Options

Step 02: Create a Pivot Table Grouped by Dates

  • In the same way, as described earlier create a PivotTable.
  • Secondly, drag the items into their respective fields as portrayed below in the snapshot.

How to Group Data in Pivot Table by Date

  • Furthermore, select the Year, in this instance 2021.
  • After that, go to the PivotTable Analyze ribbon and select Group Selection from the drop-down.

How to Group Data in Pivot Table by Date

  • Following, a box with the start date (Starting at), the end date (Ending at), and the grouping (By) options appear as shown below.
  • As a note, you can select multiple options from the grouping list.

How to Group Data in Pivot Table by Date

  • Finally, click OK to close the dialog box.
  •  Eventually, you’ll find that the table has been grouped by dates according to your preference.

How to Group Data in Pivot Table by Date

Read More: [Fixed] Excel Pivot Table Not Grouping Dates by Month


3. Grouping Data by Values in Pivot Table

By now, you must have figured out that the last type of data that can be grouped is the numeric value. So, let’s find out the process.

Considering the table shown below in B4:D14 cells. Here, the first column indicates the Store number, following that we have the store size in Square Feet, and lastly, we have a column for the Sales amount in USD.

Dataset 3

Step 01: Make a Pivot Table

  • In the first place, select the entire dataset and go to the Insert ribbon.
  • Then, from the PivotTable drop-down choose the From Table/Range option.

Group by Value

  • Next, a dialog box pops up, where you should select the New Worksheet option to generate the PivotTable in a separate sheet.

Group by Value

Step 02: Group the Pivot Table by Value

  • Secondly, drag the items in the PivotTable Fields in the Rows and Values area respectively.

How to Group Data in Pivot Table by Value

  • Additionally, drag the Sales into the Values field a second time as shown below. The reason for this will become clear, shortly after.

How to Group Data in Pivot Table by Value

  • Next, select the Sum of Sales2 and right-click on the mouse. Now, choose the Value Field Settings.

How to Group Data in Pivot Table by Value

  • Here, type in the column heading name in the Custom Name box and select Count from the list depicted below.

How to Group Data in Pivot Table by Value

  • Now, a Count of Sales column appears beside the Sum of Sales column.

How to Group Data in Pivot Table by Value

  • Then, we can similarly group the store sizes.

How to Group Data in Pivot Table by Value

  • Furthermore, you can group the store sizes into bins. Just enter the start value (Starting at), the end value (Ending at), and the interval (By).

How to Group Data in Pivot Table by Value

  • Subsequently, we get the table as shown below.

How to Group Data in Pivot Table by Value

Read More: How to Use Excel Pivot Table to Group by Different Intervals (3 Methods)


Using Slicer to Filter Grouped Data

A handy feature of Microsoft Excel is the Slicer which allows the user to quickly filter PivotTables. In fact, it is very easy to insert a Slicer into a PivotTable. Just follow along.

Steps:

  • At the very beginning, select the dataset and insert a PivotTable.

Using Slicer

  • Next, make sure to select the New Worksheet option and press OK.

Using Slicer

  • Then, group the data by going to the PivotTable Analyze tab and selecting Group Selection.

How to Group Data in Pivot Table Using Slicer

  • In turn, the various groups appear as shown in the picture below.

How to Group Data in Pivot Table Using Slicer

  • Following, navigate to the PivotTable Analyze tab but this time select Filter and then Insert Slicer.

How to Group Data in Pivot Table Using Slicer

  • Now, a dialog box with the available slicers appears, in this example, Item, and Item2 are selected.
  • As a note, the Item2 heading has been renamed to Category.

How to Group Data in Pivot Table Using Slicer

  • Consequently, the slicers for the Item and Category pop up. Now, you can see the different Categories, the Items listed inside, and the Sum of the Sales amount.

How to Group Data in Pivot Table Using Slicer


How to Ungroup Data in Pivot Table

If you’ve mistakenly added items into the wrong group, then don’t panic because you can ungroup the items just as easily. Let’s see how.

Steps:

  • Firstly, select the items you want to ungroup by holding the CTRL key and left-clicking with your mouse.

Ungroup Data

  • Secondly, locate the PivotTable Analyze tab and click on the Group drop-down.
  • Then, press the Ungroup button and your grouped items will be ungrouped.

Ungroup Data


Things to Remember

Common problems when grouping items in the PivotTable

  • Firstly, you cannot group items if you check the box before the Add this data to the Data Model option.

Handling Error

  •  In fact, if you try to group items, an error message is displayed promptly.

Handling Error

  • Secondly, in older versions of Excel, if blank cells or text data are present in a Date or Numeric field, then you may get an error message when grouping.
  • Finally, check for any duplicate field in the PivotTable Fields list since this indicates the previous grouping of the data.

Conclusion

To conclude, I hope this article provided you with a guide on how to group data in the Pivot Table. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

2 Comments
  1. Hi, how can I find the average of a grouped set of data? The average selection will get the average of the entire set of data

    • Hello FELICIA FOO,
      Thank you for your question. You can find the average of a group by right-clicking on the Row Labels (Sum of Sales) and selecting the Value Field Settings option. Next, in the Summarize value field by list, you’ll find Average.

Leave a reply

ExcelDemy
Logo