Date: January 25th, 2008

Author: Susan Harkins

Lists are new in Excel 2003 and they’re great, unless you want to subtotal columns; Excel disables the Subtotal feature for lists. Now, you can keep the list and produce subtotals the hard way. Or you can temporarily convert the list into a conventional range and subtotal the easy way. I always choose the easy way unless I’ve got a good reason to take the hard route.

Excel 2007 calls lists Tables. If you’re using Excel 2002 or an earlier version, lists aren’t available, but you can still use the easy Subtotal feature. You’ll know lists are in place by the small drop-down controls to the right of each heading cell.

Before you can subtotal list data, you must convert the list into a conventional range, viagra and premature ejaculation as follows:

Once you have a conventional range, sort the data according to your subtotaling needs. For instance, to subtotal the Total column by Salesperson, you must first sort the data by Salesperson, as follows:

1. Click in any Salesperson cell.
2. In Excel 2003 and earlier, click Sort Ascending or Sort Descending, accordingly. Excel 2007 users much select a specific sort by clicking Sort and Filter in the Editing group on the Home tab. Why they made sorting so difficult in 2007, I haven’t a clue.

Now you’re ready to add subtotals, as follows:

1. Click anywhere inside the spreadsheet.
2. In Excel 2003, choose Subtotals from the Data menu. Excel 2007 users should click Subtotal in the Outline group on the Data tab. In the Subtotal dialog box, you have a number of choices:
   • From the At Each Change In control, choose the column by which you’re subtotaling (not the column that contains the values you’re subtotaling). In this case, that’s the Salesperson column.
   • From the Use Function control, select the appropriate function. Sum is the default, and in this case, the desired function.
   • Check the appropriate columns in the Add Subtotal To section. In this case, check Total.
3. After specifying the right columns and functions, click OK.

This feature inserts a subtotaling row and result for each group as specified in the Subtotals dialog box. In this case, Salesperson is the group, so Excel displays a subtotal for both Susan and Bill.

To reclaim the previous lists, select the spreadsheet, press Ctrl+L, and click OK to close the Create List dialog box. You don’t even have to remove the subtotals first. The first time you sort any list in the spreadsheet, Excel will warn you that it’s going to remove the subtotals. However, you can remove the subtotals by displaying the Subtotal dialog box and clicking Remove All.