Summarise data quickly by using Subtotal
Excel’s subtotal feature provides a quick and easy way of summarising tabular data. A common use of the feature is to display only the totals for different categories.
For example suppose you want to calculate and display only the total units for each product from data (partly shown below)
Step 1 Sort the data by the Product field.
Click inside the Product column and click Data, AZ button.
Step 2 Then click anywhere inside the table and select Data, Subtotal.
Step 3 From the Subtotal dialog select Product as the ‘At change in’ field and Sum as the function. Use Count if you want to display the number of transactions of each Product.
Click OK to automatically inserts total rows below each change in Products.
Step 4 Uncheck Summary below to add totals above each change.
The subtotal feature creates small outline buttons 1,2,3 at the top left of the screen.
Clicking button 2 shows only the Product totals and hides the transaction details.
Clicking a + symbol expands the details for a particular product.
Clicking button 1 shows only the grand total while button 3 shows all the data including the subtotals.
Removing Subtotals
To remove the subtotals completely select any cell in the summary.
- Select Data, Subtotal, Remove All.