Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Automatically Sorting as You Enter Information.
by Allen Wyatt
(last updated March 26, 2014)
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 (9006) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel 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!
Sorting by dates is easy, and you end up with a list that is in chronological order. However, things become a bit more tricky ...Discover More
One way you can easily work with data in a worksheet is to sort it into whatever order you find most helpful. Excel allows ...Discover More
When you sort data in a worksheet, there are a couple ways you can do it. Using the simple way can result in unsatisfactory ...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 ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.