How to Use the PIVOTBY Function in Excel
Related
How to Name a Table in Microsoft Excel
The Beginner’s Guide to Excel’s Formulas and Functions
Use the PERCENTOF Function to Simplify Percentage Calculations in Excel
Sign in to your How-To Geek account
Quick Links
-
The PIVOTBY Syntax
-
PIVOTBY: Using the Required Arguments Only
-
PIVOTBY: Using the Optional Arguments
Excel’s PIVOTBY function allows you to group your figures without needing to recreate your data in a PivotTable. What’s more, data summaries created via PIVOTBY automatically update to reflect changes in your original data, and you can customize what they show by tweaking the formula.
If you already know how to use the GROUPBY function, learning to use the PIVOTBY function will be straightforward. The difference is that where GROUPBY only groups variables into rows, PIVOTBY has an additional argument that lets you also group certain variables into columns.
The PIVOTBY Syntax
The PIVOTBY function lets you input a total of 11 arguments. Although this sounds daunting, the benefit of having so many arguments is that you can tailor the result to align with what you want the data to show.
However, only the first four fields are required.
Required Fields
The required PIVOTBY fields are where you tell Excel which variables you want to be displayed in rows, which variables you want to be displayed in columns, where to find the values, and how you want to aggregate your data:
=PIVOTBY(a,b,c,d)
where
- a are the cells containing the category or categories you want to appear as row headers down the left-hand side of your result,
- b are the cells containing the category or categories you want to appear as column headers across the top of your result,
- c are the values that will appear in the center of your result, according to the rows and columns you selected for arguments a and b, and
- d is the Excel function (or function you’ve created yourself through LAMBDA) that defines how the data will be aggregated.
Although arguments a and b are required for PIVOTBY to produce the type of result it was created to produce, you can omit them to group data in rows or columns only.
Optional Fields
The optional PIVOTBY fields allow you to include headers and totals, sort your data, and—in very specific circumstances—specify some additional parameters:
=PIVOTBY(a,b,c,d,[e,f,g,h,i,j,k])
|
Argument |
What It Is |
Notes |
|---|---|---|
|
e |
A number that specifies whether arguments a, b, and c include headers and whether you want headers to be displayed in the result |
0 = No headers (default) 1 = Headers, but don’t show 2 = No headers, but generate them 3 = Headers, and show |
|
f |
A number that determines whether argument a should contain grand totals and subtotals |
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 |
|
g |
A number indicating how the columns should be sorted |
The number refers to the columns in argument a, followed by the column in argument c. For example, if you had two columns in argument a, “2” would sort the result by the second column of this argument, and “3” would sort the result by argument c. A positive number represents alphabetical or ascending order, and a negative number represents the reverse. |
|
h |
A number that determines whether argument b should contain grand totals and subtotals |
0 = No totals 1 = Grand totals on the right (default) 2 = Grand totals and subtotals on the right -1 = Grand totals on the left -2 = Grand totals and subtotals on the left |
|
i |
A number that indicates how the rows should be sorted |
The number refers to the columns in argument b, followed by the column in argument c, and works in the same way as argument g. |
|
j |
A logical expression or formula that lets you filter out rows containing certain values or text |
N/A |
|
k |
A number that gives you more control over how the result’s calculations are performed when you use the PERCENTOF function in argument d |
0 = Column totals (default) 1 = Row totals 2 = Grand total 3 = Parent column total 4 = Parent row total |
PIVOTBY: Using the Required Arguments Only
First, let me show you how to use the PIVOTBY function in its simplest form with just the four required fields.
Let’s say you’ve been handed this table of data named Sports_Viewers. It shows the live viewing figures (column D) for six sports (column B) across four regions (column C) over four years (column A), and you’ve been asked to generate a summary of total viewers by sport and year.
Related
How to Name a Table in Microsoft Excel
“Table1” doesn’t explain much…
You could use the GROUPBY function to do this, though you would need to use optional arguments to produce a result that makes analysis easier. What’s more, since GROUPBY only sorts data into rows, you could end up with a long list of information that is difficult to interpret.
Instead, using the PIVOTBY function lets you take one or more of the variables and place them into columns, giving you a clearer picture of how your numbers stack up.
Since the PIVOTBY formula produces a dynamic spilled array, you must type its formula in an area of your spreadsheet that is not formatted as an Excel table.
To do this, in cell F1, type
=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM)
where
- Sports_Viewers[Sport] is the named column in the named table that represents the six different sports. These will be the row headers down the left-hand side of your result.
- Sports_Viewers[Year] represents the table’s Year column, and these will be the column headers along the top of the result.
- Sports_Viewers[Viewers] is the column showing the number of people who have watched those sports in those years, so this data will be the focal part of the result.
- SUM tells Excel you want the total columns to add the figures together. On the other hand, if, for example, you use AVERAGE, the result will show you the mean of those figures instead.
Instead of typing the table and column names in your formula (also known as structured references) manually, if you select the cells in the table using your mouse, Excel will input these for you.
Because of the way PIVOTBY displays and organizes the data, you can draw insights from the result straightaway. For example, you can see that data is absent for certain sports in certain years. Also, in the absence of the optional fields, Excel automatically adds totals to both the rows and columns of the PIVOTBY result, meaning you can instantly see the total viewing figures per year and per sport.
Without the optional arguments, Excel also sorts the result into alphabetical or ascending order by argument a, and doesn’t include column headers for argument a.
Now, let’s say you want to include a column that divides each sport into the four different regions. To do this, you’ll need to include both the sport and the region in argument a:
=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM)
To group non-adjacent columns into an argument, use the CHOOSECOLS function.
This result provides more detailed insight into your data by showing each sport’s total viewing figures by region. However, because you’ve added an extra variable to argument a, to make the result clearer, you could enter some optional arguments.
PIVOTBY: Using the Optional Arguments
Whenever you create an Excel formula, each argument is separated by a comma. This means that if you want to skip over any of the optional arguments, you simply need to type a first comma to open the argument, followed by a second comma to close it. The fact that there’s nothing between these two commas tells Excel that you’ve deliberately omitted this argument, so you want the program to adopt the default setting.
Related
The Beginner’s Guide to Excel’s Formulas and Functions
Everything you need to know about Excel’s engine room.
1
Example 1: Grand Totals and Subtotals
One way to make the PIVOTBY result in the previous example clearer would be to add subtotal rows for each sport.
To do this, you need to type:
=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM,,2)
Notice how, after argument d (SUM), two commas indicate that you want to jump over argument e (headers), but include argument f (in this case, 2 means that you want to include a subtotal at the bottom of each sport, as well as grand totals at the bottom of the overall result).
To make the data even clearer, I have used conditional formatting to color any rows in the result where column F is not blank, column G is blank, and column F doesn’t contain the words “Grand Total.” Applying direct formatting to spilled arrays is not advised, as the formatting is attached to the cells, rather than the data. This means that if the data changes, the formatting won’t change with it.
Example 2: Sort Order
Now, let’s look at another way to manipulate your result: sorting by one of the output columns.
Typing:
=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM,,2,3)
means that, after skipping over argument e, as well as including grand totals and subtotals (number 2 in argument f), you also want to order your data by overall viewing figures in ascending order (number 3 in argument g).
Notice how sorting by total viewing figures places not only the sports in order according to their overall totals, but also the regional subcategories according to their subtotals.
Example 3: Percentages (PERCENTOF)
Here’s how to view your result using percentages.
Typing:
=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Year],Sports_Viewers[Viewers],PERCENTOF,,,2,,,,2)
tells Excel that you want the sports as the row headers (argument a), the years as the column headers (argument b), and you want to aggregate your data (argument c) in the form of percentages (argument d). The first 2 (argument g) represents the order, which, in this case, is by the values. Finally, the second 2 (argument k) tells Excel that the percentages should be relative to the overall data. The unpopulated commas tell Excel you want it to adopt the default for arguments e, f, h, i, and j.
Related
Use the PERCENTOF Function to Simplify Percentage Calculations in Excel
Handle percentages like a pro!
Now, select the cells containing the decimalized results, and click the “Percent Style” icon in the Number group of the Home tab on the ribbon. While you’re there, click the “Increase Decimal” and “Decrease Decimal” buttons to define the number of decimal places.
Select extra rows beneath the result in case more data is added to the original table in the future.
Finally, take a moment to digest what this result tells you.
For example, the number of people watching softball in 2022 constitutes 11.5% of the overall viewing figures for all sports across all years. Also, because the data is organized by the total column in ascending order, you can see that nearly a quarter (23.7%) of the overall viewing figures came from people watching basketball.
As well as using SUM, AVERAGE, and PERCENTOF for the function argument in PIVOTBY, you can also use other aggregation functions, like COUNT, MIN, or MAX.
Follow
Followed
Share
Readers like you help support How-To Geek. When you make a purchase using links on our site, we may earn an affiliate commission. Read More.
“Hey there, just a heads-up: We’re part of the Amazon affiliate program, so when you buy through links on our site, we may earn a small commission. But don’t worry, it doesn’t cost you anything extra and helps us keep the lights on. Thanks for your support!”
