by This article explores the Excel PIVOTBY function, its features, and practical applications for insightful decision-making.
For decades, Excel users have relied on pivot tables to transform rows and columns of information into meaningful reports. Now, there's something even better: the new PIVOTBY function. This handy tool lets you summarize and analyze data right within a formula without the need for separate pivot tables. It's fully dynamic, updates in real time, and fits perfectly into your existing workflows.
Excel PIVOTBY function
PIVOTBY function in Excel is designed to group data by rows and columns and aggregate the associated values. Additionally, it can do sorting and filtering of the results.
PIVOTBY is a dynamic array function. As such, it automatically spills the results into as many cells as needed. The output is a dynamic summary report similar to a pivot table, but without formatting.
Note. Excel PIVOTBY is similar to the GROUPBY function. The key difference is that while GROUPBY can only group rows, PIVOTBY can group data across both rows and columns.
Syntax
The syntax of the PIVOTBY function is as follows:
The function can take quite a lot of arguments, but only the first four are required.
- Row_fields (required) - a range of values to group in rows.
- Col_fields (required) - a range of values to group in columns.
- Values (required) - the values to aggregate.
- Function (required) - the function to summarize the grouped data such as SUM, AVERAGE, COUNT, MEDIAN, etc.
- Field_headers [optional] - specifies whether to include headers in the results:
- 0 - No headers
- 1 - Yes, but don't show
- 2 - No headers, but generate them
- 3 - Yes, and show
If omitted, the field headers are normally not displayed, unless multiple aggregations are calculated.
- Row_total_depth [optional] - determines whether to show total rows:
- 0 - No totals
- 1 - Grand totals at the bottom (default)
- 2 - Grand totals and subtotals at the bottom
- -1 - Grand totals at the top
- -2 - Grand totals and subtotals at the top
- Row_sort_order [optional] – a number indicating how to sort rows. Numbers correspond to columns in row_fields followed by the columns in values. Positive numbers sort in ascending order, negative numbers - in descending order. To sort by multiple columns, provide a one-dimensional array of numbers. Default is ascending sorting.
- Col_total_depth [optional] - determines whether to include total columns:
- 0 - No totals
- 1 - Grand totals on the right side (default)
- 2 - Grand totals and subtotals on the right side
- -1 - Grand totals on the left side
- -2 - Grand totals and subtotals on the left side
- Col_sort_order [optional] - a number indicating how to sort columns. Numbers correspond to columns in col_fields followed by the columns in values. Works with the same logic as row_sort_order.
- Filter_array [optional] - filtering out specific rows. Can be provided as a logical expression that generates a one-dimensional array of Boolean values matching the length of the row_fields and col_fields arrays.
- Rrelative_to [optional] – when the PERCENTOF function is used for aggregation, specify how the calculation is performed relative to:
- 0 - Column totals (default)
- 1 - Row totals
- 2 - Grand total
- 3 - Parent Col Total
- 4 - Parent Row Total
Usage notes
To maximize your use of the PIVOTBY function in Excel, grasp these handy tips:
- Function. For aggregating values, you can choose from a list of predefined functions. These functions are technically eta-reduced lambdas, which operate without requiring any arguments. Alternatively, you can create your own custom lambda function.
- Headers. When the field_headers argument is not specified, Excel infers header presence based on the values argument. If the first value is textual and the second is numerical, it assumes headers exist.
- Consistent range lengths. To avoid errors, supply ranges of equal lengths for the fields and values arguments.
- Dynamic formula behavior. The PIVOTBY function updates automatically with source data changes. However, if you add new rows, the formula won't update unless additional currently empty rows are included in it. Converting your dataset into an Excel table will make the formula auto-expandable and fully dynamic.
- Auto-formatting results. To improve the visual aspect of your presentation, you might want to use conditional formatting to format the results automatically according to specified criteria.
Note. The PIVOTBY function is still under development. As such, it is not recommended to use it in important workbooks or critical tasks, as its functionality may change after a final release.
PIVOTBY availability in Excel
The PIVOTBY is only available in Excel for Microsoft 365 (currently, exclusively for Office Insiders in the Beta Channel).
Basic Excel PIVOTBY formula
To grasp the concept of the PIVOTBY function, let's dive into a basic example. Imagine you have a dataset as shown in the screenshot below, with products listed in (B3:B32), countries in (C3:C32), and amounts in (D3:D32). You wish to pivot the data by grouping products in rows and countries in columns. For the aggregation function, let's choose SUM to get total sales for each product-country pair.
The complete formula takes this form:
=PIVOTBY(B3:B32, C3:C32, D3:D32, SUM)
As a result, you'll get a dynamic summary table where the products are arranged down the leftmost column, and the countries are listed across the top row. Since we haven't specified any optional arguments, Excel will apply the default settings: no headers, ascending sort order, and no filtering. A total row will appear at the bottom and a total column on the right, which is the default behavior for the total_depth arguments.
This simple example demonstrates how the Excel PIVOTBY function can be used to create a dynamic aggregated view of your data, similar to a traditional pivot table but with the flexibility of using formulas.
How to use PIVOTBY function in Excel - formula examples
Now that you're familiar with the basics of the PIVOTBY function, let's delve into practical examples of how to effectively use it in your Excel formulas.
Group based on multiple columns
The Excel PIVOTBY function supports grouping by multiple columns, which is incredibly handy when you need to analyze complex datasets.
Grouping by adjacent columns
To group by adjacent columns, you simply select a multi-column range for the row_fields and/or col_fields arguments.
For example, to organize products (B3:B32) and countries (C3:C32) in rows and sellers (D3:D32) in columns, you can use the following formula:
=PIVOTBY(B3:C32, D3:D32, E3:E32, SUM)
Grouping by non-adjacent columns
If you need to group your data based on non-contiguous columns, you can utilize the CHOOSECOLS function to pick the desired columns. For instance, to group products and sellers in rows, and countries in columns, the formula would look like this:
=PIVOTBY(CHOOSECOLS(B3:D32, 1,3), C3:C32, E3:E32, SUM)
In this formula, CHOOSECOLS selects the 1st and 3rd columns from the range B3:D32.
The same result can be achieved by using the HSTACK function, which stacks the specified arrays horizontally:
=PIVOTBY(HSTACK(B3:B32, D3:D32), C3:C32, E3:E32, SUM)
Both methods allow you to create a dynamic summary that groups your data exactly how you need it, providing a clear and organized view of your information.
Aggregate multiple columns
Occasionally, it becomes necessary to summarize multiple columns or perform various aggregations on the same metric.
Aggregating contiguous columns
To aggregate adjacent columns, simply select a range that includes all the columns you want to summarize. For example, to calculate the average unit price and amount for each product-country combination, you can use this formula:
=PIVOTBY(B2:B32, C2:C32, E2:F32, AVERAGE, 3, 1, ,0)
The formula includes the range E2:F32 to aggregate both the Unit Price and Amount columns. The optional arguments are set to display field headers (3 for field_headers) and avoid adding the total column (0 for column_total_depth).
Summarizing non-contiguous columns
To aggregate non-adjacent columns, you can use either the CHOOSECOLS function to specify the target column numbers or HSTACK to combine them into a single array.
For instance, to average the Qty. (D2:D32) and Amount (F2:F32), columns, the formulas are:
=PIVOTBY(B2:B32, C2:C32, HSTACK(D2:D32, F2:F32), AVERAGE, 3, , ,0)
=PIVOTBY(B2:B32, C2:C32, CHOOSECOLS(D2:F32, 1, 3), AVERAGE, 3, , ,0)
Perform multiple aggregations on the same column
To use different functions for aggregating the same columns of values, you need to provide a vector of the corresponding lambda functions. This can be achieved with the help of HSTACK.
For example, to get to get both the average and count of sales, the formula is:
=PIVOTBY(B2:B32, C2:C32, F2:F32, HSTACK(AVERAGE, COUNT), , , ,0)
This approach allows for a comprehensive analysis of your data with the flexibility to summarize it in various ways.
Group data only in rows or columns
While the PIVOTBY syntax marks both row_fields and col_fields as required, you have the flexibility to group data exclusively in rows or columns by setting only one of these arguments.
Grouping in rows
If you omit the col_fields argument, the function will group data in rows, similar to the GROUPBY function. For example:
=PIVOTBY(B2:B32, , D2:D32, SUM)
Grouping in columns
Conversely, if you omit row_fields, it will group data solely in columns. For example:
=PIVOTBY(, C2:C32, , D2:D32, SUM)
This versatility lets you fine-tune your data summaries to different scenarios.
Show grand total and subtotals
To include grand totals and subtotals in your PIVOTBY results, you can adjust the row_total_depth and col_total_depth arguments accordingly.
For example, if you want to display row totals and subtotals at the bottom, and no totals for columns, you would set row_total_depth to 2 and col_total_depth to 0:
=PIVOTBY(B2:C32, D2:D32, E2:E32, SUM, , 2, , 0)
This setup will result in a summary table where each row group includes a subtotal at the bottom, followed by a grand total row at the very end, while columns will not display any totals.
Note. Ensure that the total_depth value matches the level of grouping in your row_fields and col_fields. For instance, if you set a depth value that includes subtotals (2 or -2), make sure you have multiple levels of grouping in the corresponding fields.
Custom sorting
By default, the PIVOTBY function arranges the row and column labels in ascending order from A to Z.
To customize the sorting in your PIVOTBY results, you can use the row_sort_order and col_sort_order arguments. Here's how you can apply custom sorting:
- Ascending order - use positive numbers for the columns you want to sort from A to Z.
- Descending order - use negative numbers for the columns you want to sort from Z to A.
- Sorting by multiple columns - provide a one-dimensional array of numbers that correspond to the columns in row_fields or col_fields. You can sort different columns in different orders.
In the below example, we have a 2-level row grouping (by product and country) and a one-level column grouping (by seller). By default, all would be sorted ascending.
To sort rows and columns from Z to A, supply the array {-1, -2} for row_sort_order to arrange columns 1 and 2 in row_fields (B2:C32) in descending order. For col_sort_order, use -1 to sort the only column in col_fields (D2:D32) in descending order as well.
=PIVOTBY(B2:C32, D2:D32, E2:E32, SUM, , 0, {-1,-2}, 0, -1)
As a result, you'll get a summary table providing a top-down view of your data:
In some scenarios, it might be more informative to sort the results by aggregated values. For this, you can adjust the row_sort_order or col_sort_order argument accordingly.
In the example below, where both rows and columns are grouped based on one column (by products and by seller, respectively), you can sort by the SUM of Amount using 2 for ascending order or -2 for descending order:
=PIVOTBY(B2:B32, D2:D32, E2:E32, SUM, ,0, -2)
As a result, the values in the Total column will be sorted from largest to smallest, providing a clear view of which products are generating the most revenue.
Note. If a formula results in a #VALUE! error, make sure the numbers in the row_sort_order and col_sort_order arguments correspond to the number of columns in row_fields and col_fields, respectively.
Filter out specific rows
To filter out specific rows in your PIVOTBY results, you can use the filter_array argument. This argument accepts a one-dimensional array of Boolean values that correspond to the rows in your data set (TRUE indicating the rows to include and FALSE indicating the rows to exclude). To generate such an array, you can construct a logical expression with logical operators.
For example, to exclude sales in Canada, the formula is:
=PIVOTBY(B2:C32, D2:D32, E2:E32, COUNT, , , , , , C2:C32<>"Canada")
And if you wish to count only amounts greater than $200, you can do so with this formula:
=PIVOTBY(B2:C32, D2:D32, E2:E32, COUNT, , , , , , E2:E32>200)
Summarize by percentages
To specify how the PERCENTOF calculation is performed relative to different totals, use the relative_to argument of the PIVOTBY function:
- Column totals (0 or omitted) - calculation is performed relative to all values in a column, with each column's total equaling 100%.
- Row totals (1) - calculation is performed relative to all values in a row, with each row's total equaling 100%.
- Grand total (2) – calculation is performed relative to all values. The grand total equals 100%.
- Parent column total (3) - calculation is performed relative to all values in column parent.
- Parent row total (4) - calculation is performed relative to all values in row parent.
Consider the following example with 2-level row grouping (by product and by country).
To calculate the percentage of each value relative to its column's total, simply use the PERCENTOF function. Since the default for the relative_to argument is 0, which corresponds to column totals, you can choose to define it explicitly or rely on the default behavior.
=PIVOTBY(B2:C32, D2:D32, E2:E32, PERCENTOF)
To calculate the percentage of each value relative to its' row total, set the relative_to argument to 1:
=PIVOTBY(B2:C32, D2:D32, E2:E32, PERCENTOF, , , , , , ,1)
The image below illustrates the outcomes of both formulas.
As our formula groups rows into 2 levels (by product and by country), you may also want to calculate the % of parent row total, i.e. find each country's percentage of a product total. Tor this, set the relative_to argument to 4:
=PIVOTBY(B2:C32, D2:D32, E2:E32, PERCENTOF, , 0, , 0, , ,4)
In the resulting table, you can see how each merchant's sales of a particular product are distributed across different countries. For instance, Anna made around 47% of her apple sales in Mexico and around 53% in the USA. Carlos sold all his apples in Mexico, therefore he has 100% in the Apples-Mexico cell. And Emily's cells are blank as she made no apple sales.
By using these variations of the PIVOTBY function with the PERCENTOF aggregation, you can create detailed and insightful percentage-based summaries of your data.
Group dates by year
When dealing with dates, Excel's pivot tables offer a number of convenient options to group data by year, quarter, and month. To achieve similar functionality with the PIVOTBY function, you can utilize Excel's date functions like MONTH and YEAR.
For example, to group dates in A2:A32 by year, you could use this formula:
=PIVOTBY(B2:B32, YEAR(A2:A32), E2:E32, AVERAGE)
This formula will group your data by the year extracted from column A and calculate the average of the corresponding values in column E, providing a yearly summary:
Show headers
To display field headers when using the PIVOTBY function, you need to set the fifth argument named field_headers to 3, like so:
=PIVOTBY(B2:B32, C2:C32, E2:E32, AVERAGE, 3)
When aggregating values in just one column, field headers may not be particularly useful. For instance, in the first table below, the results are self-explanatory without field headers - you can see product names on the left and country names at the top. Placing the "Country" label at the top cell and duplicating the field header "Amount" in each aggregated column does not add much clarity to the results.
However, when aggregating several columns, as in the second example, field headers become more useful:
=PIVOTBY(B2:B32, C2:C32, D2:E32, AVERAGE, 3, , , 0)
Here, the field headers "Qty." and "Amount" help distinguish between the different types of aggregated data, providing a clearer understanding of the summarized information.
Pivot data from multiple tables
When you receive data from multiple sources, such as reports from different regional offices or various departments, you can use the PIVOTBY function in combination with VSTACK to consolidate the data.
For example, to group and summarize two source tables like shown in the image below, you can use this formula:
=PIVOTBY(VSTACK(D3:D17, D22:D37), VSTACK(C3:C17, C22:C37), VSTACK(E3:E17, E22:E37), SUM)
Here, we use three separate VSTACK functions to combine sellers, countries, and amounts across two different tables, which are then grouped and summarized by the PIVOTBY function.
Format PIVOTBY results with conditional formatting
A really nice feature of Excel PivotTables is their built-in formatting that automatically highlights headers, totals, and subtotals. While the results of the PIVOTBY function initially appear plain, you can use Conditional Formatting to enhance their presentation. This feature allows you to apply your preferred formatting styles, which will automatically update as the PIVOTBY results change.
The screenshot below provides an example of how the PIVOTBY results can be formatted in your worksheets. As you see, conditional formatting makes the grouped data far more readable by highlighting key figures.
Stay tuned for the forthcoming article that will delve into this method in detail.
Excel PIVOTBY function vs. PivotTable
Both the PIVOTBY function and traditional PivotTables serve the purpose of summarizing large datasets. However, they offer different approaches and functionalities and have distinct advantages and drawbacks. Here's a quick comparison to help you understand when to use each one.
The PIVOTBY function is the newest addition to Excel's dynamic array functions, offering a formula-based approach to data analysis. Pros: Cons: A traditional feature in Excel for data summarization, PivotTable offers a wide range of options for quickly creating and modifying data summaries. Pros: Cons: In summary, the PIVOTBY function is more efficient for smaller datasets and when real-time dynamic updates are crucial. It's perfect for quick analysis within formulas and integrating pivot results into other calculations. PivotTables, on the other hand, are better suited for handling large datasets with big amounts of data. They offer a more user-friendly experience with a focus on interactivity and visual presentation, rather than complex formula operations.
PIVOTBY function
Excel PivotTable
Excel PIVOTBY function not working
If you're encountering issues with the PIVOTBY function in Excel, it might be due to several reasons, especially since the function isn't universally available yet.
There is no PIVOTBY function in my Excel
Currently, the PIVOTBY function is only available to Office Insiders in the Beta channel. Once officially released, it will become available to all Excel 365 users. However, PIVOTBY is not backward compatible and won't be supported in earlier versions of Excel.
#VALUE! error
If you're seeing a #VALUE! error, it's usually because of:
- Mismatched range lengths: the row_fields, column_fields, and values arguments refer to ranges of different lengths.
- Filter array length: the filter_array doesn't match the length of the fields arrays.
- Subtotal depth mismatch: the row_total_depth or/and column_total_depth argument is set to display subtotals while the corresponding fields argument contains just one column.
- Invalid sort order value: a sort_order argument contains an invalid value, e.g. exceeding the number of columns in fields + values arrays combined.
#SPILL! error
Since PIVOTBY uses dynamic arrays, it requires sufficient space to display its results. If there is any obstruction in the neighboring cells, like a space or non-printing character, a #SPILL! error occurs. To fix it, ensure that adjacent cells around your formula are empty, allowing the dynamic array from the PIVOTBY function to spill over.
Hopefully, this tutorial has provided a basic understanding of the PIVOTBY function and given you some insights into how to harness its capabilities.
Practice workbook for download
PIVOTBY formula examples (.xlsx file)
