Function GROUPBY

If writing SQL queries is in your job description I am certain that you, in great extent, inside SELECT command, use a GROUP BY clause that allows you to group row values by a given column and then apply an aggregation function, e.g. SUM, COUNT, AVERAGE, MIN, MAX… I have good news for you: we can achieve almost the same thing within an Excel report by using the GROUPBY function…

We will start with a table that contains sales data for a certain period of time. It will be the source on which we will create reports.

The syntax of the GROUPBY function is as follows:

GROUPBY (<grouping>,<values>,<f>,<zaglavlja>,<totals>,<sort>,<filter>,<result>)

The first argument of this function is the data range, that is, the column that contains the values ​​that we need to group. The second argument is also a column, but the one that contains the values ​​over which the aggregation is performed, while the third represents the function used to aggregate the data.

Let’s see how this works with an example right away! In the given table with sales transactions, column D stores data on classifications of sold items, and column E stores sales amounts. If we want to perform grouping according to classification and add up all the amounts, we need to write the formula:

=GROUPBY($D$4:$D$53,$H$4:$H$53,SUM)

The result is a simple table that returns grouped data and automatically calculates the total value.

The next argument, after we specified the function for data aggregation, is an indication of whether there are headers in the selected range (column) to be grouped, and we specified it as the first argument, and whether header needs to be displayed. After this argument, a number is specified by which we specify whether and at what level of detail total values ​​should be displayed. For example, if we complete the formula:

=GROUPBY($D$4:$D$53,$H$4:$H$53,SUM,0,0)

this means that there is no header and is not displayed, and no totals are calculated and displayed!

The next argument to the function represents the sort order. If we specify +1 here, the order is ascending, and -1 sets the sorting in descending order. The last two arguments are the filter, if we want to set the criteria according to which the selected data is filtered, as well as the way in which the result is returned: as a hierarchy (0) or as a table (1). Excel always returns grouped data, these are just two ways of presenting it. Let’s write the formula:

=GROUPBY($E$4:$E$53,$H$4:$H$53,SUM,3,0,1,$D$4:$D$53=”KEKS”,1)

With this formula, we perform grouping according to manufacturers, add the amounts, with the given filter that the classification value is “KEKS” (BISCUITS), and the result is returned as a table.