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.

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.

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

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.

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

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

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

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

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.

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

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

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.

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.

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

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.

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

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.

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

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

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.

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

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

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

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.

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.

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

Step 02: Group the Pivot Table by Value

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

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

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

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

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

• Then, we can similarly group the store sizes.

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

• Subsequently, we get the table as shown below.

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.

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

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