Using Subtotal in Excel

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)

Data

Step 1 Sort the data by the Product field.
Click inside the Product column and click Data, AZ button.

Data2

Step 2 Then click anywhere inside the table and select Data, Subtotal.

SubTotals

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.

SubTotalsResult

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.

Continue reading “Using Subtotal in Excel”