One of our most popular video clips is how to auto sort a list using VLOOKUP and RANK with no VBA. Although this is a brilliant trick we sometimes feel that it is not what people are looking for so below are some other options to auto sort excel lists.
Auto sort via formula
As per the video clip mentioned above, you can create a formula using VLOOKUP and one of the RANK options to automatcically sort a list as it is copied and pasted into Excel. One of the constraints with this method is if there is a tie and we have dealt with the tie issue in one of many blog posts.
NEW: Go to VLOOKUP Explanations with downloadable exercises and detailed solution
Using Data Sort
Although not technically automatic, the data sort button is the most obvious place to go and sort a list either top to bottom, or left to right. You can also create your own custom sort lists
Sorting using Pivot Tables
Pivot Tables are the best form of auto sorting. You can change the settings so that you your rows or columns will sort either automatically, manually or based on one of the data fields. Once you have set it up, whenever you refresh with new data it will sort per your requirements.
Sorting using Data Autofilter
You can sort within the autofilter with relative ease. The option exists to sort by colour as well. Again it is not technically automatic as you need to click the button to sort it, it is a nice option.
Using VBA Macros
You can also create some VBA to do the sorting for you. You can either create sophisticated code to auto run, or else a simple version using the Macro Recorder.