 |
 |
| About MrExcel |
| Consulting Services |
| Learn Excel Resources |
| Challenge of the Month |
| MrExcel Seminars |
| MrExcel Store |
| Podcast |
| Search |
| Media |
| Contact |
| Home |
 |
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 SortingIt 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
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.
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.