Accounting Network News

By David H. Ringstrom, CPA

Just about everyone sorts data in a spreadsheet — it’s an easy way to organize data in a snap. However, even long-time spreadsheet users should stop and take notice of the new sorting capabilities in Excel 2007. First, you’re no longer limited to sorting on just three columns — sort data on up to 64 columns if you wish. Even better, you can now sort by cell color, font color or cell icon. Sorting has truly been transformed in Excel 2007, but if you haven’t upgraded yet, I’ll also cover sorting features that you may have overlooked in Excel 2003 and earlier.

Try it now: A free 60-day trial of Office 2007 is available for immediate download. You can instruct the installation program to leave your present version of Office intact so that you’re free to work in either version. Or, try the online test drive.

Transition tools: Office Online offers an online command reference that shows where your favorite Excel 2003 commands now reside in Excel 2007. You can also download and install the command reference tool on your computer. Yet another option is to add the Get Started tab to your Excel 2007 ribbon — this puts numerous training aids at your fingertips.

Sort in Excel 2007

The sort command now appears in several places:

§ Under the Sort & Filtering icon in the Editing group in the Home tab of the ribbon.

§ In the Sort & Filter group of the Data tab of the ribbon.

§ In a drop-down list that appears when you click any AutoFilter arrow

§ In the shortcut menu that appears when you right-click on a worksheet cell

Simple sorts, such as A-Z or Z-A in a single column, work just the same as always in Excel 2007. The magic begins when you have multiple columns of data to sort or you want to sort by color. For example, I exported the customer list from the Fabrikam, Inc. sample company in Office Accounting:

1. Choose Customers, Customer Lists and then Customers (or press Ctrl-Shift-C).

2. Choose Actions and then Export to Excel.

3. Hold down the Ctrl key, and click rows 9, 15 and 20 on the worksheet frame. Click the Yellow fill color icon in the Font section of the Home tab.

4. Click on cell A6 — you can actually click on just about any cell, but the goal is to deselect rows 9, 15 and 20 before you carry out the next step.

5. Hold down the Ctrl key, and click rows 8, 13 and 17 on the worksheet frame. Change the fill color for these cells to green.

6. Click on cell A6 (so that rows 8, 13 and 17 are no longer selected), choose Sort & Filter in the Editing section of the Home tab, and then choose Custom Sort.

7. As shown in Figure 1, make these changes in the Sort dialog box:

a. Set column to Active

b. Set Sort On to Cell Color.

c. Set Order to the color yellow, and leave default choice of On Top.

Color order: You can also choose to have colors sort to the bottom of your list.

Figure 1: The Sort dialog box permits sorting by color.

8. Click the Add Level button, and then make these changes:

a. Set column to Active.

b. Set Sort On to Cell Color.

c. Set the Order to the color green, and leave default choice of On Top.

9. Click OK to sort your list, which should now look similar to Figure 2.

Figure 2: The list from Figure 1, now sorted with yellow first and green second.

Sort by Cell Icon

In addition, you can sort two other new criteria: font color and cell icon. Font color works in the same fashion as cell color; but you may not be familiar with cell icons. To keep things simple, I’ll use an improbable example:

1. Export the customer list from Office Accounting again, as described above.

2. Select the amounts in the balance column — in my case cells I7 through I47.

3. Choose Conditional Formatting in the Styles section of the Home tab, choose Icon Sets, and then choose the first icon set (green up arrow, yellow side arrow, and red down arrow).

Graphical representation: Icon sets — new in Excel 2007 — enable you to group numerical data in graphical fashion.

4. Click on cell A6 to deselect the balance amounts — otherwise Excel will ask if you want to expand the selection to include the entire table.

5. Choose Sort & Filter in the Editing section of the Home tab and then Custom Sort.

6. As shown in Figure 3, make these changes in the Sort dialog box:

a. Set column to Balance.

b. Set Sort On to Cell Icon.

c. Set Order to the yellow side arrow.

Figure 3: You can sort a list based on cell icons applied via conditional formatting.

7. Click OK to sort your list, which should now look similar to Figure 4. Notice that the row with the yellow side arrow sorted to the top of the list, while the rest of the list remains in alphabetical order.

Figure 4: I sorted the yellow side arrows to the top of the list.

Although you probably wouldn’t sort a customer balance list in this fashion, I use this example to show that sorting in Excel 2007 is limited only by your imagination.

Remove Conditional Formatting: Choose Conditional Formatting, Clear Rule, and then either Clear Rules from Selected Cells or Clear Rules from Entire Worksheet.

Other Sort Options

You have three choices when you sort based on values (as opposed to colors or icons):

· Largest to smallest

· Smallest to largest

· Custom lists

Sorting by custom lists has long been available in Excel, but users often overlook the capability. Custom lists can be anything you use frequently, such as department names, office locations or perhaps regions. It’s easy to create a custom list:

1. Enter these values in a blank worksheet:

· Redmond

· Covington

· Lake Forest Park

· New Castle

· Medina

· Bellevue

2. Select the list with your mouse.

3. Click the Office button, and then choose Excel options.

4. In the Popular section, click the Edit Custom Lists button.

Date Series: The days of the week and months of the year are predefined custom lists in all versions of Excel.

5. Click the Import button to import the list, and then click OK twice.

In the future, you can enter the first item from the list in a worksheet cell and then drag the fill handle down or across to insert the list into your worksheets as needed.

Computer-specific: Custom lists do not reside in worksheets, so you must repeat the steps above on any other computers where you may wish to use the custom lists.

Now let’s sort the Fabrikam customer list based on our custom list:

1. Choose cell A6 in the customer list spreadsheet.

2. Choose Custom Sort via the Sort & Filter icon in the Editing section of the Home tab.

3. As shown in Figure 5, make these changes in the Sort dialog box:

a. Set Column to City.

b. Accept the default of Values for Sort On.

c. Choose Custom Lists in the Sort Order field, choose the list that begins “Redmond, Covington…” and then click OK twice.

Figure 5: You can sort data in custom list order, as opposed to alphabetical.

4. As shown in Figure 6, the cities are arranged in order of your custom list, as opposed to the traditional sort order of ascending or descending alphabetical order.

Figure 6: The cities are arranged in the same order as the custom list.

Another feature that users often overlook is the ability to sort data sideways. In the Sort box click the Options button, and then choose Sort Left to Right, as shown in Figure 7. You can use this capability in conjunction with sorting by values, colors or custom lists to reorganize data in a flash, without moving each column manually one by one.

Figure 7: The cities are arranged in the same order as the custom list.

Figure 7: Data can sort left to right or up and down.

Overlooked Excel 2003 Sort Options

Excel 2003 and earlier versions offer the same custom list feature that I discuss above. The only difference is how you access the Custom Lists screen:

1. Select your list in a worksheet.

2. Choose Tools, Options and then Custom Lists.

3. Click the Import button, and then click OK.

You’re now ready to use the custom list within any of your worksheets, or you can choose to sort based on a custom list:

1. Select a cell within the list that you wish to sort.

2. Choose Data and then Sort.

3. Click the Options button, and then select your custom list from the First Key Sort Order field.

Important: Sorting based on custom lists is limited to the first sort field. Conversely, Excel 2007 allows you to use custom lists in any of the sort criteria fields.

4. Click OK twice to complete the sort.

Sort Sideways: The Options button in the Sort dialog box also allows you to choose Sort Left to Right.

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.

Excel 2007 vs. 2003: Manage Your Data in ‘Sort’ Order