Excel’s PIVOTBY perform and PivotTables each summarize and reorganize knowledge, permitting for detailed evaluation by grouping and aggregating your figures. Nonetheless, regardless of their related names, the strategies for utilizing them differ massively.

Utilizing Excel’s PIVOTBY Perform

The PIVOTBY perform is a dynamic array perform in Excel that makes use of a system to group rows and columns of information to generate tabular summaries of your knowledge. Let’s take a look at a few of its foremost options and traits.

Restricted Compatibility

Microsoft launched the PIVOTBY perform for normal use in Excel in September 2024, round a yr after introducing it to Microsoft Insiders. Consequently, on the time of writing (April 2025), it is solely out there to individuals utilizing Excel for Microsoft 365 on a Home windows PC or Mac.

Which means for those who plan to share an Excel file that makes use of the PIVOTBY perform with somebody who makes use of an older model of this system, they’re prone to see the #NAME? error as an alternative of the superbly grouped knowledge array you see in your display screen.

Automated Refresh

In all probability one of many best advantages of the PIVOTBY perform is that it recalculates and adjustments form routinely every time the information supply adjustments.

Then again, for those who use a PivotTable, it is advisable to both carry out a guide refresh to show the most recent model of your figures or create some VBA code to automate this course of, although, in September 2024, Microsoft modified the default habits of Workplace functions to block files containing VBA macros to enhance safety.

Whereas refreshing PivotTables manually in Excel is a simple course of, it is simple to overlook to do it, that means you would possibly assume you are wanting on the latest model of your knowledge when, the truth is, you are not. With the PIVOTBY perform, you do not have to fret about forgetting to execute this important step.

Intensive Customization

One facet of PIVOTBY I’ve personally discovered endlessly helpful thus far is its flexibility, permitting for finely personalized outputs. The perform lets you:

• Outline the variables that kind the rows and headers of the output,
• Select whether or not you need to embody headers,
• Mixture your knowledge utilizing many alternative Excel features or a function you create yourself via LAMBDA,
• Create hierarchical groupings inside the output,
• Type the information based on specified rows and columns,
• Filter out undesirable knowledge, and
• Flip the totals within the outcome into percentages.

Whereas PivotTables additionally allow you to do lots of the above, the truth that these settings are all integrated inside one perform means PIVOTBY is a good way to customise your knowledge in a single place—you need not familiarize your self with the PivotTable Fields pane or seek for totally different choices within the ribbon.

What’s extra, the PIVOTBY perform works whether or not your supply knowledge is organized in columns or in rows, whereas PivotTables require the supply knowledge to be organized in columns solely.

Formulation Dependency

Personally, I generally favor to make use of PIVOTBY as an alternative of PivotTables as a result of I take pleasure in creating and manipulating Excel formulation. Perhaps it is simply me, however I like the pleasure of including an argument to a system, urgent Enter, and seeing how that impacts the output. For a similar cause, I am reluctant to make use of Copilot to execute complicated duties in my spreadsheet.

If you happen to’re of the identical mindset, after utilizing the PIVOTBY perform for the primary time, it turns into satisfyingly intuitive and can doubtless kind the route you are taking to research many knowledge sources sooner or later.

That mentioned, sending an Excel spreadsheet containing PIVOTBY to somebody unfamiliar with how the perform works might trigger points if they should alter the information or tweak its visualization. In different phrases, PIVOTBY is a superb perform to make use of if the spreadsheet is to your eyes solely, or for those who’re sharing it with others who perceive the way it works.

Utilizing PivotTables in Excel

Excel’s PivotTable device is well-known as one of many program’s strongest options for extracting figures from giant datasets and presenting them in a manageable and readable kind. Listed below are some issues it is best to learn about what they provide.

Extensive Compatibility

Initially supposed for enterprise use, PivotTables had been launched to Excel within the mid-Nineteen Nineties, and Microsoft has continued to enhance their performance ever since. Consequently, the device is obtainable in most variations of this system, with PivotTables as we all know them right this moment accessible in Excel 2016 onward, Microsoft Workplace, Excel for the net, and Excel for Microsoft 365 on a PC or Mac.

Consequently, you do not have to fret about compatibility for those who’ve decided not to upgrade to Office 2024 or for those who’re sending your workbook to others.

Designated Consumer Interface

As quickly as you choose your knowledge, click on “PivotTable” within the Insert tab on the ribbon, and choose whether or not you need the desk to look within the present worksheet or a brand new tab, Excel launches the PivotTable Fields pane.

