Excel cannot distinguish summary data from detail data due to the lack of inherent operational correlation between cells. Therefore the operation of grouping and filtering data always produces meaningless data. In order to correct the mistake and remedy this defect of Excel, users have to modify the formula for each group of data at the expense of more work or count on cooperation of different departments by referring to an expert.
esCalc, on the contrary, can group and filter data efficiently. For example, here is a contact list of distributors, in which data have been grouped by the country, including the subtotal and the maximum value of sales amount for each country.
Case 1：Select countries in which the sales amount is greater than 20,000 dollars by the subtotal of sales amount.
In esCalc, we just need to pull the data back to summary area and filter them, as shown below:
The detail data are normally displayed when we expand a record:
It seems that the result is natural. But the same operation will produce errors in Excel. The following shows the filter result in Excel:
In the above figure, all summary values become zero and an extra item of “Switzerland” is expanded, which is obviously not what we expected. The reason for this is because Excel cannot differentiate summary area from detail area, affecting detail data during the filtering of summary values and causing chaos in both detail and summary area.
Case 2：Select outstanding distributors in each country. The criterion is half of the maximum sales amount in the country.
In esCalc, we only need to enter a formula in detail area of any group (say the first group):
Once the formula is entered, it will be adjusted and executed intelligently in all other groups, without the need of manual intervention. The final result is as follows:
It can be seen that esCalc can finish the task intuitively and easily with the expected result.
But Excel can always bring the unexpected. The first problem comes with the computation of half of the maximum sales amount in each country. If pull the data back, enter the formula in B3 and drag it to other cells, we’ll find that the detail data in column B have been replaced by “#Value!” on expanding it.
This is also because Excel cannot identify summary area and detail area. In this case, we have to copy the formula manually for each group. For over 200 countries, it is extremely easy to get wrong.
It is even more troublesome to filter the detail data by column B. We can only filter groups one by one by manually entering the filter value and then copying the filter result to a new sheet for later use (otherwise the operation cannot continue). This needs a great deal of work and it is impractical to completely depend on manual labor.
By proposing the “homocell” concept, esCalc can handle data of the same operational level simultaneously and intelligently, without affecting data of different operational levels. That is to say, filtering data in summary area won’t disrupt the detail area. In a nutshell, esCalc is much more convenient to use than Excel.