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.
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.
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.