The PIVOTBY Function vs PivotTables: Which Should You Use in Excel?

Excel's PIVOTBY relation and PivotTables some summarize and reorganize data, allowing for elaborate investigation by grouping and aggregating your figures. However, contempt their akin names, the methods for utilizing them disagree hugely.

Using Excel's PIVOTBY Function

The PIVOTBY relation is simply a dynamic array relation successful Excel that uses a look to radical rows and columns of information to make tabular summaries of your data. Let's look astatine immoderate of its main features and characteristics.

Limited Compatibility

Microsoft introduced the PIVOTBY relation for wide usage successful Excel successful September 2024, astir a twelvemonth aft introducing it to Microsoft Insiders. As a result, astatine the clip of penning (April 2025), it's lone disposable to radical utilizing Excel for Microsoft 365 connected a Windows PC oregon Mac.

This means that if you program to stock an Excel record that uses the PIVOTBY relation with idiosyncratic who uses an older mentation of the program, they're apt to spot the #NAME? mistake alternatively of the beautifully grouped information array you spot connected your screen.

Automatic Refresh

Probably 1 of the top benefits of the PIVOTBY relation is that it recalculates and changes signifier automatically whenever the information root changes.

On the different hand, if you usage a PivotTable, you request to either execute a manual refresh to show the latest mentation of your figures oregon make immoderate VBA codification to automate this process, though, successful September 2024, Microsoft changed the default behaviour of Office applications to block files containing VBA macros to amended security.

While refreshing PivotTables manually successful Excel is simply a straightforward process, it's casual to hide to bash it, meaning you mightiness deliberation you're looking astatine the astir up-to-date mentation of your information when, successful fact, you're not. With the PIVOTBY function, you don't person to interest astir forgetting to execute this important step.

Extensive Customization

One facet of PIVOTBY I've personally recovered endlessly utile truthful acold is its flexibility, allowing for finely customized outputs. The relation allows you to:

• Define the variables that signifier the rows and headers of the output,
• Choose whether you privation to see headers,
• Aggregate your information utilizing galore antithetic Excel functions oregon a function you make yourself via LAMBDA,
• Create hierarchical groupings wrong the output,
• Sort the information according to specified rows and columns,
• Filter retired unwanted data, and
• Turn the totals successful the effect into percentages.

While PivotTables besides fto you bash galore of the above, the information that these settings are each incorporated wrong 1 relation means PIVOTBY is simply a large mode to customize your information successful 1 place—you don't request to familiarize yourself with the PivotTable Fields pane oregon hunt for antithetic options successful the ribbon.

What's more, the PIVOTBY relation works whether your root information is organized successful columns oregon successful rows, whereas PivotTables necessitate the root information to beryllium organized successful columns only.

Formula Dependency

Personally, I sometimes similar to usage PIVOTBY alternatively of PivotTables due to the fact that I bask creating and manipulating Excel formulas. Maybe it's conscionable me, but I emotion the pleasance of adding an statement to a formula, pressing Enter, and seeing however that affects the output. For the aforesaid reason, I'm reluctant to usage Copilot to execute analyzable tasks successful my spreadsheet.

If you're of the aforesaid mindset, aft utilizing the PIVOTBY relation for the archetypal time, it becomes satisfyingly intuitive and volition apt signifier the way you instrumentality to analyse galore information sources successful the future.

That said, sending an Excel spreadsheet containing PIVOTBY to idiosyncratic unfamiliar with however the relation works could origin issues if they request to set the information oregon tweak its visualization. In different words, PIVOTBY is simply a large relation to usage if the spreadsheet is for your eyes only, oregon if you're sharing it with others who recognize however it works.

Using PivotTables successful Excel

Excel's PivotTable instrumentality is well-known arsenic 1 of the program's astir almighty features for extracting figures from ample datasets and presenting them successful a manageable and readable form. Here are immoderate things you should cognize astir what they offer.

Wide Compatibility

Initially intended for concern use, PivotTables were introduced to Excel successful the mid-1990s, and Microsoft has continued to amended their functionality ever since. As a result, the instrumentality is disposable successful astir versions of the program, with PivotTables arsenic we cognize them contiguous accessible successful Excel 2016 onward, Microsoft Office, Excel for the web, and Excel for Microsoft 365 connected a PC oregon Mac.

As a result, you don't person to interest astir compatibility if you've decided not to upgrade to Office 2024 oregon if you're sending your workbook to others.

Designated User Interface

As soon arsenic you prime your data, click "PivotTable" successful the Insert tab connected the ribbon, and prime whether you privation the array to look successful the existent worksheet oregon a caller tab, Excel launches the PivotTable Fields pane.

