Analyse data: consolidating data; groups and subtotals

Hello students, welcome to Kwickprep. A school canteen keeps one sheet of sales for each month. The principal asks for the total sales of the whole term. Will you add every sheet by hand? Today we will learn to analyse data in LibreOffice Calc, using consolidation, subtotals and groups.

First, what does analysing data mean? It means turning many rows of numbers into a short, clear answer. To combine data from many sheets into one, we use Consolidate. To get a total for each category, like each class, we use Subtotals. To hide or show details with one click, we use Group and Outline.

Consolidation means combining data from several ranges into one summary table. A range is a group of cells, like A1 to C5. The ranges can be on different sheets of the same file. Calc joins them using a function you choose, such as Sum, Average, Count, Max or Min. You will find it in the Data menu, under Consolidate.

Here is our example, with each month on its own sheet. Samosa sales are twelve hundred rupees in July and fifteen hundred in August. Tea sales are eight hundred in July and nine hundred in August. Juice sales are six hundred in July and seven hundred in August.

Now the steps. Open the Data menu and choose Consolidate. In the Function box, choose Sum. In Source data range, select the table on the July sheet. Click Add, then select and add the August table the same way. In Copy results to, pick the cell where the summary should start. Click OK, and the summary appears.

Under Options, there are three useful boxes. Tick Row labels, so Calc matches rows by item name, even if the items are in a different order. Tick Column labels, so it matches columns by their headings. Tick Link to source data, so the summary changes when a monthly sheet changes.

Pause and predict. Using Sum, tea becomes eight hundred plus nine hundred, which is seventeen hundred. Juice becomes six hundred plus seven hundred, which is thirteen hundred. What will samosa show? Twelve hundred plus fifteen hundred is two thousand seven hundred rupees.

Now, subtotals. A subtotal is a total for one group of rows inside a bigger list. For example, a fee list may need the total for each class separately. Calc also adds a grand total for the whole list at the end. Before you begin, sort the data by the column you will group by, so each group sits together.

Here are the steps. First, sort the list by the Class column. Select the whole table, including headings. Open the Data menu and choose Subtotals. In Group by, choose Class. Tick the Fees column to calculate, and choose Sum as the function. Click OK, and a subtotal row appears under each class.

After subtotals, small buttons numbered one, two and three appear on the left. Button one shows only the grand total. Button two shows each class subtotal and the grand total. Button three shows every single row of data again.

A group is a set of rows or columns that you can hide or show together. First, select the rows or columns that belong together, such as the April to June columns. Then open Data, choose Group and Outline, then Group, or press F12. A minus button appears, and clicking it hides the group, while plus shows it again. To remove the group, choose Ungroup, or press Ctrl and F12.

Sometimes you need your plain list back. Click any cell inside the table. Open Data and choose Subtotals again. Click Remove All, and the subtotal rows disappear while your data stays.

Let us revise what we learned today. Analysing data turns many rows into a clear answer. Consolidate combines ranges from many sheets into one summary. You add each range with the Add button, then click OK. For subtotals, sort first, then use Data, Subtotals. And Group, or F12, lets you hide or show rows and columns with one click.