Excel users can now rejoice! Two groundbreaking functions, GROUPBY and PIVOTBY, have arrived to transform how you create summary reports. Imagine the power of pivottables, but with the added benefit of automatic updates whenever your data changes. No more manual refreshing – these functions keep your reports current in real-time.

With a single formula, you can generate a comprehensive two-dimensional report that elegantly summarizes your dataset. this is a game-changer for data analysis and reporting.

For optimal performance, it’s highly recommended to use a formatted table as the data source for these functions. Formatted tables possess the remarkable ability to expand automatically as you add data, ensuring that your reports remain perpetually up-to-date.

Understanding the Data

Let’s consider a sample sales dataset, aptly named “tblSales,” to illustrate the capabilities of these new functions. (See Figure 1 in the original article.)

the GROUPBY Function: A Deep Dive

The GROUPBY function, the simpler of the two, requires only three arguments to conjure a summary report. It’s a streamlined approach to data summarization.

In cell A2, a single function spills down and across, breathing life into a dynamic summary report. (Refer to figure 2 in the original article.)

This report elegantly lists the unique entries from the “State” column and meticulously sums the corresponding values from the “amount” column, providing a concise and insightful summary.

The third argument empowers you to select the specific function to be used within the report, granting you unparalleled control over the summarization process. (See Figure 3 in the original article.)

Formatting Considerations

It’s important to note that the spill range, sadly, does not inherit formatting. Therefore, manual formatting of the reports is necessary. The subsequent examples will showcase formatted reports for clarity.

Summarizing Multiple Columns

The GROUPBY function isn’t limited to single columns; it can effortlessly summarize multiple columns simultaneously.Let’s explore an example. (See Figure 4 in the original article.)

To enhance readability, the formula has been split over two lines in the Formula Bar.

When the “Customer Category” column resides adjacent to the “State” column within the table, you can create a single, unified reference to encompass both, as demonstrated in the formula. Note the presence of the extra set of square brackets surrounding the column names and the colon elegantly separating them.

But what if the columns are not conveniently located next to each other?

Fear not! There exist at least two functions that can seamlessly combine separate columns,enabling you to overcome this challenge.

HSTACK function example” style=”display:none”>

HSTACK Function: Combining Columns

The HSTACK function steps in to combine columns of data into a single, cohesive range. Figure 5 in the original article showcases a summary report organized by “Source” and “Size,” highlighting the power of this function.

CHOOSECOLS function example” style=”display:none”>

CHOOSECOLS Function: Selecting Columns by Number

Alternatively, the CHOOSECOLS function combines columns based on their column number within a range. This approach results in a more concise formula, although it may sacrifice some descriptiveness compared to HSTACK. Figure 6 in the original article presents the same report as Figure 5, but this time created using CHOOSECOLS.

In this instance, the CHOOSECOLS function is intelligently combining the 6th and 8th columns from the table.

Totals and Subtotals

By default, GROUPBY displays the overall total at the bottom of the report. The fourth argument provides granular control over total positioning, allowing you to include or exclude subtotals as desired. Totals can be omitted entirely or positioned at the top or bottom of the report. Figure 7 in the original article illustrates these options with two examples.

Conditional Formatting for enhanced Clarity

Totals frequently enough warrant distinct formatting to differentiate them from detail rows.Conditional formats can be employed to apply unique formatting to total rows, irrespective of their position within the report. Examples can be found in Figure 8 of the original article.

The Conditional Formatting settings for the report are detailed in Figure 9 of the original article. The rule applies a bold font with top and bottom borders when cells in column B are blank. The $B2 reference ensures that all three columns within the range refer to column B.

This conditional format seamlessly adapts to reports with two or more levels, assuming that there are no blank cells in the “Size” column within the data table.

Filtering Data

PivotTables are renowned for their filtering capabilities, and GROUPBY doesn’t disappoint. the seventh argument of the GROUPBY function empowers you to filter your data with precision. In Figure 10 of the original article,the report showcases customers specifically from WA.

Slicer Filtering: A Graphical Approach

Just like PivotTables,you can filter GROUPBY reports using slicers,providing a visually intuitive filtering experience. Slicers, graphical filter user interfaces, can be seamlessly integrated to filter formatted tables.

While the formula solution for slicer filtering can be intricate, it’s explored in greater depth in the companion video. An example is provided in Figure 11 of the original article.

Multiple Value Columns: Expanding Your Reporting Horizons

the GROUPBY report isn’t limited to a single calculation; it can effortlessly return multiple calculations simultaneously. The new PERCENTOF function automates the percentage of total calculations,mirroring the capabilities of PivotTables. HSTACK is then used to incorporate more than one function into the report.

figure 12 in the original article demonstrates the power of the PERCENTOF function.

DROP Function: Refining Your Report’s Appearance

As illustrated in Figure 12 of the original article, the GROUPBY function, when returning multiple functions, includes the function names in the first row. The DROP function provides a simple solution to remove this heading row, resulting in a cleaner, more polished report.

An example of the DROP function in action can be found in Figure 13 of the original article.

Flexible Reports with INDIRECT

While pivottables offer unparalleled ease of modification, changing the GROUPBY function requires manual formula editing. To inject flexibility into your GROUPBY reports, consider leveraging the INDIRECT function.

The INDIRECT function dynamically converts text into a reference that can be utilized by a formula.This reference can point to a cell, range, formatted table, or even a range name. this ingenious approach allows cell entries to govern and dynamically alter the report generated by the GROUPBY function.

Illustrative examples can be found in Figures 14 and 15 of the original article.

In these examples, users can effortlessly select the desired columns for the report using the two designated yellow drop-down cells.

PIVOTBY Function: Unleashing Two-Dimensional Power

The PIVOTBY function mirrors the functionality of GROUPBY for rows but extends its capabilities to include columns in the report, enabling true two-dimensional analysis. It boasts 11 arguments, with the first four being mandatory and the remainder optional.

A basic PIVOTBY report is showcased in Figure 16 of the original article.

In this example, “Customer Category” is elegantly listed in the rows, while “State” is prominently displayed in the columns.

PIVOTBY Function Arguments

the same arguments governing totals and filters in GROUPBY also apply to PIVOTBY. The additional arguments cater to settings specific to the columns within the report.

Handling Dates with TEXT

PivotTables excel at grouping dates into months, quarters, and years. To achieve similar date grouping in GROUPBY and PIVOTBY, you can harness the power of the TEXT function.

The TEXT function adeptly converts dates into text strings. Though, it’s crucial to remember that text is sorted alphabetically in GROUPBY and PIVOTBY. This means that month names will be listed alphabetically, starting with April and August.To circumvent this issue, a specialized custom number format code is required.

An illustrative example is provided in Figure 17 of the original article.

The code “yyyy-mm-mmmm” within the TEXT function displays the full year, the two-digit numeric depiction of the month, followed by the full month name. This structure ensures that dates are grouped and displayed in the correct chronological sequence.

Performing Extra Calculations with LET

Calculating a margin percentage directly within the GROUPBY function is not possible. However, the LET function comes to the rescue, allowing you to capture the GROUPBY result