Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Automatically Sorting as You Enter Information.
by Allen Wyatt
(last updated November 30, 2013)
Pat wonders if there is a way to automatically sort every time she adds new data to a worksheet. Pat thinks it would be great, for instance, that when she adds a new name to a list of names that the names are automatically sorted to always be in order.
The only way that this can be done is by using a macro that is triggered whenever something new is entered in the worksheet. You can, for instance, add a macro to the code for a worksheet that is triggered when something in the worksheet changes. (You can view the code window by right-clicking the worksheet tab and choosing View Code from the resulting Context menu.) The following is an example of one such simple macro:
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Range("A1").Sort Key1:=Range("A2"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End Sub
The macro assumes that you want to sort on the data in column A and that there is a header in cell A1. If the names are in a different column, just change the cell A2 reference to a different column, such as B2, C2, etc.
Of course, sorting anytime that any change is made can be bothersome. You might want to limit when the sorting is done so that it only occurs when changes are made to a specific portion of your data. The following version of the macro sorts the data only when a change is made in column A.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("A:A")) Is Nothing Then Range("A1").Sort Key1:=Range("A2"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End If End Sub
There are some drawbacks to using a macro to automatically sort your data. First, since you are using a macro to sort, the operation is essentially "final." In other words, after the sorting you can't use Ctrl+Z to undo the operation.
A second drawback is that data entry might become a bit disconcerting. For instance, if you use any of the above macros and you start to put names into the worksheet, they will be sorted as soon as you finish what is in column A. If your data uses five columns and you start your entry in row 15, as soon as you get done entering the name into column A (and before you enter data into columns B through E), your data is sorted into the proper order. This means that you will need to find where it was moved in the sort, select the proper cell in column B, and then enter the rest of the data for the record. Of course, the way around this is to add your data in an unnatural order—simply make sure that the name in column A is the very last thing you enter for the record.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9005) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Automatically Sorting as You Enter Information.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Protect a worksheet and you limit exactly what can be done with the data in the worksheet. One of the things that could ...Discover More
As yo get more and more worksheets into a workbook, you'll find yourself moving them around into different sequences. You ...Discover More
When formatting the layout of your worksheet, Excel allows you to easily merge adjacent cells together. This can cause ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.