MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

• How can I sort sales transaction data so that transactions are sorted first by salesperson, then by product, then by units sold, and finally in chronological order from oldest to most recent?

• I have always wanted to sort my data based on cell color or font color. Is this possible in Excel 2007?

• I love the great icons described in Chapter 22, “Conditional Formatting.” Can I sort my data based on the icon in the cell?

• My worksheet includes a column containing the month in which each sale occurred. When I sort by this column, I get either April (first month alphabetically) or October (last month alphabetically) on top. How can I sort by this column so that January transactions are on top, followed by February, and so on?

• Can I sort data without using the Sort dialog box?

Almost every user of Microsoft Office Excel has at one time or another sorted columns of data either alphabetically or by numerical value. Let’s look at some examples of how wonderful and powerful sorting is in Excel 2007.

• How can I sort sales transaction data so that transactions are sorted first by salesperson, then by product, then by units sold, and finally in chronological order from oldest to most recent?

• JAC is a small company that sells makeup. The Makeup worksheet in the Makeupsort-temp.xlsx file (see Figure 23-1 on the next page) contains the following sales transaction information:

  • Transaction Number

  • Name of salesperson

  • Date of transaction

  • Product sold

  • Units sold

  • Dollars received

  • Location of transaction

  Figure 23-1: Sales transaction data before sorting

  We would like to sort the data so that:

  • Transactions are listed alphabetically by salesperson. We want to sort in the usual A to Z order, so that all of Ashley’s transactions are first and all of Zaret’s transactions are last.

  • Each person’s transactions are sorted by product. Thus, Ashley’s eye liner transactions will be followed by Ashley’s foundation transactions, and so on.

  • For each salesperson and product, transactions are listed by number of units sold (in descending order).

  • If a salesperson sells makes two or more sales of the same product for the same number of units, transactions are listed in chronological order.

  In older versions of Excel, it was difficult to sort on more than 3 criteria. Excel 2007 allows the user to involve up to 64 criteria in one sort. To sort our sales data, we first select the data (cell range E3:K1894). Two easy ways to select this data are as follows:

  • Position the cursor in the upper-left corner of the data (cell E3), and press Ctrl+Shift+Right Arrow followed by Ctrl+Shift+Down Arrow.

  • Position the cursor anywhere in the cell range and press Ctrl+*.

  Next, on the Data tab, in the Sort & Filter group, click Sort to display the Sort dialog box shown in Figure 23-2.

  Figure 23-2: Sort dialog box not yet filled in

  Because row 3 contains headings for our data columns, we check the My Data Has Headers box. We will now select the following four criteria in the order shown:

  1. Sort by the Name column so that Values (this means cell contents) are in A to Z order.

  2. Sort by the Product column so that Values are in A to Z order.

  3. Sort by the Units column so that Values are in order from largest to smallest.

  4. Sort by the Date column so that Values are in chronological order from oldest to newest.

• Our dialog box now looks like Figure 23-3.

  Figure 23-3: Sort dialog box set up for sales sorting example

• The final result of our sort is shown in Figure 23-4 on the next page.

  Figure 23-4: Sorted sales transaction data

• Note that all of Ashley’s transactions are listed first, with eye liner followed by foundation, and so on. Eye liner transactions are listed from largest number of units sold to smallest. In the case of a tie (see rows 6 and 7), the transactions are listed in chronological order.

• Using the Sort dialog box, you can easily add sort criteria (Add Level), delete sort criteria (Delete Level), copy the settings that define a level of the sort, or specify whether your data has headers. By selecting Options, you can make the sort operation case sensitive or even sort data for which each case is listed in a different column (instead of the more common situation where each case is in a different row).

• I have always wanted to sort my data based on cell color or font color. Is this possible in Excel 2007?

• In Excel 2007, sorting on cell or font color is simple. Consider the Makeup worksheet in the Makeupsorttemp.xlsx file. Several names in column F are highlighted in different colors. For example, Cici in cell F620 is highlighted in red, and Colleen in cell F833 is highlighted in yellow. Suppose we want names with green fill on top, followed by yellow, and then by red, with the rest of the rows on the bottom. To sort the Name column by color, simply select the range you want to sort (E3:K1894), click Sort, and click Add Level. After selecting the Name column, click the Sort On setting, and select Cell Color (selecting Font Color sorts by font color). For the first level, select green from the Order list, select yellow for the second level, and select red for the third level. The completed dialog box is shown in Figure 23-5. The resulting sort is shown in Colors worksheet of the Makeupsort.xlsx file (see Figure 23-6).

  Figure 23-5: Sort dialog box set up to sort by color

  Figure 23-6: Results of sorting by color

• I love the great icons described in Chapter 22, “Conditional Formatting.” Can I sort my data based on the icon in the cell?

• To sort by icon, simply select Cell Icon from the Sort On area list in the Sort dialog box. Then, in the Order list, choose the icon you want on top for the first level, and so on.

• My worksheet includes a column containing the month in which each sale occurred. When I sort by this column, I get either April (first month alphabetically) or October (last month alphabetically) on top. How can I sort by this column so that January transactions are on top, followed by February, and so on?

• The Dates worksheet in the Makeupsorttemp.xlsx file contains a list of months (see Figure 23-7). We would like to sort the months so they appear in chronological order beginning with January. We begin by selecting the range D6:D15 and sorting column D by values. When selecting the order, we select Custom List and then select the option beginning with January, February, March. Note that we could also have sorted by the day of the week. The completed dialog box is shown in Figure 23-8, with the resulting sort shown in Figure 23-9.

• Figure 23-7: Months to be sorted

  Figure 23-8: Dialog box to sort by month

  Figure 23-9: Months sorted in chronological order

  Note that from the Custom Lists box you can create a custom sort order list. Simply select NEW LIST, and under List Entries, type the entries in the order you want to sort by, and then click Add. Your new list will now be included as a menu selection. For example, if you entered Jack John Alan in List Entries (on different lines or separated by commas), all entries with Jack would be listed first, followed by John listings, with Alan listings on the bottom.

• Can I sort data without using the Sort dialog box?

• Sometimes it is more convenient to sort data without using the Sort dialog box. To illustrate how this is done, suppose again that we want to sort the sales transaction data in the Makeup worksheet in the Makeupsorttemp.xlsx file, so that transactions are sorted first by salesperson, then by product, then by units sold, and finally in chronological order from the oldest to the most recent. To begin, we select the least important column to sort on first, which is the date column (G3:G1894). Next, in the Sort & Filter group on the Data tab, we click the Sort A To Z button (see Figure 23-10), and with the Expand The Selection option selected, click Sort so all our columns are sorted. The Sort A To Z button will sort numerical data so the smallest numbers or oldest dates are on top and will sort text so that A precedes B, and so on.

  Figure 23-10: Sorting

• The Sort Z To A button, of course, sorts numerical data so that the largest numbers or most recent dates are on top and sorts text data so that Z precedes Y.

• Next, we sort by the second least important column (Units) and click Sort Z To A because we want larger sales on top. We then sort from A to Z by Product, and finally from A to Z by salesperson. This achieves the same results as shown in Figure 23-4.