Pivot Table Group by Custom Fields and Rules

Updated 5 November 2026 · Standard formula, shown below

Yes, you can group items in a pivot table by custom rules, but the method depends on the spreadsheet app and the kind of grouping you need. Excel and Google Sheets both support useful grouping workflows, though manually creating a source-data category column is usually the most flexible and reliable approach.

What custom grouping means

A pivot table summarizes records into rows, columns, and values. Grouping changes how those records are collected before the summary is calculated. For example, you might group individual dates into months, ages into ranges, or product codes into business-defined categories such as “Core,” “Seasonal,” and “Clearance.”

There are two different meanings of “custom” that are easy to confuse:

Grouping existing items: Select several labels already appearing in a pivot table and combine them under a group, or set numeric/date intervals.
Defining a classification rule: Add a field to the source data that assigns each record to a category according to your own logic.

The first is convenient for a one-off summary. The second is better when categories must be consistent, reusable, or understandable to other people.

Group items directly in Excel

Excel desktop pivot tables can group selected text items and group numeric or date fields. The exact available commands can depend on the field and the version of Excel, but the general workflow is straightforward:

Build a pivot table from a clean source range or table.
Place the field you want to group in the Rows or Columns area.
In the pivot table, select the labels to combine. For nonadjacent labels, use the usual multi-select modifier for your operating system.
Right-click a selected label and choose Group, if the command is available.
Rename the resulting group to a useful label, then refresh the pivot table if the source data changes.

For numbers or dates, select a value in the field and use the grouping command to specify a starting point, ending point, and interval. For instance, a transaction-date field can be grouped by months or quarters, while an age field can be grouped into intervals of ten. If Excel refuses to group dates, check that every source value is a valid date rather than text or a blank.

Directly grouped items are convenient, but they can need maintenance. New source values may not automatically belong to the intended custom group, and the grouping is less transparent than a category explicitly stored in the data.

Use a helper column for rules you control

A helper column is an added source-data field that calculates or records the category for each row. This is the most dependable way to implement detailed or recurring custom logic. Suppose a sales table has a Product column and you want “Accessories” and “Other.” Add a Category column, assign each product to one of those labels, and use Category as the pivot table’s row field.

For range-based categories, a formula can classify values. For example, an age field might be assigned “Under 18,” “18–34,” “35–54,” or “55+.” The formula should use clearly defined boundaries so a value cannot fall into two groups or no group. In Excel, an IF-based formula or a lookup table can implement the rule; in Google Sheets, equivalent formulas or a mapping table can do the same.

Use a lookup table when many source labels map to a smaller set of categories.
Use a formula when the category follows a numeric or date rule.
Use manually entered categories when a person, rather than a formula, determines the classification.

Keep the mapping or formula alongside the source data, document any important boundary choices, and make sure all rows receive a category. This makes the pivot table easier to refresh and audit.

Grouping in Google Sheets

Google Sheets pivot tables provide grouping for supported date and numeric fields, but they do not offer the same general-purpose workflow as Excel for combining arbitrary text labels into a custom group directly inside the pivot table. For custom text categories, create a helper column in the source data and add that column to the pivot table.

For example, if a pivot table lists cities and you want to report by region, add a Region field that maps each city to North, South, East, or West. Then refresh or update the pivot table’s source range as needed and summarize by Region instead of City. A separate mapping table is often safer than a long nested formula when the list changes.

Spreadsheet features and menus can change, so if a grouping option is missing, first confirm the field’s data type and check whether your goal is better handled by a source-data category.

Choose the right method

Choose direct pivot-table grouping when you need a quick summary of a stable set of labels, or when you want standard date and numeric intervals. Choose a helper column when the classification represents a business rule, must work across multiple reports, or needs to include future records consistently.

A useful test is whether someone reviewing the source data should be able to see why a record belongs to a category. If the answer is yes, put the category or its mapping rule in the source data. If the grouping is only a temporary presentation choice, grouping within the pivot table may be enough.

Common problems and fixes

Group is unavailable: The field may contain mixed data types, blanks, errors, or text where numbers or dates are expected. Clean the source column and refresh.
Some records are missing: Check the pivot table’s source range, filters, and helper-column formulas. New rows may sit outside a fixed source range.
New labels appear as ungrouped items: Update the direct grouping or, preferably for ongoing reports, add the new label to the category mapping.
Dates group incorrectly: Confirm the source values are actual dates and use a consistent date system and format. Display formatting alone does not turn text into dates.
Numbers land in unexpected ranges: Check interval size and boundary definitions. Decide whether an endpoint belongs in the lower or upper band and apply that rule consistently.

Also distinguish grouping from sorting: sorting changes the order of items, while grouping combines records into categories. Grouping does not change the underlying source values, but it does change how the pivot summarizes them.

Frequently Asked Questions

Can I create custom groups in an Excel pivot table?
Yes. You can group selected items and set intervals for supported numeric or date fields. For complex or reusable rules, add a category field to the source data instead.

Can Google Sheets group text items in a pivot table?
For custom text categories, the dependable approach is to add a helper column that assigns each record to a group, then use that field in the pivot table. Sheets supports grouping for some date and numeric fields.

Why can’t I group dates in my pivot table?
Some entries may be stored as text, blank, or otherwise not recognized as dates. Clean the source column, make its values consistent, and refresh the pivot table.

Will custom groups update when I add new data?
They update only if the pivot table includes the new rows and the new values are assigned appropriately. A helper-column formula or maintained mapping table makes the intended category explicit.

Should I group in the pivot table or the source data?
Use pivot-table grouping for quick, simple summaries. Use a source-data field for repeatable classifications, complex rules, or reports that other people need to understand and maintain.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.