Calculate subtotals

To calculate the sum of sales per product in the worksheet ‘sales’, you have to first sort on the “product” coloumn.

• Click anywhere in the ‘product’ column
• Click on the ‘Sort Ascending’ button.
• The full list is sorted by ‘Product’
• Place the cursor on any cell in column A
• Choose the ‘Subtotals’ (Subtotals) option from the ‘Data’ menu
• In the dialog box, choose’product’ in the “at Each change in” (at Each change in) option
• In the “Function” choose “Sum”
• In the “add subtotals to” you can calculate multiple subtotals. (Eg for sales and quantity)
• Click OK, and all subtotals for Sale of Items are calculated.

 

 

If you still want to know the average sales per product:

• Place the cursor anywhere in the list
• Select the ‘Data’ menu – ‘Subtotals’
• In the dialog box, you should select ‘Average’ (Average) in the ‘function’ box
• The average sales of items calculated is displayed in the empty cell

As the the intention is to calculate the average of values, it is necessary to turn off the checkbox in the “Replace Current Subtotals” option.

• Click OK, and the averages are calculated

 

If you want to sort the data within a subtotal list ,you must first select the entire range or remove the subtotals.

• Select from the range (A2 to G30)
• Select ‘Data’
• Select ‘Sort’ (Sort)
• and now you can choose how you want to sort

 

If you want to sort multiple products with subtotals, you must repeat the above steps for each product.

When you add subtotals to a list, Excel summarizes the structure in the worksheet using outline symbols on the left.

 

You will find the keys 1,2,3 and 4 and vertical lines which are called level bars.
If you click on button 1, only the grand totals and averages will be displayed at the end.
Click on button 2, all totals are displayed.
Click on button 3, all totals and averages are displayed.
Click the button 4 and the complete the list of all the details is displayed again.

Click on the ‘-‘ button next to the ‘kl.printer average” then the details of the kl.printer group are hidden.
Click on the ‘-‘ button next to the kl.printer ‘total’, then only the total for the kl.printer is shown.

You can also select groups of data using the “-” keys
Hold the Shift key and click simultaneously on the ‘-‘ sign
You can also select a group level using level bars:
Hold the Shift key and click on the desired level bar

If you are not able to view the worksheet data in summary form you can delete all or part of the list (this does not affect the actual data in the spreadsheet):
Click on any cell in the summary list
Select ‘Group and Outline‘ (Group and Outline) from the’Data’ menu dropdown
Choose ‘Clear List’ (Clear Outine) in the second drop-down menu

Here, we also have the formula for calculating the subtotals

If you place the cursor in cell D32 the entire formula appears in the formula bar.
You can see the function name “SUBTOTAL” along with the arguments, that is the range for which the subtotal is calculated: D2: D30.

For your information: The number 9 used as the first argument is the function number that determines which function is used for the calculation of the Sub-total, in this case, it is , the SUM.

• Click the Wizard functions button under the Insert menu. Click on the Help button that appears at the bottom of the Insert wizard and you get an overview of the function numbers from 1 to 11 with the associated functions.
• Change your number 9 in the example to number 2, you get the COUNT function.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats