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.
Copying Subtotal data
When subtotal summary data is displayed at level 1 or 2 the detail rows are hidden by Excel. However this data doesn’t remain hidden if you copy to another sheet or application such as Word or Outlook. There is a way to copy only the ‘visible cells’ as follows:
- First select the data (click a cell inside the subtotal summary then press ctrl+a to select all.
- Select Home, Find & Select
- Go to Special, Visible cells only
- Now select Copy
- Click in the destination sheet or document
- Select Paste
This will ensure only at only the summary data is copied and pasted.
Resources
Insert a Sub-Total in excel 2010
