support

Grouping and Aggregation after Filtering

Below are sales amounts of some types of liquors. Find out the two places of production where the liquors have the top two highest sales for the two most popular types of liquors.
To summarize data according to the types of liquor, firstly you need to perform a sort:

esCalc_excel_aggregate_filter_1

Then aggregate each type of liquor to get the total sales amount. The result is shown below:

esCalc_excel_aggregate_filter_2

Sort the liquor types by the total sales amount. To do so, collapse rows to the level of type, and sort liquors by Amount in descending order. The result is shown below:

esCalc_excel_aggregate_filter_3

Expand the result, and only copy the sales data of the top two types of liquors in terms of sales amount to a new worksheet for further analysis. The result is shown below:

esCalc_excel_aggregate_filter_4

Since statistics are to be presented by places of production of the liquors, sort data ascendingly by Production:

esCalc_excel_aggregate_filter_5

Group data by Production and calculate the total sales amount for each place of production:

esCalc_excel_aggregate_filter_6

Collapse rows to the level of place of production, and sort data by the sales amount in descending order. The result is shown below:

esCalc_excel_aggregate_filter_7

You can view the final result from the Excel grid. If you need the details of the liquors from the top 2 places of production that have the highest sales amounts, then you will have to sort data out manually.

When using Excel to handle the data grouping and aggregation after filtering, you can complete the procedure easily though the filtering and sorting operations are a bit of complicated. However, since you need to summarize and analyze the filtered data, you will have to copy the filtered data or remove the undesired data manually. If the data volume or the number of groups is relatively great, the job would be hard, cumbersome, and error-prone.

esCalc_excel_aggregate_filter_8

Group rows by the type of liquor. Select a cell to perform Sort before group, and then calculate the total sales amount for each group in the grouping row:

esCalc_excel_aggregate_filter_9

In esCalc, you can directly sort the groups. To do so, select D2 and perform sort in a descending order:

esCalc_excel_aggregate_filter_10

You can directly filter the groups:

esCalc_excel_aggregate_filter_11

In esCalc, you can carry out a regroup operation. To do so, select C4 to perform a group operation by checking Sort before group and Regroup options. The setting with the Group panel is shown below:

esCalc_excel_aggregate_filter_12

As you can see from the above result, the grouping row and the bands under it have been regenerated after regrouping. And the formulas in the grouping rows have recalculated.
Sort the grouping rows again:

esCalc_excel_aggregate_filter_13

The desired result is the groups ranking the top 2. To get them, you can filter the groups again.

Because esCalc, the user-friendly spreadsheet software, enables performing sort, filter, regroup, and other operations directly on the groups, you needn’t manually handle grouping and aggregation after filtering. Data processing can thus be carried out step by step, and each step is purposeful and simple, with result shown intuitively.