Page 149 - IT_V1.0_Class10
P. 149
The Subtotal command is used to automatically create groups and apply inbuilt functions like SUM, COUNT,
AVERAGE, etc., to summarise data. One can use any type of summary function for each column as per the
requirement of data analysis. It can group subtotals by using category and sorts them in ascending or
descending order so that one need not use filters.
For example, the Subtotals command can be used to calculate the number of Jerseys of different sizes
required for players.
To insert Subtotal values into a sheet:
Step 1: Select the range of cells with values for which Subtotal function needs to be implemented. Ensure that
the columns must have labels. The subtotal function has a graphical interface and is accessible from
Data menu. Using the Subtotals, you can select arrays, and then choose a statistical function to apply
to them. For efficiency, you can choose up to three group of arrays to which a function can be applied.
Step 2: Click on Data menu. Select Subtotals option. Subtotals dialog box appears on the screen.
Step 3: In 1st Group tab, from the Group by drop down box, select the column for which subtotal is required
or if you wish to select all the columns check in Select all columns. If the content of the selected
column changes, the subtotals will automatically be updated.
Step 4: In the Calculate subtotals for box, select the columns for which subtotal needs to be calculated.
Step 5: In Use function box, select the desired function to calculate the subtotals. (Here, we have used
Count function.)
Step 6: Click on the OK button.
3
4
5
1
2
6
rd
You can use the 2 Group and 3 Group tabs to group the data in further levels in same sheet. For
nd
example, if we add colour and cost to the sheet, we can make another group showing sum of cost of
jerseys colour-wise for each size.
First group finds the count of sizes available, second group displays colour and third group displays cost for
the different jersey sizes available.
Analyse Data using Scenarios and Goal Seek 147

