In Excel, groups, as well as summary area and detail area, are independent of each other. There is no inherent operational correlation between cells, so it is difficult to process grouped data. We usually need to write a great many formulas repeatedly, or process every group of data separately, like what is done for sorting, filtering and the copying of formulas.
With its unique “homocell” concept, esCalc can process the grouped data easily. The following is the grouped and summarized retail data of wines.
Suppose we are asked to compute the amount of “UnitPrice*Stock”. We just need to enter the formula “=E4*F4” into G4 for once and esCalc will skip the summary rows automatically and apply it to all the detail data intelligently, as shown below:
In esCalc, detail rows and subtotal rows have different operational roles and belong to different levels of “homocell”, that’s why esCalc will only copy the formula intelligently into the detail rows while Excel requires deleting every formula copied into the subtotal rows manually, which needs a lot of work.
Then suppose the grouped data need to be sorted according to the Stock. In the same way, we just need to perform sorting on cell F4 (or cell F5, F11) for once, and then the operation will identify its position intelligently and sort each group of data automatically. The result is shown below:
By contrast, we can only sort each group of data separately and manually in Excel. When the data is huge, it is easy to get wrong.
An alternative method is to dismantle the groups to make a two-dimensional table without subtotal rows, sort the data by Type and Stock and then group and summarize them. The problem of eliminating subtotal rows is if there are other data, like “ratio of subtotal to grand total”, we need to recompute them. Apparently both methods have their weaknesses.
But we can perform many difficult operations on grouped data using esCalc’s “homocell” concept. For instance, add another layer of group by grouping data according to Name and compute the stock of the same kind of wine or the average unit price of each kind of wine, or highlight the subtotal rows (like F3 and F10) with a special color.
Still, these operations cannot be realized intelligently in Excel. For instance, to add another layer of group by grouping data according to Name and compute the stock of the same kind of wine, we cannot group data directly because the data haven’t been sorted by Name. So we have to sort each group of data first but, as we mentioned above, this is quite complicated.
There are two methods to compute the average unit price of each kind of wine. Both are complicated. One is to dismantle the groups, copy the data twice to compute average unit price and total stock respectively and then combine the two kinds of summary values together. The other is to enter a formula into each subtotal row, like “=SUBTOTAL(1,E4:E9)” for E3. But because each group has different data, N different formulas need to be entered, which is easy to get wrong.
Even for the simple operation of highlighting subtotal rows with a special color in Excel, we have to get it done one group after another. It is not automatic at all and much less convenient than esCalc.