Right here, you may choose the sector title checkboxes to resolve which variables you need to embody in your knowledge, and the sector packing containers on the backside of the pane allow you to manage them into rows and columns.

What’s extra, along with your PivotTable chosen, you may additional customise the PivotTable within the Design tab, or acquire insights from the information by means of the PivotTable Analyze tab.

Since PivotTables are a chosen device in Excel, they’ve many easy-to-access choices that allow you to manipulate your knowledge and current it in ways in which give you the results you want. What’s extra, for those who plan to share the workbook with others, the simple person interface implies that they’re extra doubtless to have the ability to perceive and use the information, even when they are not Excel energy customers.

Guide Refresh

Earlier, I highlighted that utilizing PIVOTBY as an alternative of PivotTables may be preferable as a result of the perform responds immediately to adjustments within the supply knowledge, whereas PivotTables do not.

Nonetheless, in case your spreadsheet incorporates a number of PIVOTBY formulation, since they’re consistently seeking to recalculate, they’ll eat up a number of your pc’s reminiscence, resulting in a painfully sluggish workbook.

Then again, as a result of PivotTables solely replace once you immediate them to by clicking “Refresh” within the PivotTable Analyze tab, they are not a continuing drain in your machine’s sources.

Environment friendly Formatting

Since PivotTables are a devoted device in Excel, they arrive with easy-to-use formatting choices that help you simply distinguish between the primary header row, knowledge row, subtotal rows, and the grand whole row.

What’s extra, by means of the Design tab on the ribbon, you may shortly modify the PivotTable model, resolve whether or not to incorporate bands and headers, and tweak different presentational elements.

Then again, to realize the identical impact with the output of a PIVOTBY system, it is advisable to apply formatting manually. To make issues much more difficult, if you wish to make sure rows stand out in a PIVOTBY outcome, it is advisable to use conditional formatting to make sure the formatting adjusts to adjustments within the knowledge supply.

Added Visualization

As soon as you’ve got created a PivotTable in Excel, the enjoyable does not cease there. Certainly, on the click on of a button, you may add additional spectacular graphics to visualise your figures in ways in which make them simpler to learn and manage.

There are two instruments I significantly take pleasure in utilizing as quickly as I’ve created a PivotTable:

• Timeline: If the figures within the PivotTable are sorted based on totally different time durations, you may add a dynamic timeline filter that lets you shortly show knowledge from particular person days, months, quarters, or years.
• PivotChart: Despite the fact that PivotTables help you simply group and analyze your figures, you may go one step additional and show your knowledge in a PivotChart, which adapts routinely to adjustments within the PivotTable. Consequently, PivotCharts are extra versatile than common charts for knowledge summarization.

Each these instruments can be found by way of the PivotTable Analyze tab on the ribbon.

Numerous Knowledge Sources

Whereas the PIVOTBY perform can solely reference supply knowledge in Excel, for those who create a PivotTable in Excel 2016 or later or Excel for Microsoft 365, you may select to drag in knowledge from an exterior supply, together with relational databases like SQL Server or Oracle, different varieties of information like CSVs, XLMs, or ODCs, PowerBI databases, or internet pages.

This functionality permits for extra cross-network flexibility, which is particularly helpful for those who’re producing and analyzing knowledge within the office.

Ought to You Use the PIVOTBY Perform or PivotTables in Excel?

Let me emphasize at this level that the PIVOTBY perform and PivotTables have distinctive makes use of and will not be essentially replacements for one another. Nonetheless, for those who’re contemplating the easiest way to group and mixture a set of information in an Excel spreadsheet, listed below are the primary issues to remember:

Use the PIVOTBY perform if:

• You need the outcome to refresh routinely and, thus, show probably the most up-to-date info,
• You want working with formulation and like to make use of their arguments—relatively than tabs and menus on the ribbon—to regulate knowledge output,
• You need to flexibly customise how your knowledge is offered, and
• You need to incorporate totally different features, together with personalized LAMBDA formulation, to outline how your knowledge is aggregated.

Then again, use PivotTables if:

• You are seeking to reorganize your knowledge into formatted tables in only a few clicks,
• You favor working with tabs and panes over detailed formulation to customise your knowledge,
• You need to add further graphics to additional visualize your figures,
• You are prone to share the workbook with others, and
• The supply knowledge is in numerous codecs.

---

One other method to manage your knowledge into teams in Excel is by utilizing the GROUPBY function. Nonetheless, the place the PIVOTBY perform helps you to summarize your knowledge in rows and columns, GROUPBY works with rows solely, that means it requires fewer arguments once you generate the system.