Software

How to subtotal Excel lists the easy way


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.

jan2008blog6fig1r.jpg

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

  1. Click anywhere inside the spreadsheet.
  2. In Excel 2003, choose Lists from the Data menu and select Convert To Range. Excel 2007 users should click the Design tab and click Convert To Range in the Tools group.
  3. Click OK.

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.

jan2008blog6fig2r.jpg

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.

jan2008blog6fig3r.jpg

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.

jan2008blog6fig4r.jpg

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.

If you'd rather copy the spreadsheet and sort the copy instead of disabling and enabling lists, see How to sort a single column in Excel without disrupting the rest of the spreadsheet.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

0 comments

Editor's Picks