A Hard Nut to Crack on Filtering the Grouped Data

The spreadsheet has not provided any good solution to the filtering on the grouped data, which make the business users suffer because they will have to resort to the IT stuffs while making a business analysis.
 

Suppose that you need to filter out the countries in which the sales are above 200 million US dollars. It seems that you can just collapse the data to the 2nd level and then filter directly. However, the filtered result is:
 

The summary value in the subtotal column turns out to be 0, and the Switzerland is expanded additionally. It is obvious not the result we have expected. The reason to these phenomena is that the common spreadsheet cannot tell the summary section from the detail section. Filtering on these summary values will affect the details and double the mess for both the detail and the summary.

Then, let’s check the 2nd filtering: filter out the outstanding distributer by the half of the highest sales. Considering this way of filtering, we can see that the first trouble is to calculate the half of the total sales in each country. If collapsing the data section, entering the expression in B3, and dragging it to other cells, then you will find that the original detailed data in the column B will be replaced with “#Value!”:

       

Again, this is also because the summary section and the detail section cannot be differentiated. In this case, the formula can only be copied one by one manually. For these nearly 200 countries, it is almost impossible to make no error.

Then, it is more cumbersome to filter the details by the data in the column B. You can only filter respectively one group after another and enter the filtered value manually at the first. Then, copy the filtered result to the new sheet for future use, or the filtering cannot be carried on. This job involves great workload, and almost impossible to finish manually. Shall we return to the Office Primitivation?

esCalc can achieve the Office Automation for such kind of grouping problems. esCalc introduces the concept of “homocell”, which can have the data of the same business status to be calculated automatically, and the data of different status will not be interfered mutually.  Thus, the filtering in the summary zone will not affect the detail, and vice versa. For example, to filter by the subtotal of sales, simply collapse the data and filter in the summary section: 

Once expanded, you will find that the detail data will not be affected:

If filtering out the outstanding distributor by the half of the total sales, it will be much easier. Enter the formula in the detail zone of any group like the group 1:

Just input this formula once, and it will be executed in other groups automatically. The formula will be adapted automatically with no human intervention required. The final result is shown below:

It is originally posted on it.toolbox.com.