Creating Spreadsheets with Specific Formats

Here’s a list of scores for a gymnastics competition, which has been grouped and aggregated by athlete. The total score of each athlete is calculated in the summary row that on the top of each group. Now you are required to add a new row at the end of each group to calculate the average score of each athlete, and, in addition, in each group set the font color as dark blue and the background color as light blue for the top summary row, and vice versa for the bottom summary row.

Excel

Excel offers a fixed format for data categorization and summarization. The summary row can be either at the top or at the end of a group, but a format that there is a summary row at both the top and the end is not allowed. In view of this, you have no choice but to abandon performing grouping and aggregation directly and choose to obtain a same effect through ordinary grid operations. To do so, copy the data to a new worksheet, and append a blank row after each group. The result is shown below:

Calculate the average score of every athlete in the summary row after each group:

Finally, adjust the color of the summary rows as required. A faster way is to first set color for the top summary row and then for the bottom row, with the help of the format painter to make the whole work more efficient. But you still need to select the summary rows one by one in each group. The result is shown below:

Excel has the categorization and summarization functions to provide much convenience for data analysis. But, as the format requirement on the categorization and summarization is relatively strict, they cannot be applied to build reports requiring special formats. The various appearance properties of cells in Excel are independent of each other. To change the appearance, you have to set it manually. In cases when the data volume is relatively great or there are many groups, such a job will be cumbersome and error-prone.

esCalc

In esCalc, there can be one or more slave rows for one row to meet various requirements:

Calculate the average score of each athlete in the slave row:

Finally, adjust the colors of summary rows according to the requirements:

With esCalc, the desktop BI software, you are free to choose a desired format for each group so as to ensure that requirements on report preparation can be met. In the real-world business, data of the same information background is usually required to share the same appearance attributes during report building. esCalc introduces the concept of homocell to conveniently describe such type of data. By default, the homocells in the homorows will use the same appearance properties, saving a lot of cumbersome operations.