Sorting in Excel

June 02, 2005

Excel is great at putting data in order. There are some cool tricks that allow you to sort perfectly every time. Most people are familiar with the Excel sort dialog. Found on the Data menu, the Sort dialog lets you specify up to three sort criteria.

If you need to sort by more than three criteria, you can do this with two sorts. If you want to sort by Region, then by product, then by customer, then by date, then by descending quantity, you would first sort by date and quantity. Then, do a second sort by Region, Product, and Customer.Make sure that all of the columns in your data have a heading. This will allow Excel’s intellisense to work properly. If just one heading is missing, it is likely that your headings will be sorted down into the data.One-Click Sorting

It is also possible to sort using the AZ button on the standard toolbar. Select a single cell in one column and click the AZ button to sort in ascending order or the ZA button to sort in descending order.

It is critical that you select just a single cell. If you would select the entire column, then just that column would be sorted, causing all of the data in that column to be mis-matched with other cells.Using the AZ toolbar button, you can also handle sorts by more than three criteria. To carry out the sort above, select a single cell in the Quantity column and click ZA. Select a cell in the Customer column and click AZ. Select a cell in the date column and click AZ. Repeat for Product and then Region.Custom Sorting Sometimes you need to sort data into a recognizable order that is not alphabetical. For example, maybe your company wants regions listed as “East”, “Central” and then “West”. Using a regular sort, there is not a good way to force E to sort before C and W.

• First, type the correct order in an out of the way place
• From the menu, select Tools – Options – Custom List
• ---

  ---

  Use Import from Cells and click Import

  

• When you use the Sort dialog, choose options, and select your list.

  

• Result: the list is sorted by your custom list sequence

Random Sorting

You have a list of 25 students in your class. You need to assign them a certain order in which to present their book reports. If you assign them alphabetically, then Amber and Andy will continually have to go first. You would like to randomly sort the list.

• Next to list, add a heading called Random

• Select all of the cells next to this column of names. Type =RAND()

  

• Hit Ctrl + Enter to enter the cells in all of the cells in the selection.

  

• Sort by column B to get a random sequence.

Note that when you sort, the sort will be performed and then all of the cells in column B will be re-populated with a new random number. Thus, after the sort, the rows will be randomly sorted, but the values in column B will no longer appear in ascending order. This is because the values in B changed after the sort.