Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Media
Contact
Home

 

Sorting in Excel


Amber MacArthur, Bill Jelen, Leo LaPorte, Andy Walker on the set of Call for Help on TechTV Canada.

As mentioned on the show, you can get a free chapter a week from the new book by signing up here.


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.

For the BEST TV show on technology, check out Call for Help.

This tip was originally published on June 2, 2005 and aired on TechTV on June 10, 2005. The permanent URL for this page is http://www.mrexcel.com/tip093.shtml.

If you are looking for show notes from another episode, visit my complete list of TechTV appearances.

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

All contents Copyright 1998-2008 by MrExcel Consulting.