Are Pivot Tables Obsolete? – NEW GROUPBY and PIVOTBY Functions in Excel

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

Excel has recently equipped us avid Excel users with two new functions, GROUPBY and PIVOTBY, enhancing your data aggregation capabilities right within your formulas. These innovative additions streamline the process, offering you an on-the-fly way to analyze your dataset.

With GROUPBY, you can easily categorize and tally up values, and PIVOTBY lets you cross-reference data, dynamically reflecting changes to provide an up-to-date snapshot. Naturally, the functions remove the extra step of refreshing, as you would with a pivot table, making your data analysis as efficient and seamless as Excel promises it to be.

Exploring Data Aggregation with Grouping and Pivoting

Excel’s introduction of the GROUPBY and PIVOTBY functions takes data analysis to new heights, allowing you to summarize large sets of data with a simple formula. If you’ve ever juggled a pivot table to look at data from different angles, you know the drill: manually refresh to update. But with these new tools, you witness real-time changes, no manual refresh needed, as they automatically adjust to reflect any alterations in your dataset.

Imagine having a year’s worth of data from various events at a local arena neatly laid out before you—trade exhibits, sports games, performances, you name it. The GROUPBY and PIVTOBY function can effortlessly compile this mishmash of event details—attendance numbers, ticket sales, and profits—into a coherent summary.

groupby function output

Let’s take a look!

Grouping Data with GROUPBY

Setting Up Your Rows for Grouping

To begin grouping your data, you’ll use the GROUPBY function to determine what information you’ll see in each row. Think of row fields as the backbone of your data set—the categories you’ll view your information by.

select your row fields in Groupby

It’s like organizing your closet by shirts, pants, and jackets to see what you have at a glance. For instance, you might choose the type of arena event as your row field.

Choosing What Data to Examine

In the next step, you’ll want to decide on the data you wish to analyze, which involves selecting the values. Values are the numbers you’re looking to understand better. That could be anything from tickets sold to the profit generated. Maybe management has been sorting through receipts, and are interested in the total staff required for each event or the number of tickets bought by month.

Selecting Your Aggregation Method

Now, it’s time to choose how you want to crunch your numbers with a function argument. This is where you decide whether you’re summing up totals, finding averages, or looking for maximum values. This new function gives you plenty of built-in aggregations to form insightful conclusions.

groupby aggregations

Deciding on Field Headers Display

You also have a choice on whether to display field headers with your grouped data, and it’s pretty straightforward to use. Usually, I like to display my field headers for the benefit of anyone unfamiliar with the data. But it’s not always necessary as a picture can speak for itself. Either way, just indicate your preference, and the GROUPBY function will do the rest.

Customizing Total Rows Display

At the total depth argument, you get to choose if you want to see a grand total, subtotals, or if you prefer a cleaner view with no totals at all. Be aware that if you leave this argument empty, Excel will show grand totals by default. So, you’ll need to enter a zero to get rid of them.

Sorting and Filtering Your Data

Options for sorting and adding filter criteria let you refine your grouped data. This step allows you to view your data hierarchy and omit specific, less relevant details.

The proper syntax is simple when it comes to sorting. To sort in ascending order, you’d use a positive number. Descending order requires a negative number. And the number chosen will reflect the index location of your column.

So, if I wanted to sort on the values in the third column in ascending order, my argument entry would be 3.

sort and filter groupby

A Closer Look at the PIVOTBY Feature

Understanding PIVOTBY Command Construction

The PIVOTBY function works just like GROUPBY. However, you get the added arguments to provide column selections for your data. By using the columns features, you can show a new level of aggregation that you’re used to viewing in a traditional Pivot Table.

But as I said before, the best part of this alternative is that as the values get updated, so does your function output.

To learn more about how this works, be sure to check out the video at the top!

Final Thoughts

By processing data with the GROUPBY function, you simply choose your row fields which act like categories to arrange your data. Then, you’re able to further customize the output with functions like SUM and MAX and tailor the returned view to your liking.

PIVOTBY steps up the game allowing an additional column field, perfect when you want to cross-reference data, count the number of instances, or analyze various categorical percentages. You can sort the results to your preference, apply filters to exclude anomalies, and explore what insights the information is trying to give you.

Now, the $5 million question: Are these new functions better than a Pivot Table?

Leave a Comment