Creating Subtotals

Creating Subtotals

Creating subtotals is a part of  Unit 2 Electronic Spreadsheet (Advanced). In the previous topic, we discussed consolidating data. So what to do if you want to view the aggregate results of data you have supplied in your spreadsheet? How to display a formula results according to the column header? So the answer is you can use subtotal. 
The contents are as following:
    1. Introduction to Subtotal
    2. Using subtotals in Excel
    3. Using subtotals in OpenOffice Calc

Introduction to subtotal

Spreadsheet software provides powerful features to analyze your data. There are some advanced features that can help to perform some advanced functions on the formula results. Subtotal is one of them. Subtotal is used to display your formula results according to columns group and row labels. There are two methods to achieve the same thing in spreadsheet software.
  1. Subtotal() formula: The subtotal() formula to group the results.
  2. Data → Subtotals command: The graphical command to do subtotals.

As per the CBSE study material, We will cover the Data → Subtotals command. 

Follow below-given links to read about how to use subtotal() formula:

Using subtotal in MS Excel

Before you start searching the subtotals option into the ribbon prepare your data in the worksheet. I have created data on the score of different houses as per given the screenshot below. 
Data for Creating Subtotals Electronic Spreadsheet (Advanced)
Data for Creating Subtotals Electronic Spreadsheet (Advanced)
Now follow these steps to create subtotals:
Step 1: Click on Data → Subtotal from the outline group. 
Data - Subtotals in MS Excel
Data – Subtotals in MS Excel
The Subtotals dialog box will appear as displayed in the following screenshot.
Step 2: Select the column header on which you want to apply subtotal. 
Step 3: The second option is use function where you can select a function to display the results.
Step 4: After selecting function select the values on which grouping is required. 
Step 5: You can select the option as per your need from different options from the following:
  1. Replace current subtotals: This option is used to replace the values of subtotals.
  2. Page break between groups: It will start a new page from the new group.
  3. The summary below data: It will display the summary of below the group.
  4. Remove all: To previous subtotals 

Step 6: Click on the ok button.

Observe the result is given in the following screenshot where I have applied subtotal on the class group column.
Data - Subtotals results
Data – Subtotals results

Using subtotal in OpenOffice Calc

Step 1: Click on Data → Subtotals. 
Data - Subtotals in OpenOffice Calc
Data – Subtotals in OpenOffice Calc
A subtotals dialog box appears as displayed in the following screenshot:
Subtotals in OpenOffice Calc
Subtotals in OpenOffice Calc
Step 2: There are 3 group tab buttons given, so you can group your columns from data up to 3 groups. The options tab contains different options as displayed in the following screenshot:
Options tab of subtotals dialog box in OO Calc
Options tab of subtotals dialog box in OO Calc

The first option groups are similar as explained in the MS Excel section. The second option is given for sorting records. You can sort either ascending or descending order. Include formats option allows to attach the format applied in the column header. 
Step 3:  Select the group options and functions and click on OK.
Now observe the following output screen. 
Result Subtotals in OpenOffice Calc
Result Subtotals in OpenOffice Calc
Thank you for reading this article. Post doubts or queries in the comment section or posts your opinion about this post as well. Your support is essential for me. Like, share, and follow our blog to enjoy learning. 
Watch the complete video lesson.

Comment Your Views

%d bloggers like this: