Excel Tips and Tricks / Business Reporting Question: I have a long data list of products & selling prices stored in an Excel sheet. Is it possible to have a breakdown of subtotals for each product category?

Answer: Yes; by using the subtotal option in Excel

Why: To automatically insert subtotals and totals for the selected cells

Important: The Subtotal command will appear grayed out if you are working with a Microsoft Excel table. To add subtotals in a table, you must first convert the table to a normal range of data, and then add the subtotal. Note that this will remove all table functionality from the data except table formatting.

Applies: To MS Excel 2003, 2007 & 2010

1. Refer to the screen shot in the example below.

2. The list must be sorted by category names for the subtotal option to be effective.

3. Select any cell within the data list and select data-subtotal as given below.

4. The screen shot below will be displayed.

5. Select the options as given above.

6. Select OK.

7. The data given below will be displayed.

As can be seen above the data has been grouped by the category names whose subtotals for product sales are displayed.