Visit Applied Office's Website Contact  

March 2006 : excel


  sorting it out

Excel has many powerful, but underutilized, analytical features. One of these is the Sort feature. Sorting allows you to arrange worksheet data in different orders, making the data easier to understand and analyze.

When information is entered into Excel or imported from another source, it is often not arranged in a useful way. For example, say you’ve alphabetically entered a list of your company’s salespeople and their sales for the quarter into a worksheet. To analyze the data, you can sort the salespeople by amount of sales, office location, staff level, or any other category. As long as the information is organized in a grid of rows and columns, you’re ready to sort.

Sort by one column

To sort rows in ascending order, first decide on a column to sort by. Click a cell in the column you want to sort by and click the Sort Ascending button on the Standard toolbar. To sort the rows in reverse order, click the Sort Descending button instead. When you sort by a column, the cells in each row stay together.

Sort by two or three columns

To sort using more than one column, click a cell in the range you want to sort or select the range. Select Data > Sort from the menu. In the Sort dialog box, click the first Sort by list arrow, select the first criteria (column) you want to sort by, and select Ascending or Descending. It helps to have columns with labeled headings so that you can identify the columns you want to sort by.

Next, select a second or third sort criteria and click OK. For example, you could sort a list of salespeople first by their sales totals, then by their office locations, and then by their staff levels. Now your data is sorted by each of the columns, in the sort order you selected.

Create a custom sort order

You can also define your own sort orders in Excel. For example, if you use a regular sort to order your salespeople by level—VP, Mgr, or Staff—you can only sort them in ascending or descending order alphabetically (Mgr, Staff, VP; or VP, Staff, Mgr). If you want to order them in the correct order of importance (VP, Mgr, Staff), you need to create a custom sort order.

To create a custom sort, enter the data you want to sort by in the desired order from top to bottom (VP, Mgr, Staff) in a column of cells on your worksheet. Select the cells, then select Tools > Options from the menu. Click the Custom Lists tab. Click the Import button to add your new sort criteria to the "List entries" area and click OK. Select the range you want to sort and select Data > Sort from the menu.

In the Sort dialog box, click the column you want to sort and click the Options button. The Sort Options dialog box appears. Click the "First key sort order" list arrow and select the list you just added. Click OK, then click OK again in the Sort dialog box. The data is sorted using your custom sort order.

Sort by rows instead of columns

Although sorting by columns is more common, you may need to sort some data by rows instead. To sort by rows, select Data > Sort from the menu and click the Options button to display the Sort Options dialog box. The row sorting process is similar to sorting by columns, except you need to select "Sort left to right" in the Orientation area of the Sort Options dialog box. Happy sorting!


  quick jump

Jump to other stories through these links:

[ contents.htm]

  keyboard shortcut of the month

Want to quickly zoom in to a spot on your worksheet, or zoom out so you can see more stuff?

Hold down Ctrl while scrolling that little wheel on your mouse and watch what happens.

By the way, this trick works in Word and PowerPoint too.

  quick reference card

Get the Quick Reference Card on Microsoft Excel!  Download it for free and print it on your own printer.  You might even want to laminate it.

  screen shots

The following screen shots illustrate the articles on the left.  Click on one for a larger view.