Here, you tin prime the tract sanction checkboxes to determine which variables you privation to see successful your data, and the tract boxes astatine the bottommost of the pane fto you signifier them into rows and columns.

What's more, with your PivotTable selected, you tin further customize the PivotTable successful the Design tab, oregon summation insights from the information done the PivotTable Analyze tab.

Since PivotTables are a designated instrumentality successful Excel, they person galore easy-to-access options that fto you manipulate your information and contiguous it successful ways that enactment for you. What's more, if you program to stock the workbook with others, the straightforward idiosyncratic interface means that they're much apt to beryllium capable to recognize and usage the data, adjacent if they're not Excel powerfulness users.

Manual Refresh

Earlier, I highlighted that utilizing PIVOTBY alternatively of PivotTables mightiness beryllium preferable due to the fact that the relation responds instantly to changes successful the root data, whereas PivotTables don't.

However, if your spreadsheet contains tons of PIVOTBY formulas, since they're perpetually looking to recalculate, they tin devour up tons of your computer's memory, starring to a painfully dilatory workbook.

On the different hand, due to the fact that PivotTables lone update erstwhile you punctual them to by clicking "Refresh" successful the PivotTable Analyze tab, they're not a changeless drain connected your device's resources.

Efficient Formatting

Since PivotTables are a dedicated instrumentality successful Excel, they travel with easy-to-use formatting options that let you to easy separate betwixt the main header row, information row, subtotal rows, and the expansive full row.

What's more, done the Design tab connected the ribbon, you tin rapidly modify the PivotTable style, determine whether to see bands and headers, and tweak different presentational aspects.

On the different hand, to execute the aforesaid effect with the output of a PIVOTBY formula, you request to use formatting manually. To marque matters adjacent much complicated, if you privation to marque definite rows basal retired successful a PIVOTBY result, you request to usage conditional formatting to guarantee the formatting adjusts to changes successful the information source.

Added Visualization

Once you've created a PivotTable successful Excel, the amusive doesn't halt there. Indeed, astatine the click of a button, you tin adhd further awesome graphics to visualize your figures successful ways that marque them easier to work and organize.

There are 2 tools I peculiarly bask utilizing arsenic soon arsenic I've created a PivotTable:

• Timeline: If the figures successful the PivotTable are sorted according to antithetic clip periods, you tin add a dynamic timeline filter that allows you to rapidly show information from idiosyncratic days, months, quarters, oregon years.
• PivotChart: Even though PivotTables let you to easy radical and analyse your figures, you tin spell 1 measurement further and show your information successful a PivotChart, which adapts automatically to changes successful the PivotTable. As a result, PivotCharts are much flexible than regular charts for information summarization.

Both these tools are disposable via the PivotTable Analyze tab connected the ribbon.

Diverse Data Sources

While the PIVOTBY relation tin lone notation root information successful Excel, if you make a PivotTable successful Excel 2016 oregon aboriginal oregon Excel for Microsoft 365, you tin take to propulsion successful information from an outer source, including relational databases similar SQL Server oregon Oracle, different types of files similar CSVs, XLMs, oregon ODCs, PowerBI databases, oregon web pages.

This capableness allows for much cross-network flexibility, which is particularly utile if you're generating and analyzing information successful the workplace.

Should You Use the PIVOTBY Function oregon PivotTables successful Excel?

Let maine stress astatine this constituent that the PIVOTBY relation and PivotTables person unsocial uses and are not needfully replacements for each other. However, if you're considering the champion mode to radical and aggregate a acceptable of information successful an Excel spreadsheet, present are the main things to carnivore successful mind:

Use the PIVOTBY relation if:

• You privation the effect to refresh automatically and, thus, show the astir up-to-date information,
• You similar moving with formulas and similar to usage their arguments—rather than tabs and menus connected the ribbon—to power information output,
• You privation to flexibly customize however your information is presented, and
• You privation to incorporated antithetic functions, including customized LAMBDA formulas, to specify however your information is aggregated.

On the different hand, usage PivotTables if:

• You're looking to reorganize your information into formatted tables successful conscionable a fewer clicks,
• You similar moving with tabs and panes implicit elaborate formulas to customize your data,
• You privation to adhd further graphics to further visualize your figures,
• You're apt to stock the workbook with others, and
• The root information is successful assorted formats.

---

Another mode to signifier your information into groups successful Excel is by utilizing the GROUPBY function. However, wherever the PIVOTBY relation lets you summarize your information successful rows and columns, GROUPBY works with rows only, meaning it requires less arguments erstwhile you make the formula.