Pivot Table Issues

How to Group Items in Excel PIVOTBY Like a PivotTable

Nimra Malik Sep 25, 2026 871 views

Question details

The user wants to group related values, such as specific revenue or cost codes, when using the PIVOTBY function in Excel, aiming to replicate the interactive grouping feature of a traditional PivotTable.

How to Group Items in Excel PIVOTBY Like a PivotTable

Product: Microsoft Excel
Device & OS: not provided
Scenario: Organizing and categorizing dynamic array data generated by the PIVOTBY function based on specific item prefixes or codes.
Observed behavior: The PIVOTBY function lacks a native, interactive grouping feature like standard PivotTables, requiring users to structure or group the source items prior to applying the function.

Before you start

Ensure your version of Excel supports the PIVOTBY function (available in Microsoft 365) and that your source data is formatted as an Excel Table for seamless automatic updates.

Solution 1Recommended

Use Power Query to Group Data Before Pivoting

The most efficient way to group items for a PIVOTBY output is to categorize the raw data using Power Query first, which avoids complex nested formulas and VBA macros.

Power Query allows you to clean and categorize your data before it reaches your PIVOTBY formula. By creating conditional columns, you can easily group specific revenue or cost-of-sales codes into parent categories.

Load data into Power Query

Select your source data range, go to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Add a conditional category column

Go to the Add Column tab and click 'Conditional Column'. Set up rules to group your items, such as: If 'Code' begins with '427', output 'Revenue'.

Load the grouped data back to Excel

Once your items are categorized, click 'Close & Load' on the Home tab to output the organized data into a new worksheet.

Apply the PIVOTBY function

Write your =PIVOTBY() formula referencing the newly loaded Power Query table, using the new conditional column as your row or column grouping field.

Use Power Query to Group Data Before Pivoting

Refreshable Setup: Whenever new data is added, simply click 'Refresh All' on the Data tab. Power Query will automatically group the new items, and your PIVOTBY array will update instantly.

Solution 2

Use Helper Columns with IF or IFS Functions

If you prefer to stay on the spreadsheet without using Power Query, you can categorize data directly in the source table using standard Excel logic functions.

Free Microsoft Office alternative

Easily Group Data with Built-in PivotTables in WPS Office

While Excel's dynamic array functions like PIVOTBY are useful, WPS Office Spreadsheet provides robust, traditional PivotTables that allow you to group data interactively without needing helper formulas or Power Query workarounds. WPS Office is lightweight, completely free, and perfectly compatible with Excel formats.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your .xlsx file, and ensure your data has clear column headers.
2. Insert a PivotTable: Go to the Insert tab, click PivotTable, and select where you want the report to be placed.
3. Group your items interactively: Drag your item codes into the Rows area. Highlight the items you want to combine, right-click, and select 'Group' to instantly categorize them without formulas.

Interactive drag-and-drop PivotTable groupingHighly compatible with Microsoft Excel (.xlsx) filesFree and lightweight alternative to Microsoft 365Familiar, easy-to-navigate user interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I group items directly inside the PIVOTBY function without helper columns?

No, the PIVOTBY function dynamically aggregates existing data but does not have a built-in mechanism to manually group discrete text items or codes. You must categorize your data in the source table using helper columns or Power Query before applying PIVOTBY.

What is the PIVOTBY function in Excel?

PIVOTBY is a dynamic array function available in Microsoft 365 that allows users to group, aggregate, and sort data using a single formula. It acts as a lightweight, formula-based alternative to standard PivotTables.

Why use Power Query over VBA for grouping Excel data?

Power Query offers a visual, user-friendly interface to clean and categorize data. It is easier to maintain, updates automatically when refreshed, and completely avoids the security prompts and coding complexity associated with VBA macros.

Will PIVOTBY formulas update automatically when my source data changes?

Yes, if your source data is formatted as an official Excel Table (Insert > Table), the PIVOTBY formula will dynamically resize and update its output to reflect any new, modified, or deleted data automatically.

Excel & Data Problems