Excel’s PIVOTBY function and PivotTables both summarize and reorganize data, allowing for detailed analysis by grouping and aggregating your figures. However, despite their similar names, the methods for using them differ hugely.
Using Excel’s PIVOTBY Function
The PIVOTBY function is a dynamic array function in Excel that uses a formula to group rows and columns of data to generate tabular summaries of your data. Let’s look at some of its main features and characteristics.
Related
How to Use the PIVOTBY Function in Excel
Reorganize your data into specified rows and columns.
Limited Compatibility
Microsoft introduced the PIVOTBY function for general use in Excel in September 2024, around a year after introducing it to Microsoft Insiders. As a result, at the time of writing (April 2025), it’s only available to people using Excel for Microsoft 365 on a Windows PC or Mac.
This means that if you plan to share an Excel file that uses the PIVOTBY function with someone who uses an older version of the program, they’re likely to see the #NAME? error instead of the beautifully grouped data array you see on your screen.
Related
How to Fix Common Formula Errors in Microsoft Excel
Find out what that error means and how to fix it.
Automatic Refresh
Probably one of the greatest benefits of the PIVOTBY function is that it recalculates and changes shape automatically whenever the data source changes.
On the other hand, if you use a PivotTable, you need to either perform a manual refresh to display the latest version of your figures or create some VBA code to automate this process, though, in September 2024, Microsoft changed the default behavior of Office applications to block files containing VBA macros to improve security.
While refreshing PivotTables manually in Excel is a straightforward process, it’s easy to forget to do it, meaning you might think you’re looking at the most up-to-date version of your data when, in fact, you’re not. With the PIVOTBY function, you don’t have to worry about forgetting to execute this crucial step.
Extensive Customization
One aspect of PIVOTBY I’ve personally found endlessly useful so far is its flexibility, allowing for finely customized outputs. The function allows you to:
- Define the variables that form the rows and headers of the output,
- Choose whether you want to include headers,
- Aggregate your data using many different Excel functions or a function you create yourself via LAMBDA,
- Create hierarchical groupings within the output,
- Sort the data according to specified rows and columns,
- Filter out unwanted data, and
- Turn the totals in the result into percentages.
While PivotTables also let you do many of the above, the fact that these settings are all incorporated within one function means PIVOTBY is a great way to customize your data in one place—you don’t need to familiarize yourself with the PivotTable Fields pane or search for different options in the ribbon.
What’s more, the PIVOTBY function works whether your source data is organized in columns or in rows, whereas PivotTables require the source data to be organized in columns only.
Formula Dependency
Personally, I sometimes prefer to use PIVOTBY instead of PivotTables because I enjoy creating and manipulating Excel formulas. Maybe it’s just me, but I love the pleasure of adding an argument to a formula, pressing Enter, and seeing how that affects the output. For the same reason, I’m reluctant to use Copilot to execute complex tasks in my spreadsheet.
Related
Why You Still Need Excel Skills in Today’s AI World
Don’t let Copilot drain away your expertise.
If you’re of the same mindset, after using the PIVOTBY function for the first time, it becomes satisfyingly intuitive and will likely form the route you take to analyze many data sources in the future.
That said, sending an Excel spreadsheet containing PIVOTBY to someone unfamiliar with how the function works could cause issues if they need to adjust the data or tweak its visualization. In other words, PIVOTBY is a great function to use if the spreadsheet is for your eyes only, or if you’re sharing it with others who understand how it works.
Using PivotTables in Excel
Excel’s PivotTable tool is well-known as one of the program’s most powerful features for extracting figures from large datasets and presenting them in a manageable and readable form. Here are some things you should know about what they offer.
Related
How to Use PivotTables to Analyze Excel Data
PivotTables are a powerful way to analyze data in Excel. Here’s how to start using them.
Wide Compatibility
Initially intended for business use, PivotTables were introduced to Excel in the mid-1990s, and Microsoft has continued to improve their functionality ever since. As a result, the tool is available in most versions of the program, with PivotTables as we know them today accessible in Excel 2016 onward, Microsoft Office, Excel for the web, and Excel for Microsoft 365 on a PC or Mac.
As a result, you don’t have to worry about compatibility if you’ve decided not to upgrade to Office 2024 or if you’re sending your workbook to others.
Designated User Interface
As soon as you select your data, click “PivotTable” in the Insert tab on the ribbon, and select whether you want the table to appear in the current worksheet or a new tab, Excel launches the PivotTable Fields pane.
Here, you can select the field name checkboxes to decide which variables you want to include in your data, and the field boxes at the bottom of the pane let you organize them into rows and columns.
What’s more, with your PivotTable selected, you can further customize the PivotTable in the Design tab, or gain insights from the data through the PivotTable Analyze tab.
Since PivotTables are a designated tool in Excel, they have many easy-to-access options that let you manipulate your data and present it in ways that work for you. What’s more, if you plan to share the workbook with others, the straightforward user interface means that they’re more likely to be able to understand and use the data, even if they’re not Excel power users.
Manual Refresh
Earlier, I highlighted that using PIVOTBY instead of PivotTables might be preferable because the function responds instantly to changes in the source data, whereas PivotTables don’t.
However, if your spreadsheet contains lots of PIVOTBY formulas, since they’re constantly looking to recalculate, they can eat up lots of your computer’s memory, leading to a painfully slow workbook.
Related
Everything You Need to Know About Volatile Functions in Excel
They’ve got nothing to do with chemistry or hostility!
On the other hand, because PivotTables only update when you prompt them to by clicking “Refresh” in the PivotTable Analyze tab, they’re not a constant drain on your device’s resources.
Efficient Formatting
Since PivotTables are a dedicated tool in Excel, they come with easy-to-use formatting options that allow you to easily distinguish between the main header row, data row, subtotal rows, and the grand total row.
What’s more, through the Design tab on the ribbon, you can quickly modify the PivotTable style, decide whether to include bands and headers, and tweak other presentational aspects.
On the other hand, to achieve the same effect with the output of a PIVOTBY formula, you need to apply formatting manually. To make matters even more complicated, if you want to make certain rows stand out in a PIVOTBY result, you need to use conditional formatting to ensure the formatting adjusts to changes in the data source.
Related
How to Format a Spilled Array in Excel
Don’t apply direct formatting.
Added Visualization
Once you’ve created a PivotTable in Excel, the fun doesn’t stop there. Indeed, at the click of a button, you can add further impressive graphics to visualize your figures in ways that make them easier to read and organize.
There are two tools I particularly enjoy using as soon as I’ve created a PivotTable:
- Timeline: If the figures in the PivotTable are sorted according to different time periods, you can add a dynamic timeline filter that allows you to quickly display data from individual days, months, quarters, or years.
- PivotChart: Even though PivotTables allow you to easily group and analyze your figures, you can go one step further and display your data in a PivotChart, which adapts automatically to changes in the PivotTable. As a result, PivotCharts are more flexible than regular charts for data summarization.
Both these tools are available via the PivotTable Analyze tab on the ribbon.
Diverse Data Sources
While the PIVOTBY function can only reference source data in Excel, if you create a PivotTable in Excel 2016 or later or Excel for Microsoft 365, you can choose to pull in data from an external source, including relational databases like SQL Server or Oracle, other types of files like CSVs, XLMs, or ODCs, PowerBI databases, or web pages.
This capability allows for more cross-network flexibility, which is especially useful if you’re generating and analyzing data in the workplace.
Should You Use the PIVOTBY Function or PivotTables in Excel?
Let me emphasize at this point that the PIVOTBY function and PivotTables have unique uses and are not necessarily replacements for each other. However, if you’re considering the best way to group and aggregate a set of data in an Excel spreadsheet, here are the main things to bear in mind:
Use the PIVOTBY function if:
- You want the result to refresh automatically and, thus, display the most up-to-date information,
- You like working with formulas and prefer to use their arguments—rather than tabs and menus on the ribbon—to control data output,
- You want to flexibly customize how your data is presented, and
- You want to incorporate different functions, including customized LAMBDA formulas, to define how your data is aggregated.
On the other hand, use PivotTables if:
- You’re looking to reorganize your data into formatted tables in just a few clicks,
- You prefer working with tabs and panes over detailed formulas to customize your data,
- You want to add additional graphics to further visualize your figures,
- You’re likely to share the workbook with others, and
- The source data is in various formats.
Another way to organize your data into groups in Excel is by using the GROUPBY function. However, where the PIVOTBY function lets you summarize your data in rows and columns, GROUPBY works with rows only, meaning it requires fewer arguments when you generate the formula.