The selected cell range s reference appears in the Import List From Cells field. To record your list, click the Import button. If you prefer, you can type the list in the List Entries box, to the right of the Custom Lists box.

Using Barcode creator for ASP.NET Control to generate, create EAN128 image in ASP.NET applications.

www.OnBarcode.com

Tip Another benefit of creating a custom list is that dragging the fill handle of a list cell that contains a value causes Excel to extend the series for you. For example, if you create the list Spring, Summer, Fall, Winter, then type Summer in a cell and drag the cell s fill handle, Excel extends the series as Fall, Winter, Spring, Summer, Fall, and so on.

Using Barcode generation for ASP.NET Control to generate, create MSI Plessey image in ASP.NET applications.

www.OnBarcode.com

In this exercise, you ll sort worksheet data, sort by multiple criteria, change the order in which sorting criteria are applied, sort data by using a custom list, and sort data by color. SET UP You need the ShippingSummary_start workbook located in your 06 practice file folder to complete this exercise. Start Excel, open the ShippingSummary_start workbook, and save it as ShippingSummary. Then follow the steps.

25. Click cell C3. Then on the Home tab, in the Editing group, click Sort & Filter,

and click Custom Sort. The Sort dialog box opens.

26. In the Sort by row, in the Column list, click Revenue. 27. In the Sort on list, click Cell Color. 28. In the new list control that appears in the Sort by row, click On Bottom to have

Excel put the Revenue cells that have no cell color on the bottom.

29. Click OK.

Excel sorts the data list. CLEAN UP Save the ShippingSummary workbook, and then close it.

Organizing Data into Levels

Organizing Data into Levels

After you have sorted the rows in an Excel worksheet or entered the data so that it doesn t need to be sorted, you can have Excel calculate subtotals or totals for a portion of the data. In a worksheet with sales data for three different product categories, for example, you can sort the products by category, select all the cells that contain data, and then open the Subtotal dialog box. To open the Subtotal dialog box, display the Data tab and then, in the Outline group, click Subtotal.

In the Subtotal dialog box, you can choose the column on which to base your subtotals (such as every change of value in the Week column), the summary calculation you want to perform, and the column or columns with values to be summarized. After you define your subtotals, they appear in your worksheet.

6

Reordering and Summarizing Data

Troubleshooting The appearance of buttons and groups on the ribbon changes depending on the width of the program window. For information about changing the appearance of the ribbon to match our screen images, see Modifying the Display of the Ribbon at the beginning of this book.

When you add subtotals to a worksheet, Excel also defines groups based on the rows used to calculate a subtotal. The groupings form an outline of your worksheet based on the criteria you used to create the subtotals. For example, all the rows representing months in the year 2009 could be in one group, rows representing months in 2010 in another, and so on. The outline area at the left of your worksheet holds controls you can use to hide or display groups of rows in your worksheet.