Grouping PivotTable Data

1. Grouping by Date and Time Intervals

One of the most common analytical tasks is to summarize data over time. Your source data might contain daily transactions, but you'll almost always want to see trends by week, month, quarter, or year. PivotTables make this incredibly easy.

When you add a date field to a PivotTable, modern versions of Excel often group it automatically. However, knowing how to control this grouping manually is essential for creating the exact report you need. The process involves right-clicking a date cell in your PivotTable, selecting Group, and then choosing the time intervals you want.

Let's watch a short demonstration of how this works in practice.

For a more detailed explanation and a crucial tip, let's turn to a written resource. It's important to understand not just how to group, but also the common pitfalls to avoid.

2. Grouping by Numerical Ranges

Just as dates can be too granular, so can numerical fields like price, age, or sales amount. If you want to create a frequency distribution—for instance, to see how many sales fall into different price brackets—you need to group the numbers into "bins" or "buckets."

The process is similar to date grouping: place the numerical field in the Rows area, right-click, and select Group. Excel will then present a dialog box asking for a starting value, an ending value, and an interval size (the "By" field).

The following video provides a perfect, concise demonstration of creating numerical groups to analyze a distribution of house prices. It also effectively connects to our previous lesson by using COUNT in the values area to see how many properties fall into each price bin.

To solidify this, you can read about the Grouping dialog box for numeric fields in more detail.

3. Custom Grouping of Text Items

The third type of grouping is the most flexible. It allows you to manually select several text-based items in your PivotTable and bundle them into a new, custom category. This is incredibly useful when you need to create summaries that don't correspond to any existing column in your data. For example, you could:

• Group a list of 50 states into four sales regions (West, Midwest, South, East).
• Group a list of products into "Premium" and "Standard" tiers.
• Group individual employees into project teams.

This is done by holding the Ctrl key, selecting the row labels you want to group, then right-clicking and choosing Group. Excel creates a new field for your custom groups, which you can rename and use just like any other field.

A business-focused case study can help you see how this applies in a professional context.

4. Hands-On Practice

Now it's time to apply these three grouping techniques. Please open the same practice workbook from PivotXL.com that you used in the last lesson (downloadable from the "Pivot Table Practice Examples in Excel" article if needed). Use the Transactions sheet for the following tasks, creating each PivotTable on a new worksheet.

Practice Task 1: Monthly Sales Report (Date Grouping)

1. Create a PivotTable.
2. Put Amount in the Values area (it should default to Sum of Amount).
3. Put Order Date in the Rows area.
4. Right-click on any of the dates in the PivotTable, select Group.
5. In the Grouping dialog, make sure Months and Years are both selected, then click OK.
6. You should now see a summary of total sales for each month within each year.

Practice Task 2: Sales Frequency Distribution (Numerical Grouping)

1. Create a new PivotTable.
2. Put Amount in the Rows area. This will create a very long list.
3. Right-click on any of the amount values, select Group.
4. In the Grouping dialog, set Starting at to 0, Ending at to 1000, and By to 100. Click OK. Your rows are now grouped into buckets of 100.
5. Drag Amount into the Values area. It will default to Sum of Amount.
6. Click on the Sum of Amount field in the Values area, select Value Field Settings, and change the calculation to Count. Now your report shows how many transactions occurred within each $100 sales bracket.

Practice Task 3: Team Performance Report (Custom Grouping)

1. Create a new PivotTable.
2. Put Sales Rep in the Rows area and Amount in the Values area.
3. Now, create two teams. Hold down the Ctrl key and click on the names Jones, Kivell, and Jardine.
4. Right-click on your selection and choose Group. Excel will create "Group1".
5. Click on the cell that says "Group1" and type Team Alpha to rename it.
6. Repeat the process for the remaining reps: select Howard, Morgan, Parent, and Sorvino. Group them and rename the group Team Bravo.
7. In your PivotTable Fields list, you'll see a new field called Sales Rep2. Click it, go to Field Settings, and change its Custom Name to Sales Team. You now have a report showing total sales by your newly created teams.

Conclusion

You have now added a crucial set of tools to your data analysis arsenal. By mastering grouping, you can move beyond the limitations of your raw data and create summaries at the exact level of detail your analysis requires.

Key Takeaways:

• Date Grouping: Consolidate daily data into weeks, months, quarters, or years to analyze trends over time. Remember to include years if your data spans more than one.
• Numerical Grouping: Create bins or buckets for numerical data to build frequency distributions, which are essential for understanding the spread of your data.
• Custom Grouping: Manually select and group text items to create your own categories on the fly, allowing for flexible analysis without altering your source data.

In our previous lessons, we have always worked with a single, flat table of data. In our next lesson, we will break through this limitation by exploring the Data Model. You will learn how to create a single PivotTable that combines data from multiple, related tables—a foundational skill for building more sophisticated reports.